具有 Or 条件的 Mysql 案例

php mysql sql

2022-08-30 23:02:15

如何在mysql查询中编写一个案例，用于检查特定列的null或0

CREATE TABLE tblConfirmationStatus (Confirm_Status TINY INT)

INSERT INTO tblConfirmationStatus 
Confirm_Status
VALUES
(1),
(0),
(1),
({null}),
(0),
(1),
({null})

所需输出

确认状态

   Confirmed
   Not Confirmed
   Confirmed
   Not Confirmed
   Not Confirmed
   Confirmed
   Not Confirmed

0 或空 - 未确认，1-已确认

SELECT CASE Confirm_Status 
            WHEN NULL OR 0 THEN 'Not Confirmed' 
             ELSE  'Confirmed' END AS ConfirmStatus
  FROM tblConfirmationStatus;

答案 1

CASE 语句有两个选项 - 您发布的那个，或者：

SELECT CASE 
        WHEN Confirm_Status IS NULL OR Confirm_Status = 0 THEN 'Not Confirmed' 
        ELSE  'Confirmed' 
       END AS ConfirmStatus

但您可以使用：

SELECT CASE 
        WHEN Confirm_Status > 0 THEN 'Confirmed' 
        ELSE  'Not Confirmed' 
       END AS ConfirmStatus

NULL是没有值，因此检查大于零的值应属于与零相同的类别。

答案 2

SELECT IF((Confirm_Status IS NULL OR Confirm_Status = 0), 
           'Not Confirmed', 'Confirmed') AS ConfirmStatus
FROM tblConfirmationStatus;