请教如何将工资排序并生成序号列,序号不重复,实现目标表的结果。原表:
姓名 工资
王1 800
王2 700
王3 800
李1 700
李2 1000
李3 880 目标:
ID 姓名 工资
1 李2 1000
2 王3 800
3 李3 800
4 王1 880
5 王2 700
6 李1 700
如果用
select ID=(select count(1) from 表名 where 工资> a.工资)+1,姓名
from 表名 a order by 工资 desc
则序号列出现重复。
姓名 工资
王1 800
王2 700
王3 800
李1 700
李2 1000
李3 880 目标:
ID 姓名 工资
1 李2 1000
2 王3 800
3 李3 800
4 王1 880
5 王2 700
6 李1 700
如果用
select ID=(select count(1) from 表名 where 工资> a.工资)+1,姓名
from 表名 a order by 工资 desc
则序号列出现重复。
insert @t select
'王1 ',800 union select
'王2 ',700 union select
'王3 ',800 union select
'李1 ',700 union select
'李2 ',1000 union select
'李3 ',880
select id = identity(int,1,1) ,* into #
from @t
order by 工资 desc --(select top 100 percent * from @t order by 工资 desc) aselect * from #drop table #/*
id 姓名 工资
----------- ---------- -----------
1 李2 1000
2 李3 880
3 王1 800
4 王3 800
5 王2 700
6 李1 700(所影响的行数为 6 行)
*/
name score
aa 99
bb 56
cc 56
dd 77
ee 78
ff 76
gg 78
ff 501. 名次生成方式1,Score重复时合并名次
SELECT * , Place=(SELECT COUNT(DISTINCT Score) FROM jh03 WHERE Score >= a.Score)
FROM jh03 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 62. 名次生成方式2 , Score重复时保留名次空缺
SELECT * , Place=(SELECT COUNT(Score) FROM jh03 WHERE Score > a.Score) + 1
FROM jh03 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
insert into tb values('王1', 800 )
insert into tb values('王2', 700 )
insert into tb values('王3', 800 )
insert into tb values('李1', 700 )
insert into tb values('李2', 1000 )
insert into tb values('李3', 880 )
go--1. 名次生成方式1,工资重复时合并名次
SELECT id = (SELECT COUNT(DISTINCT 工资) FROM tb WHERE 工资>= a.工资) , * from tb a ORDER BY id
/*
id 姓名 工资
----------- ---------- -----------
1 李2 1000
2 李3 880
3 王1 800
3 王3 800
4 李1 700
4 王2 700(所影响的行数为 6 行)
*/
--2. 名次生成方式2,工资重复时保留名次空缺
SELECT id = (SELECT COUNT(DISTINCT 工资) FROM tb WHERE 工资> a.工资) + 1 , * from tb a ORDER BY id
/*
id 姓名 工资
----------- ---------- -----------
1 李2 1000
2 李3 880
3 王1 800
3 王3 800
4 李1 700
4 王2 700(所影响的行数为 6 行)
*/
--3.按工资,姓名进行排序
SELECT id = (SELECT COUNT(DISTINCT 工资) FROM tb WHERE 工资> a.工资 or (工资 = a.工资 and 姓名 < a.姓名)) + 1 , * from tb a ORDER BY id
/*
id 姓名 工资
----------- ---------- -----------
1 李2 1000
2 李3 880
3 王1 800
4 王3 800
4 李1 700
5 王2 700(所影响的行数为 6 行)
*/
drop table tb
insert into tb values('王1', 800 )
insert into tb values('王2', 700 )
insert into tb values('王3', 800 )
insert into tb values('李1', 700 )
insert into tb values('李2', 1000 )
insert into tb values('李3', 880 )
go--1. 名次生成方式1,工资重复时合并名次
SELECT id = (SELECT COUNT(DISTINCT 工资) FROM tb WHERE 工资>= a.工资) , * from tb a ORDER BY id
/*
id 姓名 工资
----------- ---------- -----------
1 李2 1000
2 李3 880
3 王1 800
3 王3 800
4 李1 700
4 王2 700(所影响的行数为 6 行)
*/
--2. 名次生成方式2,工资重复时保留名次空缺
SELECT id = (SELECT COUNT(DISTINCT 工资) FROM tb WHERE 工资> a.工资) + 1 , * from tb a ORDER BY id
/*
id 姓名 工资
----------- ---------- -----------
1 李2 1000
2 李3 880
3 王1 800
3 王3 800
4 李1 700
4 王2 700(所影响的行数为 6 行)
*/
--3.按工资,姓名进行排序
SELECT id = (SELECT COUNT( 工资) FROM tb WHERE 工资> a.工资 or (工资 = a.工资 and 姓名 < a.姓名)) + 1 , * from tb a ORDER BY id
/*
id 姓名 工资
----------- ---------- -----------
1 李2 1000
2 李3 880
3 王1 800
4 王3 800
5 李1 700
6 王2 700(所影响的行数为 6 行)*/
drop table tb
--2005下可用以下方法
declare @t table(姓名 nvarchar(10),工资 int)
insert @t select
N'王1 ',800 union select
N'王2 ',700 union select
N'王3 ',800 union select
N'李1 ',700 union select
N'李2 ',1000 union select
N'李3 ',880
select * from @tselect ID=row_number() over(order by 工资 desc),*
from @t
select ID=row_number() over(order by 工资 desc),* from T2000就用下临时表:
declare @Temp table(RowNum int identity(1,1),姓名 varchar(10),工资 int)
insert into @Temp select * from T order by 工资 desc
select * from @Temp