我的表结构和数据如下:
id,title,userid,classid
1 a 1 1
2 b 2 2
3 c 1 1
4 d 3 3
我想选择前20条 userid不重复的记录 并且按classid排序结果因该是:
id,title,userid,classid
1 a 1 1
2 b 2 2
4 d 3 3不知道这个如何写?
id,title,userid,classid
1 a 1 1
2 b 2 2
3 c 1 1
4 d 3 3
我想选择前20条 userid不重复的记录 并且按classid排序结果因该是:
id,title,userid,classid
1 a 1 1
2 b 2 2
4 d 3 3不知道这个如何写?
group by userid, classid
order by userid
declare @t table (id int,title varchar(20),userid int, classid int)
insert into @t
select 1,'a',1,1
union all select 2,'b',2,2
union all select 3,'c',1,1
union all select 4,'d',3,3
union all select 5,'e',4,4
union all select 6,'f',5,5
union all select 7,'g',6,6--方法一
select top 5 * from @t a where not exists(select * from @t where title < a.title and userid =a.userid and classid=a.classid) order by classid--方法二select top 5 * from @t a where id in(select min(id) from @t group by userid,classid) order by classid
insert into @t
select 1,'a',1,1
union all select 2,'b',2,2
union all select 3,'c',1,1
union all select 4,'d',3,3
union all select 5,'e',4,4
union all select 6,'f',5,5
union all select 7,'g',6,6select top 20 * from @t a
where not exists(select 1 from @t where userid=a.userid and id<a.id)
order by classid--or
select top 20 a.* from @t a
join
(
select userid,min(classid)as classid ,min(id) as id from @t group by userid
)b
on a.userid=b.userid and a.classid=b.classid and a.id=b.id
order by a.classid--orselect top 20 a.* from @t a
where classid=(select min(classid) from @t where userid=a.userid)
and id =(select min(id) from @t where userid=a.userid)
order by a.classid
id title userid classid
----------- -------------------- ----------- -----------
1 a 1 1
2 b 2 2
4 d 3 3
5 e 4 4
6 f 5 5
7 g 6 6(6 row(s) affected)