在存在条件的情况下旋转

J86 发表于 Dev

J86

我在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)
);

数据

在里面，我有以下数据：

| Id  | Name       |
| --- | ---------- |
| 1   | Computer A |

---

| Id  | Name       | Table1_Id |
| --- | ---------- | --------- |
| 1   | Test Run 1 | 1         |

---

| Id  | Type      | Name      | Result  | Table2_Id |
| --- | --------- | --------- | ------- | --------- |
| 1   | Processor | MMX       | Pass    | 1         |
| 2   | Processor | SSE       | Pass    | 1         |
| 3   | Processor | SSE 2     | Pass    | 1         |
| 4   | Display   | Red       | Pass    | 1         |
| 5   | Display   | Green     | Pass    | 1         |
| 6   | Keyboard  | General   | Pass    | 1         |
| 7   | Keyboard  | Lights    | Skipped | 1         |
| 8   | Network   | Ethernet  | Pass    | 1         |
| 9   | Network   | Wireless  | Skipped | 1         |
| 10  | Network   | Bluetooth | Fail    | 1         |

所需查询

我的需求发生了变化，现在，每个Type值都应该变成一列，并使用以下逻辑计算该值：

对于中的任何给定值Type：

如果全部通过，则结果为Pass
如果有任何失败，则结果是Fail
如果有任何被跳过（前提是检查了前两点），则结果为Skipped。

所以对于当前数据，输出将是：

| table1_name | processor_test | display_test | keyboard_test | Network |
| ----------- | ---------------|--------------|---------------|---------|
| Computer A  | Pass           | Pass         | Skipped       | Fail    |

当前查询

当列/值位于两级子表中时，我正在努力进行条件透视。我目前的查询是：

select t1.Name as 'table1_name'
-- pivoting columns
from Table1 t1
inner join Table2 t2 on t1.Id = t2.Table1_Id
inner join Table3 t3 on t2.Id = t3.Table2_Id;

我创建了一个db-fiddle来让事情变得更容易。

塞尔格

创建一个表格来确定结果的优先级

create table Priority (
  Id int not null ,
  Result varchar(255) not null
);

insert into Priority(id, result)
values 
(1,'Fail'),
(2,'Skipped'),
(3,'Pass')

select grp.table1_name, grp.Type, p.Result
from (    
    select t1.Name as table1_name,
    t3.Type, min(p.id) mp
    from Table1 t1
    inner join Table2 t2 on t1.Id = t2.Table1_Id
    inner join Table3 t3 on t2.Id = t3.Table2_Id
    inner join Priority p on p.Result = t3.Result
    group by t1.Name, t3.Type) grp
join Priority p on p.Id = grp.mp;

然后可以根据需要旋转结果。

编辑

您还可以使用 CASE 来编码/解码优先级

select table1_name, 
    MAX(
        CASE 
            WHEN Type='Processor' 
            THEN Result
            ELSE NULL 
        END
    ) AS 'processor_test',
    MAX(
        CASE 
            WHEN Type='Display' 
            THEN Result
            ELSE NULL 
        END
    ) AS 'display_test',
    MAX(
        CASE 
            WHEN Type='Network' 
            THEN Result
            ELSE NULL 
        END
    ) AS 'network_test',
    MAX(
        CASE 
            WHEN Type='Keyboard'
            THEN Result
            ELSE NULL 
        END
    ) AS 'keyboard_test' 
  from (    
    select t1.Name as table1_name,
        t3.Type, 
        case min(
            case t3.Result 
              when'Fail' then 1
              when'Skipped' then 2
              when'Pass' then 3
            end) 
          when 1 then 'Fail' 
          when 2 then 'Skipped'     
          when 3 then 'Pass'
        end Result
    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.Name, t3.Type
    ) t
 group by table1_name;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。