MySQL中如何获取行内最小值及对应列名？

要同时拿到priceTable表中partno='OL0003'行里supplier1到supplier5的最小值和对应的列名，这里有两种实用方案，你可以根据场景选择：

方案一：用CASE语句快速匹配（适合单一最小值场景） #

这种方法基于你已有的查询，通过CASE判断哪个列的值等于最小值，直接返回对应的列名。如果有多个列同时等于最小值，只会返回第一个匹配的列名。

SELECT 
    least(supplier1, supplier2, supplier3, supplier4, supplier5) AS minValue,
    CASE least(supplier1, supplier2, supplier3, supplier4, supplier5)
        WHEN supplier1 THEN 'supplier1'
        WHEN supplier2 THEN 'supplier2'
        WHEN supplier3 THEN 'supplier3'
        WHEN supplier4 THEN 'supplier4'
        WHEN supplier5 THEN 'supplier5'
    END AS minSupplier
FROM priceTable 
WHERE partno='OL0003';

方案二：列转行查询（支持多个并列最小值） #

如果存在多个供应商价格同为最小值的情况，这个方案会返回所有对应的列名。核心思路是通过UNION ALL把原本的列数据转成行，再筛选出等于最小值的记录。

SELECT supplier_name AS minSupplier, supplier_price AS minValue
FROM (
    -- 将每一列转为一行记录
    SELECT partno, 'supplier1' AS supplier_name, supplier1 AS supplier_price FROM priceTable
    UNION ALL
    SELECT partno, 'supplier2' AS supplier_name, supplier2 AS supplier_price FROM priceTable
    UNION ALL
    SELECT partno, 'supplier3' AS supplier_name, supplier3 AS supplier_price FROM priceTable
    UNION ALL
    SELECT partno, 'supplier4' AS supplier_name, supplier4 AS supplier_price FROM priceTable
    UNION ALL
    SELECT partno, 'supplier5' AS supplier_name, supplier5 AS supplier_price FROM priceTable
) AS unpivoted_data
WHERE partno='OL0003'
-- 关联子查询获取当前partno的最小值
AND supplier_price = (
    SELECT least(supplier1, supplier2, supplier3, supplier4, supplier5) 
    FROM priceTable 
    WHERE partno='OL0003'
);

两种方案各有优势：方案一代码简洁、执行效率高，适合确定只有一个最小值的场景；方案二更灵活，能覆盖多个并列最小值的情况，结果更全面。

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