多OR条件SQL对比两表时，如何定位具体不匹配的列？

可以通过CASE条件判断+字符串聚合函数实现，直接返回每个员工对应的不匹配月份列表，无需手动对比列值。以下是不同数据库的具体实现：

MySQL 版本 #

SELECT
  o.EmpId,
  GROUP_CONCAT(
    CASE 
      WHEN (o.Jan <> n.Jan OR o.Jan IS NULL <> n.Jan IS NULL) THEN 'Jan'
      WHEN (o.Feb <> n.Feb OR o.Feb IS NULL <> n.Feb IS NULL) THEN 'Feb'
      WHEN (o.Mar <> n.Mar OR o.Mar IS NULL <> n.Mar IS NULL) THEN 'Mar'
      WHEN (o.Apr <> n.Apr OR o.Apr IS NULL <> n.Apr IS NULL) THEN 'Apr'
      WHEN (o.May <> n.May OR o.May IS NULL <> n.May IS NULL) THEN 'May'
      WHEN (o.Jun <> n.Jun OR o.Jun IS NULL <> n.Jun IS NULL) THEN 'Jun'
      WHEN (o.Jul <> n.Jul OR o.Jul IS NULL <> n.Jul IS NULL) THEN 'Jul'
      WHEN (o.Aug <> n.Aug OR o.Aug IS NULL <> n.Aug IS NULL) THEN 'Aug'
      WHEN (o.Sep <> n.Sep OR o.Sep IS NULL <> n.Sep IS NULL) THEN 'Sep'
      WHEN (o.Oct <> n.Oct OR o.Oct IS NULL <> n.Oct IS NULL) THEN 'Oct'
      WHEN (o.Nov <> n.Nov OR o.Nov IS NULL <> n.Nov IS NULL) THEN 'Nov'
      WHEN (o.Dec <> n.Dec OR o.Dec IS NULL <> n.Dec IS NULL) THEN 'Dec'
    END
    SEPARATOR ', '
  ) AS Mismatched_Months
FROM OldSales o
INNER JOIN NewSales n ON o.EmpId = n.EmpId
WHERE
  (o.Jan <> n.Jan OR o.Jan IS NULL <> n.Jan IS NULL) OR
  (o.Feb <> n.Feb OR o.Feb IS NULL <> n.Feb IS NULL) OR
  (o.Mar <> n.Mar OR o.Mar IS NULL <> n.Mar IS NULL) OR
  (o.Apr <> n.Apr OR o.Apr IS NULL <> n.Apr IS NULL) OR
  (o.May <> n.May OR o.May IS NULL <> n.May IS NULL) OR
  (o.Jun <> n.Jun OR o.Jun IS NULL <> n.Jun IS NULL) OR
  (o.Jul <> n.Jul OR o.Jul IS NULL <> n.Jul IS NULL) OR
  (o.Aug <> n.Aug OR o.Aug IS NULL <> n.Aug IS NULL) OR
  (o.Sep <> n.Sep OR o.Sep IS NULL <> n.Sep IS NULL) OR
  (o.Oct <> n.Oct OR o.Oct IS NULL <> n.Oct IS NULL) OR
  (o.Nov <> n.Nov OR o.Nov IS NULL <> n.Nov IS NULL) OR
  (o.Dec <> n.Dec OR o.Dec IS NULL <> n.Dec IS NULL)
GROUP BY o.EmpId;

SQL Server 版本 #

SELECT
  o.EmpId,
  STRING_AGG(
    CASE 
      WHEN (o.Jan <> n.Jan OR o.Jan IS NULL <> n.Jan IS NULL) THEN 'Jan'
      WHEN (o.Feb <> n.Feb OR o.Feb IS NULL <> n.Feb IS NULL) THEN 'Feb'
      WHEN (o.Mar <> n.Mar OR o.Mar IS NULL <> n.Mar IS NULL) THEN 'Mar'
      WHEN (o.Apr <> n.Apr OR o.Apr IS NULL <> n.Apr IS NULL) THEN 'Apr'
      WHEN (o.May <> n.May OR o.May IS NULL <> n.May IS NULL) THEN 'May'
      WHEN (o.Jun <> n.Jun OR o.Jun IS NULL <> n.Jun IS NULL) THEN 'Jun'
      WHEN (o.Jul <> n.Jul OR o.Jul IS NULL <> n.Jul IS NULL) THEN 'Jul'
      WHEN (o.Aug <> n.Aug OR o.Aug IS NULL <> n.Aug IS NULL) THEN 'Aug'
      WHEN (o.Sep <> n.Sep OR o.Sep IS NULL <> n.Sep IS NULL) THEN 'Sep'
      WHEN (o.Oct <> n.Oct OR o.Oct IS NULL <> n.Oct IS NULL) THEN 'Oct'
      WHEN (o.Nov <> n.Nov OR o.Nov IS NULL <> n.Nov IS NULL) THEN 'Nov'
      WHEN (o.Dec <> n.Dec OR o.Dec IS NULL <> n.Dec IS NULL) THEN 'Dec'
    END, ', '
  ) AS Mismatched_Months
FROM OldSales o
INNER JOIN NewSales n ON o.EmpId = n.EmpId
WHERE
  (o.Jan <> n.Jan OR o.Jan IS NULL <> n.Jan IS NULL) OR
  (o.Feb <> n.Feb OR o.Feb IS NULL <> n.Feb IS NULL) OR
  (o.Mar <> n.Mar OR o.Mar IS NULL <> n.Mar IS NULL) OR
  (o.Apr <> n.Apr OR o.Apr IS NULL <> n.Apr IS NULL) OR
  (o.May <> n.May OR o.May IS NULL <> n.May IS NULL) OR
  (o.Jun <> n.Jun OR o.Jun IS NULL <> n.Jun IS NULL) OR
  (o.Jul <> n.Jul OR o.Jul IS NULL <> n.Jul IS NULL) OR
  (o.Aug <> n.Aug OR o.Aug IS NULL <> n.Aug IS NULL) OR
  (o.Sep <> n.Sep OR o.Sep IS NULL <> n.Sep IS NULL) OR
  (o.Oct <> n.Oct OR o.Oct IS NULL <> n.Oct IS NULL) OR
  (o.Nov <> n.Nov OR o.Nov IS NULL <> n.Nov IS NULL) OR
  (o.Dec <> n.Dec OR o.Dec IS NULL <> n.Dec IS NULL)
GROUP BY o.EmpId;

Oracle 版本 #

SELECT
  o.EmpId,
  LISTAGG(
    CASE 
      WHEN (o.Jan <> n.Jan OR o.Jan IS NULL <> n.Jan IS NULL) THEN 'Jan'
      WHEN (o.Feb <> n.Feb OR o.Feb IS NULL <> n.Feb IS NULL) THEN 'Feb'
      WHEN (o.Mar <> n.Mar OR o.Mar IS NULL <> n.Mar IS NULL) THEN 'Mar'
      WHEN (o.Apr <> n.Apr OR o.Apr IS NULL <> n.Apr IS NULL) THEN 'Apr'
      WHEN (o.May <> n.May OR o.May IS NULL <> n.May IS NULL) THEN 'May'
      WHEN (o.Jun <> n.Jun OR o.Jun IS NULL <> n.Jun IS NULL) THEN 'Jun'
      WHEN (o.Jul <> n.Jul OR o.Jul IS NULL <> n.Jul IS NULL) THEN 'Jul'
      WHEN (o.Aug <> n.Aug OR o.Aug IS NULL <> n.Aug IS NULL) THEN 'Aug'
      WHEN (o.Sep <> n.Sep OR o.Sep IS NULL <> n.Sep IS NULL) THEN 'Sep'
      WHEN (o.Oct <> n.Oct OR o.Oct IS NULL <> n.Oct IS NULL) THEN 'Oct'
      WHEN (o.Nov <> n.Nov OR o.Nov IS NULL <> n.Nov IS NULL) THEN 'Nov'
      WHEN (o.Dec <> n.Dec OR o.Dec IS NULL <> n.Dec IS NULL) THEN 'Dec'
    END, ', '
  ) WITHIN GROUP (ORDER BY NULL) AS Mismatched_Months
FROM OldSales o
INNER JOIN NewSales n ON o.EmpId = n.EmpId
WHERE
  (o.Jan <> n.Jan OR o.Jan IS NULL <> n.Jan IS NULL) OR
  (o.Feb <> n.Feb OR o.Feb IS NULL <> n.Feb IS NULL) OR
  (o.Mar <> n.Mar OR o.Mar IS NULL <> n.Mar IS NULL) OR
  (o.Apr <> n.Apr OR o.Apr IS NULL <> n.Apr IS NULL) OR
  (o.May <> n.May OR o.May IS NULL <> n.May IS NULL) OR
  (o.Jun <> n.Jun OR o.Jun IS NULL <> n.Jun IS NULL) OR
  (o.Jul <> n.Jul OR o.Jul IS NULL <> n.Jul IS NULL) OR
  (o.Aug <> n.Aug OR o.Aug IS NULL <> n.Aug IS NULL) OR
  (o.Sep <> n.Sep OR o.Sep IS NULL <> n.Sep IS NULL) OR
  (o.Oct <> n.Oct OR o.Oct IS NULL <> n.Oct IS NULL) OR
  (o.Nov <> n.Nov OR o.Nov IS NULL <> n.Nov IS NULL) OR
  (o.Dec <> n.Dec OR o.Dec IS NULL <> n.Dec IS NULL)
GROUP BY o.EmpId;

说明 #

1. 加入了NULL值判断：因为SQL中<>运算符无法识别NULL与非NULL的差异，所以通过o.Jan IS NULL <> n.Jan IS NULL补充这种场景。
2. 结果展示：查询后会直接返回类似EmpId=1, Mismatched_Months='Feb, Apr'、EmpId=2, Mismatched_Months='Dec'的结果，清晰标记不匹配的月份。

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