SQL：如何在表中找到某一列值出现最频繁的项？

Question

我试图通过计算每个菜单项ID的出现次数来找到表中最常点的菜单项。

我尝试了一些不同的方法，但是我感到很迷茫。这产生了错误：

“除非聚合列是外部引用，并且聚合出现在HAVING子句或选择列表中的子查询中，否则聚合不能出现在WHERE子句中。”

但是我认为这能够说明我正在努力实现的目标：

SELECT count(itemCode) 
FROM OrderProcessing 
WHERE count(itemCode)=max(count(itemCode))

- camohreally

Answer 1

I. 修改后的原始查询语句 (在 @DanGuzman 提出绑定问题后更新)

选择所有与最受欢迎的项目数量相同的项目:

SELECT itemCode , count(*) as MaxCount
FROM OrderProcessing
GROUP BY itemCode
HAVING count(*) = 
-- count of most wanted item
(select top 1 count(*) 
 from OrderProcessing 
 group by itemCode 
 order by count(*) desc)

II. 选择最常订购的一件物品的查询

SELECT top 1 itemCode --, count(*) as MaxCount  --optional
FROM OrderProcessing
GROUP BY itemCode
ORDER BY count(*) DESC

- ASh

---

@DanGuzman，是的，“top 1”将忽略并列。我现在应该改进解决方案。 - ASh

你的查询只返回最大出现次数，但是 OP 想要那些出现次数最多的项，无论如何，尝试这个 SELECT MAX(cnt) FROM (SELECT COUNT(*) cnt FROM OrderProcessing GROUP BY itemCode) dt 就可以了。 - shA.t

Answer 2

如果您只想要一行数据，我建议使用order by：

SELECT TOP 1 itemCode, count(itemCode)
FROM OrderProcessing
GROUP BY itemCode
ORDER BY count(itemCode);

如果您想获取所有计数最大的项，请使用WITH TIES：

SELECT TOP 1 WITH TIES itemCode, count(itemCode)
FROM OrderProcessing
GROUP BY itemCode
ORDER BY count(itemCode);

如果你想要更加高级的操作，可以使用窗口函数。以下是一个示例：

SELECT itemCode, cnt
FROM (SELECT itemCode, count(itemCode) as cnt,
             MAX(count(itemCode)) OVER () as maxcnt
      FROM OrderProcessing
      GROUP BY itemCode
     ) op
WHERE cnt = maxcnt;

- Gordon Linoff

Answer 3

这是另一种实现的方式：

SELECT OP.itemCode
FROM (
    SELECT OP.itemCode, OP.TotalRows, MAX(TotalRows) OVER() AS MaxRows
    FROM (
        SELECT OP.itemCode, COUNT(*) AS TotalRows
        FROM dbo.OrderProcessing AS OP
        GROUP BY OP.itemCode
    ) AS OP
) AS OP
WHERE OP.TotalRows = OP.MaxRows;

这看起来并不美观，但它可能会产生更好的执行计划（排序是一个非常耗费资源的子句）。

即使存在多个具有相同出现次数的菜单项，它也应该返回结果。

- Evaldas Buinauskas

Answer 4

我使用这个查询来查找出现次数最高的项目：

SELECT t.*
FROM OrderProcessing t
    JOIN
    (SELECT itemCode, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) seq
    FROM OrderProcessing
    GROUP BY itemCode) dt
    ON t.itemCode= dt.itemCode
WHERE dt.seq = 1

查询出现次数最高的数量的查询语句如下：

SELECT MAX(cnt) 
FROM (SELECT COUNT(*) cnt 
      FROM OrderProcessing 
      GROUP BY itemCode) dt 

- shA.t

Answer 5

包含聚合结果的where子句被称为having子句。但是，您不能嵌套聚合函数：Max( Count( * ) )。

但是，您可以将它们分层。使用CTE很好实现这一点：

with
Data( ItemID )as(
    select 1 union all
    select 2 union all
    select 2 union all
    select 3 union all
    select 3 union all
    select 3
),
Sums( ItemID, TotalSold )as(
    select  ItemID, Count( * )
    from    Data
    group by ItemID
)
select  *
from    Sums
where   TotalSold =(
    select Max( TotalSold )
    from    Sums );

这将显示所有交易最多的物品。然而，基于这个查询所要回答的问题，我认为显示所有并列的结果才是正确的。如果物品X和Y都卖得最好，那么提交物品X作为答案，那么暗示其他所有物品，包括物品Y，都卖得更少。这不是正确的答案。我建议你与分析师或提出这个结果的人进一步讨论此事。

- TommCatt