代码之家 › 专栏 › 技术社区 › fredolito

选择每个地区订单总额最高的客户,以及两个订单总额

window-functions greatest-n-per-group sql-server sql

fredolito · 技术社区 · 9 年前

我有一张这样的桌子:

SaleID    Region      Customer        OrderAmt
1         North       Keesha          10
2         West        Mary            10
3         North       Winston         10
4         North       John            10
5         North       Keesha          10
6         West        John            10
7         West        Mary            10
8         South       John            10

使用SQL Server 2012,选择每个地区订单最高的客户的最佳方式是什么,以及该客户和该地区的总数,即:

Region    Customer    CustAmt   RegAmt
North     Keesha      20        40
West      Mary        20        30
South     John        10        10

尽管多个区域可能包含相同的名称,但我们希望 CustAmt 仅在每个区域内,而不是跨区域(即,在最后一行中,John在南部区域的总数是10,而不是30)。

3 回复 | 直到 9 年前

Erwin Brandstetter 9 年前

使用CTE:

WITH cte AS (
   SELECT Region, Customer
        , sum(OrderAmt) AS CustAmt
        , sum(sum(OrderAmt)) OVER (PARTITION BY Region) AS RegAmt
        , row_number()  OVER (PARTITION BY Region ORDER BY sum(OrderAmt) DESC) AS rn
   FROM   tbl
   GROUP  BY Region, Customer
   )
SELECT Region, Customer, CustAmt, RegAmt
FROM   cte
WHERE  rn = 1;

或者,与子查询相同:

SELECT Region, Customer, CustAmt, RegAmt
FROM  (
   SELECT Region, Customer
        , sum(OrderAmt) AS CustAmt
        , sum(sum(OrderAmt)) OVER (PARTITION BY Region) AS RegAmt
        , row_number()  OVER (PARTITION BY Region ORDER BY sum(OrderAmt) DESC) AS rn
   FROM   tbl
   GROUP  BY Region, Customer
   ) sub
WHERE  rn = 1;

关键特性是聚合上的窗口函数,因此我们只需要 仅有一个的 CTE/子查询。

SQL Fiddle.

Vadim Loboda 9 年前

在一个选择中:

select top 1 with ties
    Region   , 
    Customer ,
    CustAmt  =  sum(OrderAmt),
    RegAmt   =  sum(sum(OrderAmt)) over (partition by Region)   
from
    your_table
group by    
    Region, 
    Customer
order by 
    row_number() over(partition by Region order by sum(OrderAmt) desc);

SQL Fiddle

Felix Pamittan 9 年前

您可以使用多个 CTE s和窗口函数:

WITH Cte AS(
    SELECT *,
        CustAmt = SUM(OrderAmt) OVER(PARTITION BY Customer, Region),
        RegAmt  = SUM(OrderAmt) OVER(PARTITION BY Region)
    FROM Sales
),
CteFinal AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY Region ORDER BY CustAmt DESC)
    FROM Cte
)
SELECT
    Region, Customer, CustAmt, RegAmt
FROM CteFinal WHERE rn = 1

SQL Fiddle