1 SQL server 2005中新增的排序函数及操作
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
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
424
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
