使用SQL计算一行多列数据中非零最小值的问题求助

问题原因 #

你代码的核心问题出在最小值比较逻辑没有排除0值：如果前序步骤的val为0，0会比你要的有效值（1-6）更小，CASE判断会优先匹配到更小的0值，自然得不到你预期的5。另外手写多列比较的CASE语句冗余度高，也容易写错判断条件。

解决方案 #

不需要写复杂的多层CASE判断，直接用行值表达式过滤0后取最小值即可，以下是适配SQL Server的优化代码（兼容你原来的临时表逻辑）：

-- 临时表生成逻辑保持原有逻辑即可
Select pt.PROJECT_NUMBER, pt.STEP1, pt.STEP2, pt.STEP3, pt.STEP4, pt.STEP5, pt.STEP6,
CASE WHEN STEP1 = 'PARTIAL COMPLETE' OR STEP1 IS NULL OR STEP1 = '' THEN 1 ELSE 0 END AS step1_val,
CASE WHEN STEP2 = 'PARTIAL COMPLETE' OR STEP2 IS NULL OR STEP2 = '' THEN 2 ELSE 0 END AS step2_val,
CASE WHEN STEP3 = 'PARTIAL COMPLETE' OR STEP3 IS NULL OR STEP3 = '' THEN 3 ELSE 0 END AS step3_val,
CASE WHEN STEP4 = 'PARTIAL COMPLETE' OR STEP4 IS NULL OR STEP4 = '' THEN 4 ELSE 0 END AS step4_val,
CASE WHEN STEP5 = 'PARTIAL COMPLETE' OR STEP5 IS NULL OR STEP5 = '' THEN 5 ELSE 0 END AS step5_val,
CASE WHEN STEP6 = 'PARTIAL COMPLETE' OR STEP6 IS NULL OR STEP6 = '' THEN 6 ELSE 0 END AS step6_val
INTO #Temp
FROM ProjectTracker.GIS.PROJECT_TRACKING pt 
WHERE PROJECT_RECEIVED = 'YES' AND PROJECT_NUMBER = '5797'

-- 优化后的最小值计算逻辑
SELECT PROJECT_NUMBER,
(
    SELECT MIN(val) 
    FROM (VALUES (step1_val),(step2_val),(step3_val),(step4_val),(step5_val),(step6_val)) AS v(val)
    WHERE val > 0
) AS TheMin
FROM #Temp

Drop Table #Temp

如果需要处理所有步骤都是0的极端场景，可以在外层加COALESCE函数自定义默认值，比如COALESCE((SELECT MIN(val) FROM ...), 0)即可。

其他数据库适配说明 #

• MySQL 8.0+可以把VALUES子句换成UNION ALL拼接各列值再取MIN
• PostgreSQL可以直接用LEAST函数配合NULLIF处理0：LEAST(NULLIF(step1_val,0), NULLIF(step2_val,0), NULLIF(step3_val,0), NULLIF(step4_val,0), NULLIF(step5_val,0), NULLIF(step6_val,0))

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