T-SQL 2005 中新增函数练习
2010-07-26 20:55 音乐让我说 阅读(155) 评论(0) 编辑 收藏 举报下面是我的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 */
呵呵,写完了。
作者:音乐让我说(音乐让我说 - 博客园)
出处:http://music.cnblogs.com/
文章版权归本人所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。