我在从数据库中获取最畅销的产品时遇到问题。这是我尝试过的代码:
SELECT productid
FROM soldvia
GROUP BY productid
HAVING SUM(noofitems) = (SELECT MAX(SUM(noofitems))
FROM soldvia
GROUP BY productid);
我得到这个错误:
Msg 130,级别15,状态1,第68行
无法对包含聚合或子查询的表达式执行聚合函数。
我不知道自己做错了什么,请帮忙。
在SQL Server中,您可以将其写成:
SELECT productid
FROM soldvia
GROUP BY productid
HAVING SUM(noofitems) = (SELECT TOP (1) SUM(noofitems)
FROM soldvia
GROUP BY productid
ORDER BY SUM(noofitems) DESC
);
不过,更典型的编写查询的方法是使用RANK()
或窗口函数:
SELECT productid
FROM (SELECT productid, SUM(noofitems) as numitems,
RANK() OVER (ORDER BY SUM(noofitems) DESC) as seqnum
FROM soldvia
GROUP BY productid
) t
WHERE seqnum = 1;