php - Mysql 中的 IF 函数和带有 LEAST() 的 PHP 语句

Question

我目前正在尝试获取一个脚本来显示两列之间的最小值。我遇到的问题是某些行在一列中显示 N/A 或 NA，因此它会忽略它。我怎样才能让它显示该行的值，即使它是 NA。

例子

Name     | pCst | cCst
______________________
Player 1 |  352 | 392
Player 2 |  NA  | 400

所以在上面我想为玩家 2 显示 400，为玩家 1 显示 352

"SELECT LEAST(pCst,cCst) 
AS Cost
FROM users";

编辑:

"SELECT name,make,model
                    CASE
                    WHEN pCost IS NULL THEN cCost
                    WHEN cCost IS NULL THEN pCost
                    ELSE LEAST(cCost, pCost)
                    END AS Cost
                    FROM users"
                    ;

使用上面的示例这不起作用。如果我删除名称、制造商、型号，它会起作用，但如果我添加它们，脚本不会执行

Answer 1

这是发明 NULL 的原因之一。将您的值存储为字符串会使事情变得复杂，最终需要进行大量转换:

SELECT CASE 
      WHEN pCst IN ('NA', 'N/A') AND cCst IN ('NA', 'N/A') THEN 0
      WHEN pCst IN ('NA', 'N/A') THEN CAST(cCst AS UNSIGNED) 
      WHEN cCst IN ('NA', 'N/A') THEN CAST(pCst AS UNSIGNED)
      ELSE LEAST(CAST(cCst AS UNSIGNED), CAST(pCst AS UNSIGNED)
   END AS Cost
FROM users
;

如果当两者都是“NA”时你真的想要某种形式的“NA”，那么需要更多(不同的)转换......

SELECT CASE 
      WHEN pCst IN ('NA', 'N/A') AND cCst IN ('NA', 'N/A') THEN 'NA'
      WHEN pCst IN ('NA', 'N/A') THEN cCst
      WHEN cCst IN ('NA', 'N/A') THEN pCst
      ELSE CAST(LEAST(CAST(cCst AS UNSIGNED), CAST(pCst AS UNSIGNED) AS CHAR)
   END AS Cost
FROM users
;

如果您为该字段使用可为空的整数类型，它会更简单:

SELECT CASE 
      WHEN pCst IS NULL THEN cCst
      WHEN cCst IS NULL THEN pCst
      ELSE LEAST(pCst, cCst)
   END AS Cost
FROM users
;

;