在SQL2005中利用DENSE_RANK()排名函数对现有数据进行排序改造

 

/********** 利用排名函数 DENSE_RANK() 对现有数据排序的改造 ***************/

 

-- select left('claro',2) 整理于西安， -06-10 14:15:10.310

--> Microsoft SQL Server 2005 - 9.00.1406.00 

-- (Intel X86)   Mar  3 2007 18:40:02   Copyright (c) 1988-2005

--Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

 

-- 在现有数据中分区生成排序数据（四）

/* 注： DENSE_RANK() 函数与 RANK() 函数的区别是， DENSE_RANK() 返回的数字没有间断，并且始终具有连续的排名。

而 RANK() 函数可能返回不连续的整数。 */

 

-- 例一：关联时间获得相同时间的排名 DENSE_RANK() 返回值始终有连续的排名。

SELECT userid= DENSE_RANK() OVER ( ORDER BY 时间 ), 编号 , 日期 , 时间 -- 按时间分区（重复）生成排序数据

FROM (

select 1 编号 , '20090601' 日期 , '0701' 时间

union all select 1, '20090601' , '1130'   

union all select 1, '20090601' , '1400'  

union all select 1, '20090601' , '1550'

union all select 1, '20090602' , '1550'

union all select 1, '20090602' , '1500'

union all select 1, '20090602' , '1400' ) a

-- 所以排名按结果集 userid 所示。

/*

userid 编号 日期 时间

1   1   20090601   0701

2   1   20090601   1130

3   1   20090602   1400

3   1   20090601   1400

4   1   20090602   1500 -- 排名连续。

5   1   20090602   1550

5   1   20090601   1550

*/

 

-- 例二：关联时间获得相同时间的排名 RANK() 返回值中的排名不连续。

SELECT userid= RANK() OVER ( ORDER BY 时间 ), 编号 , 日期 , 时间 -- 按时间分区（重复）生成排序数据

FROM (

select 1 编号 , '20090601' 日期 , '0701' 时间

union all select 1, '20090601' , '1130'   

union all select 1, '20090601' , '1400'  

union all select 1, '20090601' , '1550'

union all select 1, '20090602' , '1550'

union all select 1, '20090602' , '1500'

union all select 1, '20090602' , '1400' ) a

-- 所以排名按结果集 userid 所示。

/*

userid 编号 日期 时间

1   1   20090601   0701

2   1   20090601   1130

3   1   20090602   1400

3   1   20090601   1400

5   1   20090602   1500 -- 排名不连续 。

6   1   20090602   1550

6   1   20090601   1550

*/

 

-------------------------------------续-------------------------------------------

 

 

刚才在网上看到一个 SQL 考题，顺便做一下。因为会用到 DENSE_RANK() 函数故归到此文章中。

------------ 网上的考题 ------------

有两张表：

Record( recNumber < 主键 > 学号 , recScore 分数 )

Student( stuNumber < 主键 > 学号 , stuName 姓名 , stuClass 班级 )

用 SQL 语句实现以下查询：

查询每一个班级中第二名的同学的姓名。

------------ 生成测试数据 ------------

create table record ( recNumber int , recScore int )

insert into record

select 111, 60 union all

select 112, 70 union all

select 113, 70 union all

select 114, 90 union all

select 115, 60

create table Student ( stuNumber int , stuName varchar ( 20), stuClass varchar ( 20))

insert into Student

select 111, 'a' , 'A1' union all

select 112, 'b' , 'B1' union all

select 113, 'c' , 'C1' union all

select 114, 'd' , 'D1' union all

select 115, 'e' , 'E1'

------------ 查询方式1 ------------

select stuname

from student a

join record b on b. recNumber= a. stuNumber

where recScore in (

select max ( recScore)

from record a

where recScore not in (

select max ( recScore)

from record ))

------------ 查询方式 2 ------------

with a as (

select a. stuName, recScore, DENSE_RANK() over ( order by b. recScore desc ) ID

from student a

join record b on b. recNumber= a. stuNumber

)

select stuName

from a

where ID= '2'

------------ 查询结果 ------------

stuName

--------------------

b

c

 

( 2 行受影响 )