34,576
社区成员
发帖
与我相关
我的任务
分享
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 2000
select 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 , id
drop 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 , id
drop table tb
/*
id xh
---------- -----------
A 1
B 4
C 6
D 8
A 2
B 5
C 7
A 3
(8 行受影响)
*/
-- 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 , id
--sql 2000
select 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 , id
SELECT * FROM tb
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)