SQL - 如何按 id 进行分组并识别具有最高值的列？

Question

我遇到了 SQL 挑战，需要一些帮助。

下面是一个简化的示例，在我的实际情况中，慢速 VIEW 中有大约 500k 行。因此，如果您有一个也有效的解决方案，我将不胜感激。我想我必须以一种或另一种方式使用 GROUP BY，但我不确定。

假设我有一张这样的 table

╔═════════╦══════════╦══════════╦═══════╗
║ ORDERID ║   NAME   ║   TYPE   ║ PRICE ║
╠═════════╬══════════╬══════════╬═══════╣
║       1 ║ Broccoli ║ Food     ║ 1     ║
║       1 ║ Beer     ║ Beverage ║ 5     ║
║       1 ║ Coke     ║ Beverage ║ 2     ║
║       2 ║ Beef     ║ Food     ║ 2.5   ║
║       2 ║ Juice    ║ Beverage ║ 1.5   ║
║       3 ║ Beer     ║ Beverage ║ 5     ║
║       4 ║ Tomato   ║ Food     ║ 1     ║
║       4 ║ Apple    ║ Food     ║ 1     ║
║       4 ║ Broccoli ║ Food     ║ 1     ║
╚═════════╩══════════╩══════════╩═══════╝

所以我想做的是:

在每个订单中，如果同时有食品和饮料订单行，我想要最高的饮料价格

所以在这个例子中我想要一个这样的结果集:

╔═════════╦═══════╦═══════╗
║ ORDERID ║ NAME  ║ PRICE ║
╠═════════╬═══════╬═══════╣
║       1 ║ Beer  ║ 5     ║
║       2 ║ Juice ║ 1.5   ║
╚═════════╩═══════╩═══════╝

我怎样才能有效地实现这一目标？

Answer 1

您可以使用子查询来获取每个食品和饮料订单的 max(price)，然后将其连接回您的表以获取结果:

select t1.orderid,
  t1.name,
  t1.price
from yourtable t1
inner join
(
  select max(price) MaxPrice, orderid
  from yourtable t
  where type = 'Beverage'
    and exists (select orderid
                from yourtable o
                where type in ('Food', 'Beverage')
                  and t.orderid = o.orderid
                group by orderid
                having count(distinct type) = 2)
  group by orderid
) t2
  on t1.orderid = t2.orderid
  and t1.price = t2.MaxPrice

参见SQL Fiddle with Demo

结果是:

| ORDERID |  NAME | PRICE |
---------------------------
|       1 |  Beer |     5 |
|       2 | Juice |   1.5 |