sql - SQL 显示计数中的第一个和最后一个值

Question

我需要编写一个查询来显示客户订单的最大和最小订单数。

我试过了：

(SELECT TOP 1 CustomerID, COUNT(*) AS Number_Of_Orders
FROM Orders
GROUP BY CustomerID
ORDER BY COUNT(*) ASC)

UNION ALL

(SELECT TOP 1 CustomerID, COUNT(*) AS Number_Of_Orders
FROM Orders
GROUP BY CustomerID
ORDER BY COUNT(*) DESC)

但是我没有成功合并输出，我得到了错误Incorrect syntax near the keyword 'ORDER'。

我怎么能得到那个？

标签： sqlsql-servertsqlaggregate-functionsnorthwind

Answer 1

我不确定我是否要运行两次聚合，所以使用窗口函数：

SELECT CustomerID, Number_Of_Orders
FROM (SELECT CustomerID, COUNT(*) AS Number_Of_Orders,
             ROW_NUMBER() OVER (ORDER BY COUNT(*) ASC) as seqnum_asc,
             ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) as seqnum_desc
      FROM Orders
      GROUP BY CustomerID
     ) c
WHERE seqnum_asc = 1 OR seqnum_desc = 1;