如何查询同一Order下Col4列值不同的订单？

假设有如下OrderLine表：

规则说明：Col4的值只能是X或Y，同一Order下不能同时出现两种值，表中的Order 1违反了该规则。需要找出所有存在这种情况的Order。

用户的初步思路是多次关联表，再使用HAVING子句，SQL语句如下：

SELECT DISTINCT A.[Order], A.Line, A.Col3, A.Col4, A.Col4
FROM OrderLines A 
   INNER JOIN OrderLines B ON B.[Order]= A.[Order]
      AND B.Line = A.Line
GROUP BY A.[Order], A.Line, A.Col3, A.Col4, A.Col4, B.[Order], B.Line, B.Col4
HAVING(A.Line <> B.Line AND A.Col4 <> B.Col4)
ORDER BY A.[Order] ASC, A.Line ASC

更高效的解法 #

方法一：分组统计不同Col4值数量

通过分组统计每个Order下Col4的不同值数量，若数量大于1则说明违反规则：

SELECT [Order]
FROM OrderLine
GROUP BY [Order]
HAVING COUNT(DISTINCT Col4) > 1

该方法只需一次表扫描，逻辑简洁直接，性能最优。

方法二：EXISTS关联查询

通过子查询判断同一Order下是否存在Col4值不同的记录：

SELECT DISTINCT A.[Order]
FROM OrderLine A
WHERE EXISTS (
    SELECT 1
    FROM OrderLine B
    WHERE B.[Order] = A.[Order]
      AND B.Col4 <> A.Col4
)

该方法通过关联子查询定位违规Order，适合需要扩展筛选条件的场景。

原SQL的问题分析 #

• 关联条件B.Line = A.Line限制了仅关联同一Line的记录，无法检测同一Order下不同Line的Col4差异
• HAVING子句中A.Line <> B.Line永远不成立，与关联条件矛盾
• SELECT和GROUP BY中存在冗余字段，逻辑混乱

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