SQL查询，包含group by子句的特定字段上的一条记录

Question

我不知道这是否可能。如果没有，也没关系。当然还有其他方法可以做到这一点。一个同事告诉我这是不可能的，我们就靠这个喝了一轮酒。不，我不介意寻求帮助，但我想我要买。

问题是:给我一份报告，列出每个国家最大单笔订单的顶级客户。目标是在没有子查询、窗口函数或普通连接以外的任何东西的情况下回答问题。 到目前为止，这是我的查询。这是在SQLite中，这并不重要。

select o.customerid, c.companyname, c.country,
od.quantity * od.unitprice as total_order
from orders o
join order_details od on o.orderid = od.orderid
join customers c on o.customerid = c.customerid
group by  c.country
order by c.country, total_order desc;

结果如下。

CustomerID,CompanyName,Country,total_order
OCEAN,"Océano Atlántico Ltda.",Argentina,223.2
ERNSH,"Ernst Handel",Austria,760.0
SUPRD,"Suprêmes délices",Belgium,2592.0
HANAR,"Hanari Carnes",Brazil,77.0
MEREP,"Mère Paillarde",Canada,2000.0
SIMOB,"Simons bistro",Denmark,16.0
WARTH,"Wartian Herkku",Finland,364.8
VINET,"Vins et alcools Chevalier",France,168.0
TOMSP,"Toms Spezialitäten",Germany,167.4
HUNGO,"Hungry Owl All-Night Grocers",Ireland,608.0
MAGAA,"Magazzini Alimentari Riuniti",Italy,43.2
CENTC,"Centro comercial Moctezuma",Mexico,80.0
SANTG,"Santé Gourmet",Norway,54.0
WOLZA,"Wolski  Zajazd",Poland,300.0
FURIB,"Furia Bacalhau e Frutos do Mar",Portugal,396.0
ROMEY,"Romero y tomillo",Spain,7.3
FOLKO,"Folk och fä HB",Sweden,532.0
CHOPS,"Chop-suey Chinese",Switzerland,54.0
BSBEV,"B''s Beverages",UK,240.0
RATTC,"Rattlesnake Canyon Grocery",USA,204.0
HILAA,HILARION-Abastos,Venezuela,877.5

这是一个关于阿根廷的测试查询。

select o.orderid, o.customerid, c.companyname, c.country,
od.quantity * od.unitprice as total_order
from orders o
join order_details od on o.orderid = od.orderid
join customers c on o.customerid = c.customerid
where c.country like "Argentina"
group by  c.country, o.orderid
order by c.country, total_order desc;

下面是测试查询的结果。

OrderID,CustomerID,CompanyName,Country,total_order
10986,OCEAN,"Océano Atlántico Ltda.",Argentina,630.0
10958,OCEAN,"Océano Atlántico Ltda.",Argentina,427.0
10828,RANCH,"Rancho grande",Argentina,405.0
10937,CACTU,"Cactus Comidas para llevar",Argentina,364.8
10819,CACTU,"Cactus Comidas para llevar",Argentina,322.0
10409,OCEAN,"Océano Atlántico Ltda.",Argentina,223.2
10881,CACTU,"Cactus Comidas para llevar",Argentina,150.0
10448,RANCH,"Rancho grande",Argentina,149.4
10531,OCEAN,"Océano Atlántico Ltda.",Argentina,110.0
10916,RANCH,"Rancho grande",Argentina,104.7
10521,CACTU,"Cactus Comidas para llevar",Argentina,54.0
10716,RANCH,"Rancho grande",Argentina,50.0
11019,RANCH,"Rancho grande",Argentina,36.0
10898,OCEAN,"Océano Atlántico Ltda.",Argentina,30.0
11054,CACTU,"Cactus Comidas para llevar",Argentina,25.0
10782,CACTU,"Cactus Comidas para llevar",Argentina,12.5

显然，阿根廷的头号客户是ocsamano Atlántico Ltda。订单为630.00，但第一个结果显示订单为223.2。是的，我可以使用窗口函数或子查询，但挑战是只使用表连接。这可能吗?谢谢。

Answer 1

如果您必须从订单中汇总订单大小，那么我认为这是不可能的。下面不是一个证明。这是对我思想的解释。

如果你在订单表中有可用的订单大小，那么这应该是可能的。

该查询的基本结构是:

select o.*, c.country
from orders o join
customers c
on o.customerid = c.customerid left join
(orders o2 join
customers c2
on o2.customerid = c2.customerid
)
on c2.country = c.country and
o2.ordersize > o.ordersize
where c2.country is null;  -- no bigger order in the country

然而，在您的问题中，您需要汇总来自另一个表的订单大小。这就是问题所在，因为您需要ON子句中的总订单大小，因此聚合必须出现在"之前"。加入。我想不出一个不使用子查询或CTE的方法。

还有其他方法可以获得最大的订单，但排除窗口函数和子查询几乎排除了它们。这并不是说这是不可能的，只是由于聚合问题，我不能轻易地想到一个解决方案。