SQL多表关联统计各州对应水果总数量问题求助

现有表结构及测试数据 #

-- PAR表：人员信息表
CREATE TABLE PAR (
    ID          varchar(10) not null,
    Name                varchar(20) not null,
    State               varchar(20) not null,
    primary key (ID)
);

INSERT INTO PAR (ID, Name, State)
VALUES
('A000','Susan','New York'),
('B123','Bob','Texas'),
('C456','Tony','Washington'),
('D789','Adam','California');

-- GROUP表：群组信息表，存储每个群组的成员ID（GROUP为SQL关键字，使用反引号转义）
CREATE TABLE `GROUP` (
    GroupID         varchar(25) not null,
    Person1         varchar(15) not null,
    Person2         varchar(15) not null,
    Person3         varchar(15),
    Person4         varchar(15),
    primary key (GroupID)
);

INSERT INTO `GROUP` (GroupID, Person1, Person2, Person3, Person4)
VALUES
('G234','A000','B666','C777'),
('G456','B123','D898','E632'),
('G789','C456', null, null),
('G444','D789','O123', null);

-- SOLO表：个人水果关联表
CREATE TABLE SOLO (
    Classification          varchar(15) not null,
    ID      varchar(10) not null,
    Fruits          varchar(10) not null,
    primary key (Classification, ID),
    foreign key (ID) references PAR(ID)
);

INSERT INTO SOLO(Classification, ID, Fruits)
VALUES
('A Group','A000','Apple'),
('A Group','B123','Banana'),
('B Group','C456','Orange');

-- GROUPINGS表：群组水果关联表
CREATE TABLE GROUPINGS (
    Classification      varchar(20) not null,
    `Group`           varchar(25) not null,
    Fruits          varchar(15) not null,
    primary key (Classification, `Group`),
    foreign key (`Group`) references `GROUP`(GroupID)
);

INSERT INTO GROUPINGS(Classification, `Group`, Fruits)
VALUES
('A Group','G234','Apple'),
('B Group','G456','Grape'),
('A Group','G789','Banana'),
('D Group','G444','Orange');

预期查询结果 #

State     |  Fruits
----------+-------
New York  | 2
Texas     | 2
Washington| 2
California| 1

问题说明 #

原始查询逻辑有误，需要实现的统计规则为：统计每个州对应的水果总数，总数由两部分组成：

• 该州所有人员在SOLO表中关联的水果记录数
• 该州所有人员作为群组成员时，所属群组在GROUPINGS表中关联的水果记录数
  核心难点是如何将PAR表的人员ID与GROUP表中Person1/Person2/Person3/Person4四个成员列做关联。

正确实现SQL #

SELECT 
    p.State,
    IFNULL(solo_cnt, 0) + IFNULL(group_cnt, 0) AS Fruits
FROM PAR p
-- 统计每个人对应的SOLO水果数量
LEFT JOIN (
    SELECT ID, COUNT(*) AS solo_cnt
    FROM SOLO
    GROUP BY ID
) s ON p.ID = s.ID
-- 统计每个人所属群组对应的水果数量
LEFT JOIN (
    SELECT person_id, COUNT(*) AS group_cnt
    FROM (
        -- 将4个成员列拆分为单列行数据，解决多列关联问题
        SELECT Person1 AS person_id, GroupID FROM `GROUP`
        UNION ALL
        SELECT Person2 AS person_id, GroupID FROM `GROUP`
        UNION ALL
        SELECT Person3 AS person_id, GroupID FROM `GROUP`
        UNION ALL
        SELECT Person4 AS person_id, GroupID FROM `GROUP`
    ) t
    WHERE t.person_id IS NOT NULL
    JOIN GROUPINGS g ON t.GroupID = g.`Group`
    GROUP BY t.person_id
) g ON p.ID = g.person_id
GROUP BY p.State
ORDER BY p.State;

逻辑说明 #

1. 先通过子查询按人员ID聚合，统计每个人在SOLO表中的水果记录数
2. 通过UNION ALL把GROUP表的4个成员列拆为<人员ID, 群组ID>的行结构，解决多成员列无法直接关联的问题
3. 将拆分后的群组人员数据和GROUPINGS表关联，统计每个人所属群组对应的水果记录数
4. 把两部分统计结果左连接到PAR表，按州聚合求和即可得到预期结果

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