我试图通过计算每个菜单项ID的出现次数来找到表中最常点的菜单项。
我尝试了一些不同的方法,但是我感到很迷茫。这产生了错误:
“除非聚合列是外部引用,并且聚合出现在HAVING子句或选择列表中的子查询中,否则聚合不能出现在WHERE子句中。”
但是我认为这能够说明我正在努力实现的目标:
SELECT count(itemCode)
FROM OrderProcessing
WHERE count(itemCode)=max(count(itemCode))
我试图通过计算每个菜单项ID的出现次数来找到表中最常点的菜单项。
我尝试了一些不同的方法,但是我感到很迷茫。这产生了错误:
“除非聚合列是外部引用,并且聚合出现在HAVING子句或选择列表中的子查询中,否则聚合不能出现在WHERE子句中。”
但是我认为这能够说明我正在努力实现的目标:
SELECT count(itemCode)
FROM OrderProcessing
WHERE count(itemCode)=max(count(itemCode))
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
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;
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;
这看起来并不美观,但它可能会产生更好的执行计划(排序是一个非常耗费资源的子句)。
即使存在多个具有相同出现次数的菜单项,它也应该返回结果。
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
包含聚合结果的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 );
SELECT MAX(cnt) FROM (SELECT COUNT(*) cnt FROM OrderProcessing GROUP BY itemCode) dt
就可以了。 - shA.t