原始数据 如下:declare @a table (a int,b int,n int)insert @a
select 1,2,1
union all
select 1,3,2
union all
select 2,2,2
union all
select 3,2,4
union all
select 4,2,4
union all
select 5,2,5select * from @aa b n
----------- ----------- -----------
1 2 1
1 3 2
2 2 2
3 2 4
4 2 4
5 2 5我想得到的结果为:a b n 排名
----------- ----------- ----------- -----------
1 2 1 1
1 3 2 2
2 2 2 2
3 2 4 3
4 2 4 3
5 2 5 4说明下:
a,b两列式联合主键,n是要排序的列
最后一列排名 是我们根据n的大小排出来的数据量很大,大家尽量想一个速度快的方法
select 1,2,1
union all
select 1,3,2
union all
select 2,2,2
union all
select 3,2,4
union all
select 4,2,4
union all
select 5,2,5select * from @aa b n
----------- ----------- -----------
1 2 1
1 3 2
2 2 2
3 2 4
4 2 4
5 2 5我想得到的结果为:a b n 排名
----------- ----------- ----------- -----------
1 2 1 1
1 3 2 2
2 2 2 2
3 2 4 3
4 2 4 3
5 2 5 4说明下:
a,b两列式联合主键,n是要排序的列
最后一列排名 是我们根据n的大小排出来的数据量很大,大家尽量想一个速度快的方法
declare @a table (a int,b int,n int)insert @a
select 1,2,1
union all
select 1,3,2
union all
select 2,2,2
union all
select 3,2,4
union all
select 4,2,4
union all
select 5,2,5select Dense_Rank() over(order by n),* from @a1 1 2 1
2 1 3 2
2 2 2 2
3 3 2 4
3 4 2 4
4 5 2 5
*,排名=row_number()over(partition by n order by n)
from
tb
CREATE TABLE tb(Name varchar(10),Score decimal(10,2))
INSERT tb SELECT 'aa',99
UNION ALL SELECT 'bb',56
UNION ALL SELECT 'cc',56
UNION ALL SELECT 'dd',77
UNION ALL SELECT 'ee',78
UNION ALL SELECT 'ff',76
UNION ALL SELECT 'gg',78
UNION ALL SELECT 'ff',50
GO--1. 名次生成方式1,Score重复时合并名次
SELECT *,Place=(SELECT COUNT(DISTINCT Score) FROM tb WHERE Score>=a.Score)
FROM tb a
ORDER BY Place
/*--结果
Name Score Place
---------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 3
ff 76.00 4
bb 56.00 5
cc 56.00 5
ff 50.00 6
--*/--2. 名次生成方式2,Score重复时保留名次空缺
SELECT *,Place=(SELECT COUNT(Score) FROM tb WHERE Score>a.Score)+1
FROM tb a
ORDER BY Place
/*--结果
Name Score Place
--------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 4
ff 76.00 5
bb 56.00 6
cc 56.00 6
ff 50.00 8
--*/
排名=(select count(distinct n) from @a where n<=t.n)
from @a t/**
a b n 排名
----------- ----------- ----------- -----------
1 2 1 1
1 3 2 2
2 2 2 2
3 2 4 3
4 2 4 3
5 2 5 4(所影响的行数为 6 行)
**/
declare @a table (a int,b int,n int)insert @a
select 1,2,1
union all
select 1,3,2
union all
select 2,2,2
union all
select 3,2,4
union all
select 4,2,4
union all
select 5,2,5select a,b,n,NTILE(4) OVER (ORDER BY n) AS '排名'
from @a
select dense_rank() over(order by n),* from tb
--2000
select *,(select count(distinct n) from tb where n<=t.n)
from tb t
1、排名函数:
declare @a table (a int,b int,n int)insert @a
select 1,2,1
union all
select 1,3,2
union all
select 2,2,2
union all
select 3,2,4
union all
select 4,2,4
union all
select 5,2,5select *,Dense_Rank() over(order by n) from @a
(6 行受影响)
a b n
----------- ----------- ----------- --------------------
1 2 1 1
1 3 2 2
2 2 2 2
3 2 4 3
4 2 4 3
5 2 5 4(6 行受影响)
2、
declare @a table (a int,b int,n int)insert @a
select 1,2,1
union all
select 1,3,2
union all
select 2,2,2
union all
select 3,2,4
union all
select 4,2,4
union all
select 5,2,5select *,(select count(distinct n) from @a where n<=a.n) from @a a(6 行受影响)
a b n
----------- ----------- ----------- -----------
1 2 1 1
1 3 2 2
2 2 2 2
3 2 4 3
4 2 4 3
5 2 5 4(6 行受影响)