SQL比较同一列，但依赖于其他列

Question

我需要找到所有客户的名字，他们的总价高于同一订单日期和同一生产商的其他客户。

Customer = CustomerNr (Int)
- Name (varchar)
- Adress (varchar)
- Country (int)
- Phone (char)
Order = OrderNr (Int)
- Customer (int)
- Status (char)
- Total Price (Decimal)
- Order date(date)
- Producer (char)

Answer 1

您正在寻找每个订单日期、生产商的最大总价，因此您可以使用例如

with totalprice(customer, order_date, producer, sum_total_price) as
(
select customer, order_date, producer, sum(total_price) sum_total_price
from order
group by customer, order_date, producer
)
select * 
from totalprice t1
where sum_total_price = (
select max(sum_total_price)
from totalprice t2
where t1.order_date = t2.order_date and t1.producer = t2.producer
)

和没有WITH的相同查询

select * 
from (
select customer, order_date, producer, sum(total_price) sum_total_price
from order
group by customer, order_date, producer
) t1
where sum_total_price = (
select max(sum_total_price)
from (
select customer, order_date, producer, sum(total_price) sum_total_price
from order
group by customer, order_date, producer
) t2
where t1.order_date = t2.order_date and t1.producer = t2.producer
)

Answer 2

编写查询的正确方法使用窗口函数：

select co.*
from (select co.CustomerNr, co.name, o.order_date, sum(o.total_price) as total_price,
max(sum(o.total_price)) over (partition by order_date) as max_daily_total_price
from order o join
customer co
on co.CustomerNr = o.Customer
group by co.CustomerNr, co.name, order_date
) co
where total_price = max_daily_total_price;