越弄越复杂,结果问题还没有解决,
有如下数据
ID XH
A 1
A 2
A 3
B 4
B 5
C 6
C 7
D 8
得到如下排列
ID XH
A 1
B 4
C 6
D 8
A 2
B 5
C 7
A 3
有如下数据
ID XH
A 1
A 2
A 3
B 4
B 5
C 6
C 7
D 8
得到如下排列
ID XH
A 1
B 4
C 6
D 8
A 2
B 5
C 7
A 3
ORDER BY ROW_NUMBER() OVER(PARTITION BY ID ORDER BY XH);
SELECT * FROM tb AS A
ORDER BY (SELECT COUNT(*) FROM tb
WHERE ID=A.ID AND XH < A.XH)
(
select t.* , px = (select count(1) from tb where id = t.id and xh < t.xh) + 1 from tb t
) m
order by px , id
select id , xh from
(
select t.* , px = row_number() over(partition by id order by xh) from tb t
) m
order by px , id
insert into tb values('A', 1)
insert into tb values('A', 2)
insert into tb values('A', 3)
insert into tb values('B', 4)
insert into tb values('B', 5)
insert into tb values('C', 6)
insert into tb values('C', 7)
insert into tb values('D', 8)
go--sql 2000select id , xh from
(
select t.* , px = (select count(1) from tb where id = t.id and xh < t.xh) + 1 from tb t
) m
order by px , iddrop table tb/*id xh
---------- -----------
A 1
B 4
C 6
D 8
A 2
B 5
C 7
A 3(所影响的行数为 8 行)*/create table tb(ID varchar(10),XH int)
insert into tb values('A', 1)
insert into tb values('A', 2)
insert into tb values('A', 3)
insert into tb values('B', 4)
insert into tb values('B', 5)
insert into tb values('C', 6)
insert into tb values('C', 7)
insert into tb values('D', 8)
go-- sql 2005
select id , xh from
(
select t.* , px = row_number() over(partition by id order by xh) from tb t
) m
order by px , iddrop table tb/*id xh
---------- -----------
A 1
B 4
C 6
D 8
A 2
B 5
C 7
A 3(8 行受影响)
*/