T-SQL 2005 中新增函数练习

下面是我的SQL练习：

 

CREATE TABLE Sales 
( 
 EmpID  VARCHAR(10) NOT NULL PRIMARY KEY, 
 MgrID  VARCHAR(10) NOT NULL, 
 Qty    INT NOT NULL 
); 
INSERT INTO Sales VALUES('A','Z',300); 
INSERT INTO Sales VALUES('B','X',100); 
INSERT INTO Sales VALUES('C','X',200); 
INSERT INTO Sales VALUES('D','Y',200); 
INSERT INTO Sales VALUES('E','Z',250); 
INSERT INTO Sales VALUES('F','Z',300); 
INSERT INTO Sales VALUES('G','X',100); 
INSERT INTO Sales VALUES('H','Y',150); 
INSERT INTO Sales VALUES('I','X',250); 
INSERT INTO Sales VALUES('J','Z',100); 
INSERT INTO Sales VALUES('K','Y',250); 




select * from dbo.Sales

/* 练习 Row_Number()
select empid,mgrid,qty 
      ,row_number() over(order by qty) as 刘
      ,row_number() over(order by qty,empid) as 祖 
      ,row_number() over(partition by mgrid order by qty,empid) as 亮 
from sales 
order by qty 
*/



/* 练习 rank()和dense_rank()
select empid,mgrid,qty
	  ,rank() over(order by qty) as rank 
	  ,dense_rank() over(order by qty) as dense_rank 
	  ,rank() over(partition by mgrid order by qty) as rank_partition 
	  ,dense_rank() over(partition by mgrid order by qty) as dense_rank_partition 
from sales 
order by qty 
*/



/* 练习 ntitle()函数
select empid,mgrid,qty
      ,ntile(3) over(order by qty) as ntile_by_qty 
      ,ntile(3) over(order by qty,empid) as ntile_by_empidqty 
      ,ntile(3) over(partition by mgrid order by qty,empid) as ntile_by_mgrid_empidqty 
from sales
order by mgrid,qty
*/

 

呵呵，写完了。