SQL - 按订单查找客户的最大位置 $

Question

我有一个包含客户 ID、位置 ID 及其订单值的表。我需要为支出最高的每个客户选择位置 ID

Customer   |  Location   |  Order $
1          |  1A         | 100
1          |  1A         | 20
1          |  1B         | 100
2          |  2A         | 50
2          |  2B         | 20
2          |  2B         | 50

所以我会得到

Customer   |  Location   |  Order $
1          |  1A         | 120
2          |  2B         | 70

我尝试了这样的事情：

SELECT
   a.CUST
  ,a.LOC
  ,c.BOOKINGS
FROM (SELECT DISTINCT TOP 1 b.CUST, b.LOC, sum(b.ORDER_VAL) as BOOKINGS
   FROM ORDER_TABLE b
   GROUP BY b.CUST, b.LOC
   ORDER BY BOOKINGS DESC) as c
INNER JOIN ORDER_TABLE a
   ON a.CUST =  c.CUST

但这只返回最高顺序。

Answer 1

只需使用变量来模拟ROW_NUM()

演示

SELECT *
FROM ( SELECT `Customer`, `Location`, SUM(`Order`) as `Order`, 
               @rn := IF(@customer = `Customer`,
                         @rn + 1,
                         IF(@customer := `Customer`, 1, 1)
                        ) as rn
        FROM Table1
        CROSS JOIN (SELECT @rn := 0, @customer := '') as par
        GROUP BY `Customer`, `Location`
        ORDER BY `Customer`,  SUM(`Order`)  DESC
      ) t
WHERE t.rn = 1

Answer 2

冷杉 您必须对每个位置的值求和：

select Customer, Location, Sum(Order) as tot_order
from order_table
group by Customer, Location

然后，您可以使用 MAX 获得最大订单，以及具有group_concat组合的顶部位置，该位置将返回所有位置，按总描述排序，并substring_index，以便仅获得顶部位置：

select
  Customer,
  substring_index(
    group_concat(Location order by tot_order desc),
    ',', 1
  ) as location,
  Max(tot_order) as max_order
from (
  select Customer, Location, Sum(Order) as tot_order
  from order_table
  group by Customer, Location
) s
group by Customer

(如果存在平局，两个位置具有相同的顶部顺序，则此查询将仅返回一个(

Answer 3

这似乎是使用聚合函数问题的顺序。 这是我的刺伤;

SELECT 
  c.customer, 
  c.location, 
  SUM(`order`) as `order_total`,
  (
  SELECT 
    SUM(`order`) as `order_total`
    FROM customer cm

    WHERE cm.customer = c.customer
    GROUP BY location
    ORDER BY `order_total` DESC LIMIT 1
  ) as max_order_amount

FROM customer c   
GROUP BY location
HAVING max_order_amount = order_total

这是SQL小提琴。 http://sqlfiddle.com/#!9/2ac0d1/1

Answer 4

这就是我的处理方式(也许不是最好的方法？ - 我首先使用 CTE 编写它，只是看到 MySQL 不支持 CTE，然后切换到编写相同的子查询两次：

SELECT B.Customer, C.Location, B.MaxOrderTotal
FROM
(
    SELECT A.Customer, MAX(A.OrderTotal) AS MaxOrderTotal
    FROM
    (
        SELECT Customer, Location, SUM(`Order`) AS OrderTotal
        FROM Table1
        GROUP BY Customer, Location
    ) AS A
    GROUP BY A.Customer
) AS B INNER JOIN 
(
    SELECT Customer, Location, SUM(`Order`) AS OrderTotal
    FROM Table1
    GROUP BY Customer, Location
) AS C ON B.Customer = C.Customer AND B.MaxOrderTotal = C.OrderTotal;

编辑：使用提供的表格结构

此解决方案将在平局的情况下提供多行。这个解决方案的SQL小提琴

Answer 5

怎么样：

select a.* 
from (
    select customer, location, SUM(val) as s 
    from orders 
    group by customer, location
    ) as a 
left join  
    (
    select customer, MAX(b.tot) as t 
    from ( 
        select customer, location, SUM(val) as tot 
        from orders 
        group by customer, location
        ) as b 
        group by customer
    ) as c 
on a.customer = c.customer where a.s = c.t;

Answer 6

with 
Q_1 as
(
select customer,location, sum(order_$) as order_sum
from cust_order
group by customer,location
order by customer, order_sum desc
),
Q_2 as
(
  select customer,max(order_sum) as order_max
  from Q_1
  group by customer
),
Q_3 as
(
  select Q_1.customer,Q_1.location,Q_1.order_sum
  from Q_1 inner join Q_2 on Q_1.customer = Q_2.customer and Q_1.order_sum = Q_2.order_max
)
select * from Q_3

Q_1 - 选择正常聚合，Q_2 - 从Q_1

中选择最大(聚合(，Q_3从Q_1中选择与Q_2匹配的客户，位置，总和(订单(