MySQL 5.7三级关联表按Type分组条件合并测试结果怎么实现

表结构 #

create table Table1 (
    Id int not null auto_increment,
    Name varchar(255) not null,
    primary key(Id)
);

create table Table2 (
    Id int not null auto_increment,
    Name varchar(255) not null,
    Table1_Id int not null,
    primary key(Id),
    foreign key(Table1_Id) references Table1(Id)  
);


create table Table3 (
    Id int not null auto_increment,
    Type varchar(255) not null,
    Name varchar(255) not null,
    Result varchar(255) not null,
    Table2_Id int not null,
    primary key(Id),
    foreign key(Table2_Id) references Table2(Id)
);

现有测试数据 #

Table1 数据 #

Table2 数据 #

Table3 数据 #

查询需求 #

返回table1_name和test_result两列，其中test_result为拼接字符串，对Type字段每个分组的判定规则如下：

• 若分组内所有测试项的Result均为Pass，则该Type对应的结果为Pass
• 若分组内存在任意Result为Fail，则该Type对应的结果为Fail
• 若不满足以上两个条件，且分组内存在任意Result为Skipped，则该Type对应的结果为Skipped

期望输出 #

实现代码 #

现有基础关联代码 #

select t1.Name as 'table1_name'
-- coalesce to happen here
from Table1 t1
inner join Table2 t2 on t1.Id = t2.Table1_Id
inner join Table3 t3 on t2.Id = t3.Table2_Id;

完整可用查询代码 #

SELECT 
    t1.Name AS table1_name,
    GROUP_CONCAT(CONCAT(t3_group.Type, ': ', t3_group.type_result) SEPARATOR ', ') AS test_result
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Id = t2.Table1_Id
INNER JOIN (
    -- 先按测试类型分组计算每个类型的最终结果
    SELECT 
        Table2_Id,
        Type,
        CASE
            WHEN SUM(CASE WHEN Result = 'Fail' THEN 1 ELSE 0 END) > 0 THEN 'Fail'
            WHEN SUM(CASE WHEN Result = 'Skipped' THEN 1 ELSE 0 END) > 0 THEN 'Skipped'
            ELSE 'Pass'
        END AS type_result
    FROM Table3
    GROUP BY Table2_Id, Type
) t3_group ON t2.Id = t3_group.Table2_Id
GROUP BY t1.Id, t1.Name;

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