select * from tb a where message in(select top 5 message from tb where a.userid=userid)
declare @tb table (id int,name varchar(10),mess varchar(10)) insert into @tb select 1,'a','aaaa' insert into @tb select 2,'a','bbbb' insert into @tb select 3,'a','cccc' insert into @tb select 4,'b','dddd' insert into @tb select 5,'b','eeee' insert into @tb select 6,'b','ffff' insert into @tb select 7,'c','ggg' insert into @tb select 8,'c','hhh' insert into @tb select 9,'c','iiii'select * from @tb a where id in(select top 2 id from @tb where name=a.name)1 a aaaa 2 a bbbb 4 b dddd 5 b eeee 7 c ggg 8 c hhh
select * from table1 T where OID in (select top 5 OID from table1 where USERID=T.USERID order by OID desc)
--1 select * from table1 T where OID in (select top 5 OID from table1 where USERID=T.USERID order by OID desc)--2 select * from table1 T where (select count(1) from table1 where USERID=T.USERID and OID>TOID)<5
declare @tb table (id int,name varchar(10),mess varchar(10)) insert into @tb select 1,'a','aaaa' insert into @tb select 2,'a','bbbb' insert into @tb select 3,'a','cccc' insert into @tb select 4,'b','dddd' insert into @tb select 5,'b','eeee' insert into @tb select 6,'b','ffff' insert into @tb select 7,'c','ggg' insert into @tb select 8,'c','hhh' insert into @tb select 9,'c','iiii'select a.* from @tb a where exists (select count(*) from @tb where name = a.name and id < a.id having Count(*) < 2) order by a.id1 a aaaa 2 a bbbb 4 b dddd 5 b eeee 7 c ggg 8 c hhh
select * into #table1 from table1 where 1=2 declare @id int declare cur_user cursor for select distinct userid from table1 open cur_user fetch next from cur_user into @id while @@fetch_status!=-1 begin insert into #table1 select top 5 * from table1 where userid = @id fetch next from cur_user into @id end close cur_user deallocate cur_userselect * from #table1
declare @tb table (oid int,USERID varchar(10),message varchar(10)) insert into @tb select 1,'a','aaaa' insert into @tb select 2,'a','bbbb' insert into @tb select 3,'a','cccc' insert into @tb select 4,'b','dddd' insert into @tb select 5,'b','eeee' insert into @tb select 6,'b','ffff' insert into @tb select 7,'c','ggg' insert into @tb select 8,'c','hhh' insert into @tb select 9,'c','iiii' select * from @tb T where (select count(1) from @tb where USERID=T.USERID and OID<T.OID)<5
2005: select * from ( select *,row=row_number()over(partition by Name order by Name) from T )T1 where row<=52000: select * from T a where OID in(select top 5 OID from T where UserID=a.UserID)
--借用楼上例子 declare @tb table (oid int,USERID varchar(10),message varchar(10)) insert into @tb select 1,'a','aaaa' insert into @tb select 2,'a','bbbb' insert into @tb select 3,'a','cccc' insert into @tb select 4,'b','dddd' insert into @tb select 5,'b','eeee' insert into @tb select 6,'b','ffff' insert into @tb select 7,'c','ggg' insert into @tb select 8,'c','hhh' insert into @tb select 9,'c','iiii'select * from @tb a where oid in(select top 2 oid from @tb where UserID=a.UserID)--取前二条 oid USERID message ----------- ---------- ---------- 1 a aaaa 2 a bbbb 4 b dddd 5 b eeee 7 c ggg 8 c hhh(所影响的行数为 6 行)
select * from table1 T where OID in (select top 5 OID from table1 where USERID=T.USERID order by OID desc)
没发现什么太好的办法 我的做法也是 select * from tb a where message in (select top 5 message from tb where tb.id=a.id) 自己跟自己建立一个联系
select * from tb t where OID in ( select top 5 rdate from tb where USERID=t.USERID order by OID ) order by USERID , OID
insert into @tb select 1,'a','aaaa'
insert into @tb select 2,'a','bbbb'
insert into @tb select 3,'a','cccc'
insert into @tb select 4,'b','dddd'
insert into @tb select 5,'b','eeee'
insert into @tb select 6,'b','ffff'
insert into @tb select 7,'c','ggg'
insert into @tb select 8,'c','hhh'
insert into @tb select 9,'c','iiii'select * from @tb a where id in(select top 2 id from @tb where name=a.name)1 a aaaa
2 a bbbb
4 b dddd
5 b eeee
7 c ggg
8 c hhh
select * from table1 T
where OID in (select top 5 OID from table1 where USERID=T.USERID order by OID desc)--2
select * from table1 T
where (select count(1) from table1 where USERID=T.USERID and OID>TOID)<5
insert into @tb select 1,'a','aaaa'
insert into @tb select 2,'a','bbbb'
insert into @tb select 3,'a','cccc'
insert into @tb select 4,'b','dddd'
insert into @tb select 5,'b','eeee'
insert into @tb select 6,'b','ffff'
insert into @tb select 7,'c','ggg'
insert into @tb select 8,'c','hhh'
insert into @tb select 9,'c','iiii'select a.* from @tb a where exists
(select count(*) from @tb where name = a.name and id < a.id having Count(*) < 2)
order by a.id1 a aaaa
2 a bbbb
4 b dddd
5 b eeee
7 c ggg
8 c hhh
select * into #table1 from table1 where 1=2
declare @id int
declare cur_user cursor for
select distinct userid from table1
open cur_user
fetch next from cur_user into @id
while @@fetch_status!=-1 begin
insert into #table1
select top 5 * from table1 where userid = @id
fetch next from cur_user into @id
end
close cur_user
deallocate cur_userselect * from #table1
insert into @tb select 1,'a','aaaa'
insert into @tb select 2,'a','bbbb'
insert into @tb select 3,'a','cccc'
insert into @tb select 4,'b','dddd'
insert into @tb select 5,'b','eeee'
insert into @tb select 6,'b','ffff'
insert into @tb select 7,'c','ggg'
insert into @tb select 8,'c','hhh'
insert into @tb select 9,'c','iiii'
select * from @tb T
where (select count(1) from @tb where USERID=T.USERID and OID<T.OID)<5
select
*
from
(
select
*,row=row_number()over(partition by Name order by Name)
from
T
)T1
where
row<=52000:
select * from T a where OID in(select top 5 OID from T where UserID=a.UserID)
declare @tb table (oid int,USERID varchar(10),message varchar(10))
insert into @tb select 1,'a','aaaa'
insert into @tb select 2,'a','bbbb'
insert into @tb select 3,'a','cccc'
insert into @tb select 4,'b','dddd'
insert into @tb select 5,'b','eeee'
insert into @tb select 6,'b','ffff'
insert into @tb select 7,'c','ggg'
insert into @tb select 8,'c','hhh'
insert into @tb select 9,'c','iiii'select * from @tb a where oid in(select top 2 oid from @tb where UserID=a.UserID)--取前二条
oid USERID message
----------- ---------- ----------
1 a aaaa
2 a bbbb
4 b dddd
5 b eeee
7 c ggg
8 c hhh(所影响的行数为 6 行)
我的做法也是
select * from tb a where message in (select top 5 message from tb where tb.id=a.id)
自己跟自己建立一个联系
where OID in
(
select top 5 rdate from tb where USERID=t.USERID order by OID
)
order by USERID , OID