单SQL查询分组内最小库存对应的商品名、总价及最低库存量

错误原因 #

你原来的SQL仅按group_id分组，未做特殊处理的情况下，非聚合字段product_name会返回分组内随机匹配的记录，因此无法对应库存最小的商品。

可行方案 #

因为你的分组数据量极小（最多5条），以下两种单SQL方案都可以直接使用： #

方案1：兼容MySQL 8.0+/所有支持窗口函数的数据库

用CTE+窗口函数实现，逻辑清晰无兼容风险：

WITH group_goods AS (
    SELECT 
        product_name,
        price,
        (purchased_quantity - sold_quantity) AS stock,
        -- 窗口函数直接计算整个分组的总价
        SUM(price) OVER() AS total_price,
        -- 按库存升序排序，最小库存的行序号为1
        ROW_NUMBER() OVER(ORDER BY (purchased_quantity - sold_quantity) ASC) AS rn
    FROM `tables` 
    WHERE group_id = 1
)
SELECT product_name, total_price, stock AS min_stock
FROM group_goods 
WHERE rn = 1;

方案2：兼容所有MySQL版本（含低版本）

利用字符串函数取排序后的首个商品名，性能足够应对小数据量场景：

SELECT 
    -- 按库存升序拼接商品名后取第一个，即为最小库存对应的商品
    SUBSTRING_INDEX(GROUP_CONCAT(product_name ORDER BY (purchased_quantity - sold_quantity) ASC), ',', 1) AS product_name,
    SUM(price) AS total_price,
    MIN((purchased_quantity - sold_quantity)) AS min_stock
FROM `tables`
WHERE group_id = 1;

注：因分组最多仅5条记录，GROUP_CONCAT默认长度限制完全不会触发截断问题，可放心使用。

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