所以我的任务是要求找到在Northwind中发送最多产品的供应商ID。这是我的代码
select s.SupplierID, count(p.ProductID)
from Suppliers s join Products p
on s.SupplierID = p.SupplierID
group by s.SupplierID
having count(p.ProductID) in
(select top 1 count(p.ProductID)
from Products
order by count(p.ProductID) desc)
但我得到的是我的结果,他们只是在没有前1名的情况下计数,订单不起作用
我只会使用ORDER BY
和TOP
:
select top (1) s.SupplierID, count(*) cnt
from Suppliers s
join Products p on s.SupplierID = p.SupplierID
group by s.SupplierID
order by cnt desc
如果要允许领带,请改用top (1) with ties
。
子查询中需要一个group by
:
having count(p.ProductID) in (select top 1 count(p.ProductID)
from Products
group by SupplierID
order by count(p.ProductID) desc
)
然而,一个简单得多的公式使用窗口函数:
select top (1) p.SupplierID, count(*) as cnt,
from products p
group by p.SupplierID
order by count(*) desc;
请注意,您不需要Suppliers
,因为id
已经在products
中,并且您没有选择任何其他列。
此外,如果您希望在发生平局时有多行,则可能需要select top (1) with ties
。