现有一表TB结构如下:Id UserId Content CreateTime
1 1
2 2
3 16
4 5
5 2
6 5
7 2
8 16
9 10
中间结果:
Id UserId Content CreateTime
1 12 2
5 2
7 24 5
6 53 16
8 169 10
最终结果:
Id UserId Content CreateTime
1 12 2
5 2
7 23 16
8 164 5
6 59 10即:按用户分组后,再按分组后的最小Id从小到大排序
1 1
2 2
3 16
4 5
5 2
6 5
7 2
8 16
9 10
中间结果:
Id UserId Content CreateTime
1 12 2
5 2
7 24 5
6 53 16
8 169 10
最终结果:
Id UserId Content CreateTime
1 12 2
5 2
7 23 16
8 164 5
6 59 10即:按用户分组后,再按分组后的最小Id从小到大排序
1 1
2 2
3 16
4 5
5 2
6 5
7 2
8 16
9 10
----------------select * from t
order by userid,id
group by id,userid
order by id
order by userid,id语句很简单,不知楼主想干什么
8 16 4 5
6 5 按UserId分组后,要按组内最小的Id号从小到大排~
create table #(id int,userid int)
insert into # values(1,1)
insert into # values(2,2)
insert into # values(3,16)
insert into # values(4,5)
insert into # values(5,2)
insert into # values(6,5)
insert into # values(7,2)
insert into # values(8,16)
insert into # values(9,10)select iden=identity(int,1,1), min(id)id,userid into #ta from # group by userid order by idselect a.* from # a left join #ta b on a.userid=b.userid order by b.iden,a.iddrop talbe #ta----结果
id userid
----------- -----------
1 1
2 2
5 2
7 2
3 16
8 16
4 5
6 5
9 10(9 行受影响)
insert into #
select 1, 1 union all
select 2, 2 union all
select 3, 16 union all
select 4, 5 union all
select 5, 2 union all
select 6, 5 union all
select 7, 2 union all
select 8, 16 union all
select 9, 10
select a.* from # a left join
(
select top 10 userid,min(id)as id from # group by userid order by min(id)
)b
on a.userid=b.userid
order by b.id/*
Id UserId
----------- -----------
1 1
2 2
5 2
7 2
8 16
3 16
4 5
6 5
9 10(所影响的行数为 9 行)*/
Id UserId Content CreateTime
1 1 2 2
5 2
7 2 4 5
6 5 9 10 3 16
8 16
楼上的为什么要加一个Top 10
create table # (Id int,UserId int)
insert into #
select 1, 1 union all
select 2, 2 union all
select 3, 16 union all
select 4, 5 union all
select 5, 2 union all
select 6, 5 union all
select 7, 2 union all
select 8, 16 union all
select 9, 10
select a.* from # a left join
(
select userid,min(id)as id from # group by userid
)b
on a.userid=b.userid
order by b.id
drop table #/*Id UserId
----------- -----------
1 1
2 2
5 2
7 2
8 16
3 16
4 5
6 5
9 10(所影响的行数为 9 行)*/
(
select top 10 userid,min(id)as id from # group by userid order by min(id)
)b
on a.userid=b.userid
order by b.id,a.idId UserId
----------- -----------
1 1
2 2
5 2
7 2
3 16
8 16
4 5
6 5
9 10(所影响的行数为 9 行)
select a.* from # a left join (select top 100 percent min(id) id,userid from # group by userid order by id) b on a.userid=b.userid
order by b.id,a.idid userid
----------- -----------
1 1
2 2
5 2
7 2
3 16
8 16
4 5
6 5
9 10(9 行受影响)
[code=SQL]
select a.* from # a left join (select top 100 percent min(id) id,userid from # group by userid order by id) b on a.userid=b.userid
order by b.id,a.idid userid
----------- -----------
1 1
2 2
5 2
7 2
3 16
8 16
4 5
6 5
9 10(9 行受影响)
[/code]