22,210
社区成员
发帖
与我相关
我的任务
分享
SELECT (SELECT COUNT(1) AS Expr1
FROM [#TP]
WHERE ([USER] = T.[USER]) AND (VALUE > T.VALUE) OR
([USER] = T.[USER]) AND (VALUE = T.VALUE) AND (NAME < T.NAME)) + 1 AS ID, [USER], NAME, VALUE
FROM [#TP] AS T
ORDER BY T.[USER], ID, T.NAME
ID user name VALUE
----------- ---------- ---------- -----------
1 a name2 300
2 a name1 100
3 a name3 100
1 b name6 600
2 b name4 300
3 b name7 300
1 c name8 400
(7 row(s) affected)
create table A([user] varchar(10), name varchar(10),value int)
insert into a values('a', 'name1', 300)
insert into a values('a', 'name2', 100)
insert into a values('a', 'name3', 100)
insert into a values('b', 'name4', 600)
insert into a values('b', 'name6', 300)
insert into a values('b', 'name7', 300)
insert into a values('c', 'name8', 400)
go
--相同user+value合并名次
select id = (select count(distinct value) from a where [user] = t.[user] and value > t.value) + 1, * from a t order by t.[user] , id , t.name
/*
id user name value
----------- ---------- ---------- -----------
1 a name1 300
2 a name2 100
2 a name3 100
1 b name4 600
2 b name6 300
2 b name7 300
1 c name8 400
(所影响的行数为 7 行)
*/
--相同user+value再按照name排名
select id = (select count(1) from a where [user] = t.[user] and ((value > t.value) or (value = t.value and name < t.name))) + 1,* from a t order by t.[user] , id , t.name
/*
id user name value
----------- ---------- ---------- -----------
1 a name1 300
2 a name2 100
3 a name3 100
1 b name4 600
2 b name6 300
3 b name7 300
1 c name8 400
(所影响的行数为 7 行)
*/
drop table a
create table A([user] varchar(10), name varchar(10),value int)
insert into a values('a', 'name1', 300)
insert into a values('a', 'name2', 100)
insert into a values('a', 'name3', 100)
insert into a values('b', 'name4', 600)
insert into a values('b', 'name6', 300)
insert into a values('b', 'name7', 300)
insert into a values('c', 'name8', 400)
go
--相同user+value合并名次
select id = (select count(distinct value) from a where [user] = t.[user] and value > t.value) + 1, * from a t
/*
id user name value
----------- ---------- ---------- -----------
1 a name1 300
2 a name2 100
2 a name3 100
1 b name4 600
2 b name6 300
2 b name7 300
1 c name8 400
(所影响的行数为 7 行)
*/
--相同user+value再按照name排名
select id = (select count(1) from a where [user] = t.[user] and ((value > t.value) or (value = t.value and name < t.name))) + 1,* from a t
/*
id user name value
----------- ---------- ---------- -----------
1 a name1 300
2 a name2 100
3 a name3 100
1 b name4 600
2 b name6 300
3 b name7 300
1 c name8 400
(所影响的行数为 7 行)
*/
drop table a
SELECT ID=(SELECT COUNT(1)+1 FROM #tp p
WHERE p.[user]=t.[user] AND NAME<t.NAME)
,[user], name ,value
FROM #tp t
ID user name value
----------- ---------- ---------- -----------
1 a name1 300
2 a name2 100
3 a name3 100
1 b name4 600
2 b name6 300
3 b name7 300
1 c name8 400
(7 row(s) affected)