请在下面尝试,在子查询中使用TOP关键字。
Select GroupID,ItemName,COUNT(ItemName) as ItemCount
FROM Table t1
WHERE ItemName in (
Select TOP 10 t2.ItemName
from Table t2
where t2.GroupID = t1.GroupID
order by count(t2.ItemName) desc,t2.ItemName
)
GROUP BY GROUPID,ItemName
Order By GROUPID,COUNT(ItemName) DESC;
,
您可以使用相关子查询。这是一种方法:
select GroupID,count(*) as ItemCount
from Table as t
group by groupid,ItemName
having count(*) in (select top 10 count(*)
from table as t2
where t2.groupid = t.groupid
group by t2.groupid,t2.itemname
order by count(*) desc
)
order by count(*) desc;
注意:如果有联系,则给定组的行数可能超过10。
,
您是否可以尝试从中选择前10名并在第二个选择中包括订单依据来创建CTE(通用表表达式)。
它应该可以工作,如果不可以,请通知我。
with cte as (
Select GroupID,COUNT(ItemName) as ItemCount
FROM Table
GROUP BY GROUPID,ItemName
)
Select top(10)*
from cte
Order By cte.ItemCount DESC
本文链接:https://www.f2er.com/3092747.html