如何编写SQL查询Column B同时含Y和N值的Column A相关记录

问题描述 #

现有TableA表结构及数据如下：

其中1234和3456对应的Column B同时存在Y和N值，2345仅存在N值。需要获取Column B同时包含Y和N的Column A对应的所有行，理想输出如下：

尝试执行以下SQL语句未得到预期结果：

Select * from TableA
where column b = 'Y'
and column b = 'N'

原因分析 #

你写的SQL逻辑本身矛盾——同一行的Column B不可能同时等于Y和N，自然查不到任何数据。

正确SQL写法 #

这里提供几种实用的实现方式：

方法1：子查询+IN筛选 #

先找出同时存在Y和N的Column A值，再关联原表获取对应行：

SELECT *
FROM TableA
WHERE ColumnA IN (
    SELECT ColumnA
    FROM TableA
    WHERE ColumnB IN ('Y', 'N')
    GROUP BY ColumnA
    HAVING COUNT(DISTINCT ColumnB) = 2
)

方法2：自连接匹配 #

通过自连接让同一Column A下的Y和N记录互相匹配，再去重得到结果：

SELECT DISTINCT t1.*
FROM TableA t1
JOIN TableA t2 
    ON t1.ColumnA = t2.ColumnA
    AND t1.ColumnB != t2.ColumnB
WHERE t1.ColumnB IN ('Y', 'N')
  AND t2.ColumnB IN ('Y', 'N')

方法3：窗口函数（适用于MySQL 8+、PostgreSQL、SQL Server等） #

用窗口函数统计每个Column A下Column B的取值种类数，再筛选符合条件的行：

SELECT ColumnA, ColumnB
FROM (
    SELECT 
        ColumnA, 
        ColumnB,
        COUNT(DISTINCT ColumnB) OVER (PARTITION BY ColumnA) AS cnt
    FROM TableA
    WHERE ColumnB IN ('Y', 'N')
) AS sub
WHERE cnt = 2

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