MySQL中如何无需自连接实现基于列间最值选取对应字段值？

当然可以完全避免自连接！我们可以通过窗口函数+条件聚合的方式来实现你的需求，这种方法不仅代码更简洁，效率也比自连接更高（只需要扫描一次表）。

先看完整SQL语句 #

SELECT
    id,
    MAX(CASE WHEN supplier = 'Microsoft' THEN price1 END) AS price_microsoft,
    MAX(CASE WHEN supplier = 'Apple' THEN price1 END) AS price_apple,
    MAX(CASE WHEN supplier = 'IBM' THEN price1 END) AS price_ibm,
    MIN(price1) AS best_price,
    MAX(CASE WHEN rn = 1 THEN quantity1 END) AS best_price_corresponding_quantity
FROM (
    SELECT
        id,
        supplier,
        price1,
        quantity1,
        -- 按id分组，给价格最低的行标记为1
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY price1) AS rn
    FROM prices
) t
GROUP BY id;

分步解释 #

1. 子查询：标记最低价格的行
   用ROW_NUMBER()窗口函数，按id分组，再按price1升序排序。这样每个id下价格最低的那一行会被标记为rn=1。如果有多个行价格相同且都是最低值，你可以换成RANK()来保留所有符合条件的行（不过你的例子里每个id只有一个最低价格，所以ROW_NUMBER()足够）。

2. 外层查询：行转列+聚合结果

   • 行转列：通过MAX(CASE...)的条件聚合，把不同供应商的price1转换成单独的列（因为每个id下每个供应商只有一行，用MAX或MIN都可以）。
   • 获取最低价格：直接用MIN(price1)就能得到每个id下的最低价格（也就是你要的best price）。
   • 获取对应数量：用MAX(CASE WHEN rn=1 THEN quantity1 END)提取标记为rn=1的行的quantity1，也就是最低价格对应的数量。

补充说明 #

• 如果你使用的是MySQL 8.0及以上版本，也可以简化成不用子查询的写法（不过子查询的方式兼容性更好）：

  SELECT
      id,
      MAX(CASE WHEN supplier = 'Microsoft' THEN price1 END) AS price_microsoft,
      MAX(CASE WHEN supplier = 'Apple' THEN price1 END) AS price_apple,
      MAX(CASE WHEN supplier = 'IBM' THEN price1 END) AS price_ibm,
      MIN(price1) AS best_price,
      FIRST_VALUE(quantity1) OVER (PARTITION BY id ORDER BY price1) AS best_price_corresponding_quantity
  FROM prices
  GROUP BY id;
  

• 注意：你给出的期望结果中id=2的对应数量似乎有误，根据原始数据，最低价格6,78对应的quantity应该是6000而非4000。

内容的提问来源于stack exchange，提问作者AndreasW