菜鸟询问一道关于with的题

Question

有关系表s(student_id, score)，增加一列作为学生的排名，即最高分的一批学生rank为1，第二高的为2，依此类推。用with做。

Answer 1

select  student_id, score,rank() over(order by score) as [rank]
from s

Answer 2

row_number()
dense_rank()
rank()

Answer 3

select  student_id, score,rank() over(order by score) as [rank]
from
(
select '001' student_id,89 score union all 
select '003',89 union all 
select '002',59 union all 
select '004',100 
) sstudent_id score       rank
---------- ----------- --------------------
002        59          1
003        89          2
001        89          2
004        100         4(4 行受影响)

Answer 4

select  student_id, score,rank() over(order by score) as [rank]
from
(
select '001' student_id,89 score union all 
select '003',89 union all 
select '002',59 union all 
select '004',100 
) sstudent_id score       rank
---------- ----------- --------------------
002        59          1
003        89          2
001        89          2
004        100         4(4 行受影响)
select  student_id, score,dense_rank() over(order by score) as [rank]
from
(
select '001' student_id,89 score union all 
select '003',89 union all 
select '002',59 union all 
select '004',100 
) s
student_id score       rank
---------- ----------- --------------------
002        59          1
003        89          2
001        89          2
004        100         3(4 行受影响)
select  student_id, score,row_number() over(order by score) as [rank]
from
(
select '001' student_id,89 score union all 
select '003',89 union all 
select '002',59 union all 
select '004',100 
) s
student_id score       rank
---------- ----------- --------------------
002        59          1
003        89          2
001        89          3
004        100         4(4 行受影响)

Answer 5

with 可以创建一个临时表，比如s(student_id, score)中选成绩最高的人可以这样：
with max_score(value) as
    select max(score)
    from s
select student_id
from s, max_score
where score = max_score.value

Answer 6

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([id] int,[point] int,[name] varchar(4))
insert [TB]
select 1,100,'张三' union all
select 2,80,'李四' union all
select 3,80,'王五' union all
select 4,75,'杨六' union all
select 5,75,'方七'select id=(select count([point])+1 from TB where T.[point]<[point]),point,name
from TB t/*
id          point       name
----------- ----------- ----
1           100         张三
2           80          李四
2           80          王五
4           75          杨六
4           75          方七(5 行受影响)
*/drop table TB--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([id] int,[point] int,[name] Nvarchar(4))
insert [TB]
select 1,100,N'张三' union all
select 2,80,N'李四' union all
select 3,80,N'王五' union all
select 4,75,N'杨六' union all
select 5,75,N'方七'select id=(select count(distinct point) from TB where T.[point]<=[point]),
       point,
       [name]
from TB t
/*
id          point       name
----------- ----------- ----
1           100         张三
2           80          李四
2           80          王五
3           75          杨六
3           75          方七(5 個資料列受到影響)
*/
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int,[b] varchar(2))
insert [tb]
select 1100,'aa' union all
select 1100,'bb' union all
select 1101,'cc' union all
select 1101,'dd' union all
select 1102,'ee' union all
select 1103,'ff'
 
---查询---
select
  ltrim(a)+right('00'+ltrim((select count(1)+1 from tb where a=t.a and b<t.b)),2) as a,
  b
from
  tb t---结果---
a                b    
---------------- ---- 
110001           aa
110002           bb
110101           cc
110102           dd
110201           ee
110301           ff（所影响的行数为 6 行）
-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-08-18 17:15:23
-------------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (a INT,b VARCHAR(2))
INSERT INTO @T
SELECT 1100,'aa' UNION ALL
SELECT 1100,'bb' UNION ALL
SELECT 1101,'cc' UNION ALL
SELECT 1101,'dd' UNION ALL
SELECT 1102,'ee' UNION ALL
SELECT 1103,'ff'--SQL查询如下:SELECT
    rtrim(a)+RIGHT(100+row_number() over(partition by a order by a),2) as a,
    b
FROM @T/*
a                b
---------------- ----
110001           aa
110002           bb
110101           cc
110102           dd
110201           ee
110301           ff(6 行受影响)
*/

Answer 7

row_number() 
dense_rank() 
rank()

Answer 8

with cte as
(select student_id, 
rank() over(order by score desc) rank from s)
select student_id,score,rank
from s a join cte b on a.student_id=b.student_id

Answer 9

with tbRank as
(select student_id, score,rankID=row_number() over(order by score desc)from s)
select * from tbRank order by rankID