--意思是分组求前10吧select distinct b.* from news a cross apply (select top 10 * from news where cid=a.cid order by id )b
2楼得到的是全部cid的 你要求哪些CID的自己去处理下。
查每個CID是90%的數據? code=SQL]SELECT * FROM (select *,NTILE(10)OVER(PARTITION BY CID ORDER BY ID) AS NT from News where ','+@Cid+',' like '%,'+rtrim(ID)+',%')t WHERE T<10[/code]
改改 SELECT * FROM (select *,NTILE(10)OVER(PARTITION BY CID ORDER BY ID) AS NT from News where ','+@Cid+',' like '%,'+rtrim(ID)+',%')t WHERE NT<10
select t.* from news t where cid in (select top 10 cid from news where id = t.id order by cid)select t.* from news t where cid in (select top 10 cid from news where id = t.id order by cid desc)
貌似写反了,更改为如下: select t.* from news t where id in (select top 10 id from news where cid = t.cid order by id)select t.* from news t where id in (select top 10 id from news where cid = t.cid order by id desc)
这个能得到总共30条数据吗(cid=1 ,10条.....)
如果每个CID都至少有10条数据以上,则能得到30条,否则则不能.
我个人认为(你可以自己测试):select t.* from news t where id in (select top 10 id from news where cid = t.cid order by id)select t.* from news t where id in (select top 10 id from news where cid = t.cid order by id desc)
distinct b.*
from
news a
cross apply
(select top 10 * from news where cid=a.cid order by id )b
code=SQL]SELECT *
FROM (select *,NTILE(10)OVER(PARTITION BY CID ORDER BY ID) AS NT from News where ','+@Cid+',' like '%,'+rtrim(ID)+',%')t
WHERE T<10[/code]
SELECT *
FROM (select *,NTILE(10)OVER(PARTITION BY CID ORDER BY ID) AS NT from News where ','+@Cid+',' like '%,'+rtrim(ID)+',%')t
WHERE NT<10
select t.* from news t where id in (select top 10 id from news where cid = t.cid order by id)select t.* from news t where id in (select top 10 id from news where cid = t.cid order by id desc)