sql - 有没有办法做像 SQL NOT top 语句这样的事情？

Question

我正在尝试创建一个 SQL 语句，该语句为我提供前 X 条记录，然后将所有其他记录相加。第一部分很简单...

select top 3 Department, Sum(sales) as TotalSales
from Sales
group by Department

如果我 就好了工会第二个查询类似于...

select NOT top 3 "Others" as Department, Sum(sales) as TotalSales
from Sales
group by Department

...对于看起来像的结果集，

Department    TotalSales
-----------   -----------
Mens Clothes  120.00
Jewelry       113.00
Shoes          98.00
Others        312.00

有没有办法在 TOP 上做与 NOT 运算符等效的操作？ (我知道我可能可以制作一个顶级 X 的临时表并使用它，但我更喜欢一个只有一个 sql 语句的解决方案。)

Answer 1

WITH    q AS
        (
        SELECT  ROW_NUMBER() OVER (ORDER BY SUM(sales) DESC) rn,
                CASE
                WHEN ROW_NUMBER() OVER (ORDER BY SUM(sales) DESC) <= 3 THEN
                        department
                ELSE
                        'Others'
                END AS dept,
                SUM(sales) AS sales
        FROM    sales
        GROUP BY
                department
        )
SELECT  dept, SUM(sales)
FROM    q
GROUP BY
        dept
ORDER BY
        MAX(rn)