MySQL技术问询：如何标识SELECT语句WHERE子句中触发的OR条件

当然可行！而且有几种不同的实现方式，能满足你不同的需求——不管是只返回第一个匹配的条件，还是把所有触发的条件都列出来，甚至应对未来30+条件的扩展性需求。

先提个小细节：你的原WHERE子句存在优先级问题，AND的优先级比OR高，所以最后那段DAY(...) AND WEEKDAY(...)其实只和Details_PNarration LIKE '%SAL%'绑定，这大概率不是你想要的逻辑。建议给所有OR条件整体加括号，修正后的WHERE子句应该是这样：

WHERE 
  (Details_Pn LIKE '%SALARY%' 
   OR Details_Pn LIKE '%Mid MTH%' 
   OR Details_Pn LIKE '%Mid Month%' 
   OR Details_Pn LIKE '%Pay%' 
   OR Details_PNarration LIKE '%SAL%')
  AND DAY(Details_TransactionDate) BETWEEN 23 AND 25 
  AND WEEKDAY(Details_TransactionDate) BETWEEN 0 AND 4;

方案1：用CASE WHEN返回第一个匹配的条件 #

这是最直接的写法，适合只需要知道首个触发条件的场景。CASE是短路执行的，会返回第一个满足的条件：

SELECT 
  Details_TransactionDate as Transaction_Date, 
  Details_Credit as Credit, 
  MSD.TicketNo as Ticket_Number, 
  Details_Pn as Description, 
  MSD.Nun as Numbers,
  -- 新增的条件标识列
  CASE
    WHEN Details_Pn LIKE '%SALARY%' THEN 'Salary Match'
    WHEN Details_Pn LIKE '%Mid MTH%' THEN 'Mid MTH Match'
    WHEN Details_Pn LIKE '%Mid Month%' THEN 'Mid Month Match'
    WHEN Details_Pn LIKE '%Pay%' THEN 'Pay Match'
    WHEN Details_PNarration LIKE '%SAL%' THEN 'SAL Narration Match'
  END AS Triggered_Condition
FROM MS_Transactions MST 
LEFT JOIN MBS_Details as MSD ON MST.TicketNo = MSD.TicketNo 
WHERE 
  (Details_Pn LIKE '%SALARY%' 
   OR Details_Pn LIKE '%Mid MTH%' 
   OR Details_Pn LIKE '%Mid Month%' 
   OR Details_Pn LIKE '%Pay%' 
   OR Details_PNarration LIKE '%SAL%')
  AND DAY(Details_TransactionDate) BETWEEN 23 AND 25 
  AND WEEKDAY(Details_TransactionDate) BETWEEN 0 AND 4;

方案2：返回所有满足的条件（字符串拼接） #

如果需要知道一行触发了多个条件，可以用字符串拼接函数（不同数据库函数略有差异，以下是MySQL示例）：

SELECT 
  Details_TransactionDate as Transaction_Date, 
  Details_Credit as Credit, 
  MSD.TicketNo as Ticket_Number, 
  Details_Pn as Description, 
  MSD.Nun as Numbers,
  -- 自动拼接所有满足的条件，忽略NULL值
  CONCAT_WS(', ',
    CASE WHEN Details_Pn LIKE '%SALARY%' THEN 'Salary Match' END,
    CASE WHEN Details_Pn LIKE '%Mid MTH%' THEN 'Mid MTH Match' END,
    CASE WHEN Details_Pn LIKE '%Mid Month%' THEN 'Mid Month Match' END,
    CASE WHEN Details_Pn LIKE '%Pay%' THEN 'Pay Match' END,
    CASE WHEN Details_PNarration LIKE '%SAL%' THEN 'SAL Narration Match' END
  ) AS Triggered_Conditions
FROM MS_Transactions MST 
LEFT JOIN MBS_Details as MSD ON MST.TicketNo = MSD.TicketNo 
WHERE 
  (Details_Pn LIKE '%SALARY%' 
   OR Details_Pn LIKE '%Mid MTH%' 
   OR Details_Pn LIKE '%Mid Month%' 
   OR Details_Pn LIKE '%Pay%' 
   OR Details_PNarration LIKE '%SAL%')
  AND DAY(Details_TransactionDate) BETWEEN 23 AND 25 
  AND WEEKDAY(Details_TransactionDate) BETWEEN 0 AND 4;

注：SQL Server用STRING_AGG，PostgreSQL也用STRING_AGG，根据你的数据库调整即可。

方案3：应对30+条件的扩展性——用条件表JOIN #

如果未来要新增大量条件，写冗长的CASE会非常难维护。可以把条件和对应的标识存到一个单独的表中，通过JOIN来匹配：

-- 先创建条件表（示例）
CREATE TABLE Match_Conditions (
  Condition_ID INT AUTO_INCREMENT PRIMARY KEY,
  Column_Name VARCHAR(50), -- 要匹配的列名
  Match_Pattern VARCHAR(100), -- LIKE匹配模式
  Condition_Label VARCHAR(100) -- 条件标识文本
);

-- 插入初始条件
INSERT INTO Match_Conditions (Column_Name, Match_Pattern, Condition_Label)
VALUES
('Details_Pn', '%SALARY%', 'Salary Match'),
('Details_Pn', '%Mid MTH%', 'Mid MTH Match'),
('Details_Pn', '%Mid Month%', 'Mid Month Match'),
('Details_Pn', '%Pay%', 'Pay Match'),
('Details_PNarration', '%SAL%', 'SAL Narration Match');

然后用JOIN+聚合查询获取所有匹配的条件：

SELECT 
  MST.Details_TransactionDate as Transaction_Date, 
  MST.Details_Credit as Credit, 
  MSD.TicketNo as Ticket_Number, 
  MST.Details_Pn as Description, 
  MSD.Nun as Numbers,
  -- 聚合所有匹配的条件标签
  GROUP_CONCAT(mc.Condition_Label SEPARATOR ', ') AS Triggered_Conditions
FROM MS_Transactions MST 
LEFT JOIN MBS_Details as MSD ON MST.TicketNo = MSD.TicketNo 
-- 关联条件表，匹配符合的规则
LEFT JOIN Match_Conditions mc ON 
  (mc.Column_Name = 'Details_Pn' AND MST.Details_Pn LIKE mc.Match_Pattern)
  OR (mc.Column_Name = 'Details_PNarration' AND MST.Details_PNarration LIKE mc.Match_Pattern)
WHERE 
  mc.Condition_ID IS NOT NULL -- 确保至少匹配一个条件
  AND DAY(MST.Details_TransactionDate) BETWEEN 23 AND 25 
  AND WEEKDAY(MST.Details_TransactionDate) BETWEEN 0 AND 4
GROUP BY 
  MST.Details_TransactionDate, 
  MST.Details_Credit, 
  MSD.TicketNo, 
  MST.Details_Pn, 
  MSD.Nun;

这种方式的好处是，新增条件只需要往Match_Conditions表插入行，完全不用修改主查询的SQL，非常适合大量条件的场景。

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