Top 功能增强

Top字句功能增强
--create a table and insert some data
use demo
go
CREATE TABLE toptest (column1 VARCHAR(150))
go
INSERT INTO toptest VALUES('t1')
INSERT INTO toptest VALUES('t2')
INSERT INTO toptest VALUES('t3')
INSERT INTO toptest VALUES('t4')
INSERT INTO toptest VALUES('t5')
INSERT INTO toptest VALUES('t6')
INSERT INTO toptest VALUES('t7')
INSERT INTO toptest VALUES('t8')
select * from toptest
go
CREATE TABLE toptest2 (column2 VARCHAR(150))
go
INSERT INTO toptest2 VALUES('c1')
INSERT INTO toptest2 VALUES('c2')

--declare 3 variables
DECLARE @a INT
DECLARE @b INT
DECLARE @c INT
--set values
SET @a = 10
SET @b = 5
SELECT @c = @a/@b
--在 TOP 中使用变量
SELECT TOP(@c) * FROM toptest
--在 TOP 中select 表达式
SELECT TOP(SELECT COUNT(*) FROM toptest2) *
FROM toptest

--DML top
DELETE TOP(2) toptest where column1>'t6'
--this sets 't1' and 't2' to 'hi'
UPDATE TOP(2) toptest SET column1 = 'hi' where column1<='t2'

--在 TOP 中使用 PERCENT 和 WITH TIES
以下示例获取所有雇员中薪水最高的 10% 的员工，并根据基本薪金按降序返回。
指定 WITH TIES 可确保结果集中同时包含其薪水与返回的最低薪水相同的所有雇员，
即使这样返回的雇员数会超过总数的 10% 也是这样。

USE AdventureWorks ;
GO
SELECT TOP(10) PERCENT WITH TIES
EmployeeID, Title, DepartmentID, Gender, BaseRate
FROM HumanResources.Employee
ORDER BY BaseRate DESC;