MySQL 5.7环境下跨三级关联表的条件行转列查询实现问题

背景 #

当前使用 MySQL 5.7.35 版本，涉及三张业务表，结构与测试数据如下：

表结构 #

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 数据 #

需求说明 #

需要将Table3中Type字段的取值转为独立查询列，列值计算优先级如下：

• 若该Type下所有测试项Result均为Pass，返回Pass
• 若该Type下存在任意Result为Fail，优先返回Fail
• 若该Type下无Fail但存在Skipped，返回Skipped

期望输出结果：

实现SQL #

MySQL 5.7无原生行转列语法，可通过条件聚合实现逻辑：

SELECT 
    t1.Name AS table1_name,
    CASE
        WHEN SUM(CASE WHEN t3.Type = 'Processor' AND t3.Result = 'Fail' THEN 1 ELSE 0 END) > 0 THEN 'Fail'
        WHEN SUM(CASE WHEN t3.Type = 'Processor' AND t3.Result = 'Skipped' THEN 1 ELSE 0 END) > 0 THEN 'Skipped'
        ELSE 'Pass'
    END AS processor_test,
    CASE
        WHEN SUM(CASE WHEN t3.Type = 'Display' AND t3.Result = 'Fail' THEN 1 ELSE 0 END) > 0 THEN 'Fail'
        WHEN SUM(CASE WHEN t3.Type = 'Display' AND t3.Result = 'Skipped' THEN 1 ELSE 0 END) > 0 THEN 'Skipped'
        ELSE 'Pass'
    END AS display_test,
    CASE
        WHEN SUM(CASE WHEN t3.Type = 'Keyboard' AND t3.Result = 'Fail' THEN 1 ELSE 0 END) > 0 THEN 'Fail'
        WHEN SUM(CASE WHEN t3.Type = 'Keyboard' AND t3.Result = 'Skipped' THEN 1 ELSE 0 END) > 0 THEN 'Skipped'
        ELSE 'Pass'
    END AS keyboard_test,
    CASE
        WHEN SUM(CASE WHEN t3.Type = 'Network' AND t3.Result = 'Fail' THEN 1 ELSE 0 END) > 0 THEN 'Fail'
        WHEN SUM(CASE WHEN t3.Type = 'Network' AND t3.Result = 'Skipped' THEN 1 ELSE 0 END) > 0 THEN 'Skipped'
        ELSE 'Pass'
    END AS Network
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Id = t2.Table1_Id
INNER JOIN Table3 t3 ON t2.Id = t3.Table2_Id
GROUP BY t1.Id, t1.Name;

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