MySQL动态转置复杂SQL查询表

Question

我有一个包含两列的表，以下是其结构：

create table scan( `application_name` varchar(255) NOT NULL, `defect_type` varchar(255) NOT NULL);

数据会相应地填充。该表存储了“应用程序”及其对应的“缺陷类型”的数据。我想在此表上执行以下两个操作：

1. 按百分比获取特定应用程序中前3个“缺陷类型”。
2. 转置上述输出，其中“缺陷类型”（defect_type）中的值变为列，其相应的百分比（finalPercent）作为其值。

我已经实现了第一个操作，下面是SQL Fiddle：SQLFiddle 但是，我无法根据要求转置输出。在执行1和2之后，理想情况下应该有一行结果。

application_name | CrossSide |  CSS  |  XML 
       A         |   33.33   | 33.33 | 16.67 

谢谢！

- awattal

Answer 1

你可以使用 group_concat 构建一个动态的透视查询，然后执行它：

set @sql = null;

set @total = (select count(*) as totalcount from scan where application_name = "a");

select group_concat(distinct
    concat(
      'round(((count(case when defect_type = ''',
      defect_type,
      ''' then application_name end)/ @total ) * 100 ), 2) as `',
      defect_type, '`'
    )
  ) into @sql 
from ( select  defect_type 
        from   scan 
        where  application_name = "a" 
        group  by defect_type
        order by count(defect_type) desc
        limit 3) t;

set @sql = concat(
'select application_name, ', @sql,
' from scan
where application_name = "a" 
group by application_name'
);

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

SQLFiddle

- potashin

---

谢谢。有没有不硬编码“CrossSide”，“CSS”，“XML”的方法，并动态填充列值？这些值可以是任何类型的缺陷，我只想要前三个缺陷类型。 - awattal

太棒了。这对我的场景非常完美，我也理解其中的复杂性。 - awattal