Select * From(
select top 10 * from table1 where f=1 order by id desc) A
Union All
Select * From(
select top 10 * from table1 where f=2 order by id desc) B
Union All
Select * From(
select top 10 * from table1 where f=3 order by id desc) C
Union All
Select * From(
select top 10 * from table1 where f=4 order by id desc ) D
WHERE 10>(SELECT COUNT(1) FROM table1 b WHERE a.f=b.f AND b.id>a.id) AND f IN(1,2,3,4)
ORDER BY f,ID DESC
如果id是自增列的話Select * From table1
Where ID In (Select TOP 10 ID From table1 Where f=A.f)
And A.f In (1,2,3,4)
Where ID In (Select TOP 10 ID From table1 Where f=A.f order by id desc)
And A.f In (1,2,3,4)
经过测试,结果是一样的
Select * From table1
Where ID In (Select TOP 10 ID From table1 Where f=A.f order by id desc)
And A.f In (1,2,3,4)
=========================
SELECT * FROM table1 a
WHERE 10 >(SELECT COUNT(1) FROM table1 b WHERE a.f=b.f AND b.id >a.id) AND f IN(1,2,3,4)
ORDER BY f,ID DESC
=========================
但是
参见另贴
http://community.csdn.net/Expert/topic/4900/4900247.xml?temp=.2374079
第一个答案有点小错,排序该放外面
总之
谢谢
Where ID In (Select TOP 10 ID From table1 Where f=A.f order by id desc)
And A.f In (1,2,3,4)
order by id desc