MySQL 8两表多规则关联查询实现方法求助

测试数据准备 #

先创建并初始化测试表（已修正原SQL中的语法错误）：

create table person (
    id integer primary key,
    name text not null
);

create table team (
    id integer primary key,
    person_id integer,
    org_id integer,
    champion integer
);

insert into person values (1, 'joe');
insert into person values (2, 'bill');
insert into person values (3, 'sally');
insert into person values (4, 'beth');
insert into person values (5, 'grace');

insert into team values (1, 1, 500, 0);
insert into team values (2, 2, 500, 0);
insert into team values (3, 2, 500, 0);
insert into team values (4, 3, 500, 1);
insert into team values (5, 1, 700, 1);
insert into team values (6, 1, 600, 0);
insert into team values (7, 2, 600, 0);
insert into team values (8, 4, 600, 1);
insert into team values (9, 3, 700, 0);
insert into team values (10, 4, 700, 1);

查询需求 #

筛选符合以下任一规则的人员：

• 未加入任何团队的人员；
• 加入的所有团队均非冠军团队（champion = 0）的人员；
• 加入的团队中存在属于700组织且为冠军团队（org_id = 700 AND champion = 1）的人员。

实现查询语句 #

SELECT p.id AS ID, p.name AS NAME
FROM person p
LEFT JOIN team t ON p.id = t.person_id
GROUP BY p.id, p.name
HAVING 
    -- 规则1：未加入任何团队
    COUNT(t.id) = 0
    OR
    -- 规则2：所有团队都不是冠军团队
    MAX(t.champion) = 0
    OR
    -- 规则3：存在700组织的冠军团队
    SUM(CASE WHEN t.org_id = 700 AND t.champion = 1 THEN 1 ELSE 0 END) > 0
ORDER BY p.id;

逻辑解释 #

1. 规则1：通过LEFT JOIN后统计关联的团队数量为0，判定该人员未加入任何团队；
2. 规则2：统计该人员所有团队的champion最大值为0，说明所有团队都不是冠军团队；
3. 规则3：通过条件计数，若存在符合org_id=700且champion=1的团队，计数结果大于0。

查询结果 #

执行上述语句后，将得到符合预期的结果：

ID NAME
5  grace
2  bill
4  beth
1  joe

筛选说明 #

• grace：未加入任何团队，符合规则1；
• bill：所有加入的团队champion均为0，符合规则2；
• beth：存在org_id=700且champion=1的团队，符合规则3；
• joe：存在org_id=700且champion=1的团队，符合规则3；
• sally：虽然有冠军团队，但不属于700组织，且存在冠军团队不满足规则2，因此未被筛选。

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