查询满足唯一出现且关联status含YES的partial_match表ID

数据表结构与数据 #

partial_match（别名p）

id | name
----------
1  | a
1  | b
2  | c
3  | d
4  | e

status（别名s）

name | code
------------
a    | YES
b    | NO
c    | YES
c    | MAYBE
d    | NO
d    | MAYBE
e    | YES

查询需求 #

筛选出满足以下两个条件的id：

• 该id在partial_match表中仅出现一次
• 对应name在status表中存在code为'YES'的记录

解决方案SQL语句 #

推荐两种实现方式：

方式一：关联后分组筛选

SELECT p.id
FROM partial_match p
JOIN status s ON p.name = s.name
WHERE s.code = 'YES'
GROUP BY p.id
HAVING COUNT(p.id) = 1;

方式二：子查询+存在性验证

SELECT p.id
FROM partial_match p
WHERE p.id IN (
    SELECT id
    FROM partial_match
    GROUP BY id
    HAVING COUNT(*) = 1
)
AND EXISTS (
    SELECT 1
    FROM status s
    WHERE s.name = p.name
    AND s.code = 'YES'
);

逻辑说明 #

1. 筛选仅出现一次的id：通过GROUP BY id结合HAVING COUNT(*) = 1，定位partial_match中只出现过一次的id
2. 验证对应name的code为YES：要么通过JOIN关联两张表直接筛选符合条件的记录，要么用EXISTS子查询验证该name存在code='YES'的状态记录

查询结果 #

执行上述语句后，返回结果为：

id
---
2
4

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