排序不变，根据分类加id号，value有重复，不太好表述，请进来看看数据

wxl2ooo 2010-10-23 10:40:53
A表：
user name value

a name1 300
a name2 100
a name3 100
b name4 600
b name6 300
b name7 300
c name8 400

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
...全文
112 点赞 收藏 11

11 条回复

wxl2ooo 2010-10-25

zsh0809 2010-10-23
``````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)``````

dawugui 2010-10-23
[Quote=引用 7 楼 wxl2ooo 的回复:]

A表：
user name value

a name1 100
a name2 300
a name3 100
b name4 300
b name6 600
b name7 300
c name8 400

[/Quote]

dawugui 2010-10-23
[Quote=引用 4 楼 wxl2ooo 的回复:]

[/Quote]
``````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
``````

wxl2ooo 2010-10-23

A表：
user name value

a name1 100
a name2 300
a name3 100
b name4 300
b name6 600
b name7 300
c name8 400

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

zsh0809 2010-10-23

order by value desc

dawugui 2010-10-23
``````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 ``````

wxl2ooo 2010-10-23

zsh0809 2010-10-23
[Quote=引用 2 楼 wxl2ooo 的回复:]

[/Quote]

wxl2ooo 2010-10-23

zsh0809 2010-10-23
``````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)``````

2.1w+

MS-SQL Server 疑难问题

2010-10-23 10:40