mysql - MySQL 的 Case 语句语法

Question

我有一个运行良好的 MySQL 存储过程

BEGIN 
SELECT ID, SID, SerialNumber, SerialType
FROM orders
WHERE paymentStatus =  'Completed'
AND paymentSuccess =  '1'
AND VerificationStatus IS NULL 
OR VerificationStatus NOT IN ('Found', 'NotFound', 'NotVerified');
END

但是我需要将其更改为根据传递到 SerialType 的值以不同的方式返回。我认为 case 语句可能是执行此操作的最佳方法，但很难找到正确的语法，下面的示例显示了逻辑以及我迄今为止所尝试的内容。

BEGIN 
SELECT ID, SID, SerialNumber, SerialType
FROM orders
CASE
    WHEN SerialType IN ('Var1','Var2') THEN
        WHERE paymentStatus =  'Completed' AND paymentSuccess =  '1';
    WHEN SerialType IN ('Var3','Var4') THEN
        WHERE paymentStatus =  'Completed' AND paymentSuccess =  '1' AND VerificationStatus IS NULL OR VerificationStatus NOT IN ('Found', 'NotFound', 'NotVerified');
END CASE
END

Answer 1

首先，我想知道您的第一个查询是否真的应该是:

SELECT ID, SID, SerialNumber, SerialType
FROM orders
WHERE paymentStatus =  'Completed' AND paymentSuccess =  '1' AND
      (VerificationStatus IS NULL OR VerificationStatus NOT IN ('Found', 'NotFound', 'NotVerified'));

注意括号。

其次，我怀疑您只是想要第二次进行更高级的过滤。这看起来像:

SELECT ID, SID, SerialNumber, SerialType
FROM orders
WHERE (SerialType IN ('Var1', 'Var2') AND paymentStatus =  'Completed' AND paymentSuccess =  '1') OR
      (SerialType IN ('Var3', 'Var4') AND paymentStatus =  'Completed' AND paymentSuccess =  '1' AND 
       VerificationStatus IS NULL OR VerificationStatus NOT IN ('Found', 'NotFound', 'NotVerified')
      )

这又可以简化为:

WHERE paymentStatus =  'Completed' AND paymentSuccess =  '1' AND
      (SerialType IN ('Var1', 'Var2') OR
       SerialType IN ('Var3', 'Var4') AND VerificationStatus IS NULL OR VerificationStatus NOT IN ('Found', 'NotFound', 'NotVerified')
      )