SQL server 2005中新增函数及操作
来源:互联网 发布:杭州卓森网络怎么样 编辑:程序博客网 时间:2024/05/11 21:16
1 SQL server 2005中新增的排序函数及操作 ---此文章发表在:http://www.cnblogs.com/xh831213/archive/2008/02/13/1068041.html
SELECT
YEAR(BirthDate),
ROW_NUMBER() OVER(ORDER BY YEAR(BirthDate)) AS 'RowNumber',/**//* 按年产生一个唯一的序号 */
ROW_NUMBER() OVER(PARTITION BY YEAR(BirthDate) ORDER BY BirthDate) AS RowNumberPartition, /**//* 每年产生一个唯一的序列 */
RANK() OVER(ORDER BY YEAR(BirthDate)) AS 'Rank', /**//* 产生一个非紧密排名 */
DENSE_RANK() OVER(ORDER BY YEAR(BirthDate)) AS 'Dense_Rank', /**//* 产生一个紧密排名 */
NTILE(10) OVER(ORDER BY BirthDate DESC) AS 'ntile' /**//* 将结果分成10个组 */
FROM HumanResources.Employee
ORDER BY BirthDate
看看新的排序函数如何解决SQL server 2000中不方便解决的问题
--按BirthDate排序,取第10条到20条的数据 (这一定是最激动人心的新特性,哈哈)
SELECT BirthDate FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY BirthDate) AS a,BirthDate
FROM HumanResources.Employee
) AS a
WHERE a BETWEEN 10 AND 20
--将数据分成十份,取第三份
SELECT
BirthDate
FROM
(
SELECT
NTILE(10) OVER(ORDER BY BirthDate DESC) AS a,BirthDate
FROM HumanResources.Employee
) AS a
WHERE a = 3再来看看如何用新的排序函数解决以前在SQL server 2000中的问题
-- 出生的员工最多的一年出生多少员工(有点)
/**//* SQL server 2000 */
SELECT MAX(a)
FROM
(
SELECT COUNT(EmployeeID) AS a FROM HumanResources.Employee GROUP BY YEAR(BirthDate)
) AS a
/**//* SQL server 2005 */
SELECT MAX(a)
FROM
(
SELECT
ROW_NUMBER() OVER(PARTITION BY YEAR(BirthDate) ORDER BY BirthDate) AS a
FROM HumanResources.Employee
) AS a
-- 出生日期涵盖了多少年
/**//* SQL server 2000 */
SELECT COUNT(DISTINCT YEAR(BirthDate)) FROM HumanResources.Employee
/**//* SQL server 2005 */
SELECT MAX(a)
FROM
(
SELECT
DENSE_RANK() OVER(ORDER BY YEAR(BirthDate)) AS a
FROM HumanResources.Employee
) AS a
-- 显示前10%的数据
/**//* SQL server 2000 */
SELECT TOP 10 Percent BirthDate FROM HumanResources.Employee
/**//* SQL server 2005 */
SELECT
BirthDate
FROM
(
SELECT
NTILE(10) OVER(ORDER BY BirthDate DESC) AS a,BirthDate
FROM HumanResources.Employee
) AS a
WHERE a = 1
SELECT
YEAR(BirthDate),
ROW_NUMBER() OVER(ORDER BY YEAR(BirthDate)) AS 'RowNumber',/**//* 按年产生一个唯一的序号 */
ROW_NUMBER() OVER(PARTITION BY YEAR(BirthDate) ORDER BY BirthDate) AS RowNumberPartition, /**//* 每年产生一个唯一的序列 */
RANK() OVER(ORDER BY YEAR(BirthDate)) AS 'Rank', /**//* 产生一个非紧密排名 */
DENSE_RANK() OVER(ORDER BY YEAR(BirthDate)) AS 'Dense_Rank', /**//* 产生一个紧密排名 */
NTILE(10) OVER(ORDER BY BirthDate DESC) AS 'ntile' /**//* 将结果分成10个组 */
FROM HumanResources.Employee
ORDER BY BirthDate
看看新的排序函数如何解决SQL server 2000中不方便解决的问题
--按BirthDate排序,取第10条到20条的数据 (这一定是最激动人心的新特性,哈哈)
SELECT BirthDate FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY BirthDate) AS a,BirthDate
FROM HumanResources.Employee
) AS a
WHERE a BETWEEN 10 AND 20
--将数据分成十份,取第三份
SELECT
BirthDate
FROM
(
SELECT
NTILE(10) OVER(ORDER BY BirthDate DESC) AS a,BirthDate
FROM HumanResources.Employee
) AS a
WHERE a = 3再来看看如何用新的排序函数解决以前在SQL server 2000中的问题
-- 出生的员工最多的一年出生多少员工(有点)
/**//* SQL server 2000 */
SELECT MAX(a)
FROM
(
SELECT COUNT(EmployeeID) AS a FROM HumanResources.Employee GROUP BY YEAR(BirthDate)
) AS a
/**//* SQL server 2005 */
SELECT MAX(a)
FROM
(
SELECT
ROW_NUMBER() OVER(PARTITION BY YEAR(BirthDate) ORDER BY BirthDate) AS a
FROM HumanResources.Employee
) AS a
-- 出生日期涵盖了多少年
/**//* SQL server 2000 */
SELECT COUNT(DISTINCT YEAR(BirthDate)) FROM HumanResources.Employee
/**//* SQL server 2005 */
SELECT MAX(a)
FROM
(
SELECT
DENSE_RANK() OVER(ORDER BY YEAR(BirthDate)) AS a
FROM HumanResources.Employee
) AS a
-- 显示前10%的数据
/**//* SQL server 2000 */
SELECT TOP 10 Percent BirthDate FROM HumanResources.Employee
/**//* SQL server 2005 */
SELECT
BirthDate
FROM
(
SELECT
NTILE(10) OVER(ORDER BY BirthDate DESC) AS a,BirthDate
FROM HumanResources.Employee
) AS a
WHERE a = 1
- SQL server 2005中新增的排序函数及操作
- SQL server 2005中新增函数及操作
- SQL server 2005中新增函数及操作
- SQL Server 2005中新增的功能强大的窗口函数
- SQL Server 2005新增函数简介
- SQL Server 2005安装及界面截图,新增功能.
- SQL SERVER 2005 新增的几个常用的函数
- 对SQL Server 2005中XML操作函数的理解
- 对SQL Server 2005中XML操作函数的理解
- 对SQL Server 2005中XML操作函数
- SQL Server 2005 新增功能
- SQL Server 2005 新增功能
- SQL SERVER 2005 新增语法
- 新增系统日志函数(SQL Server)
- SQL Server 2012新增内置函数
- SQL Server 2012 2016 2017 新增函数
- sql server 2005中新增加的try catch学习
- sql server 2005中新增的bulk功能
- 【asp.net】VS.Net 下的Wondows窗体常用项目
- 关于为对话框添加背景图片LoadBitmap的问题
- 【c#】如何动态加载控件以及插件编程思想
- 【c#】C#中利用DirectX实现声音播放
- 谷歌Chrome不是浏览器
- SQL server 2005中新增函数及操作
- 【C#+ASP.NET】ASP.NET跨页面传值技巧总结
- html:options标签与html:optionCollectoin标签
- Qt学习手记之一 转帖
- 【C#】日期函数所有样式大全
- 敏捷SOA成功秘诀四:IT运营和监测
- 【汇总c#.net常用函数和方法集】
- 【c#】C#基础全接触
- 用VB结束Excel的进程