--如果ID是唯一的字段。 select A.* from 表名 as A where id in (select top 50 id from 表名 where [name]=A.[name] and price>100)
select * from tbName as tmp where (select count(*) from tbName where name=tmp.name and price>100 and id<tmp.id)<50
select * from 表 as A where id in ( select top 50 id from 表 where name=A.name and price>100)
select t.* from 表 t where t.id in(select top 50 id from 表 where name=t.name and price>100)
select * from 表 ta where id in (select top 50 id from 表 where price>100 and name=ta.name order by price desc)
select t.* from 表 t where t.id in(select top 50 id from 表 where name=t.name and price>100 order by id)
create table T(id int, name varchar(10)) insert T select 1, 'AA' insert T select 2, 'AA' insert T select 3, 'BB' insert T select 4, 'BB' insert T select 5, 'AA' insert T select 6, 'BB' --取每組的兩條 select * from T as tmp where (select count(*) from T where name=tmp.name and id>tmp.id)<2 order by name, id--result id name ----------- ---------- 2 AA 5 AA 4 BB 6 BB(4 row(s) affected)
select A.*
from 表名 as A
where id in (select top 50 id from 表名 where [name]=A.[name] and price>100)
where (select count(*) from tbName where name=tmp.name and price>100 and id<tmp.id)<50
where id in ( select top 50 id from 表 where name=A.name and price>100)
(select top 50 id from 表 where price>100 and name=ta.name order by price desc)
insert T select 1, 'AA'
insert T select 2, 'AA'
insert T select 3, 'BB'
insert T select 4, 'BB'
insert T select 5, 'AA'
insert T select 6, 'BB'
--取每組的兩條
select * from T as tmp
where (select count(*) from T where name=tmp.name and id>tmp.id)<2
order by name, id--result
id name
----------- ----------
2 AA
5 AA
4 BB
6 BB(4 row(s) affected)