mysql数据透视表的麻烦 [英] trouble with mysql pivot table

问题描述

我无法使用以下表在MySQL中创建数据透视表:

I'm having trouble creating a pivot table in MySQL using the following tables:

Teams
-------------
id | name
1  | teamA

Processes
-------------
id | name
1  | processA
2  | processB

ProcessDetails
---------------
id | processId | keyName
 1 |     1     |  shape
 2 |     1     |  vegetable
 3 |     1     |  fruit
 4 |     2     |  animal

TeamProcesses
-----------------
id | teamId | processId
 5 |   1    |    1
 6 |   1    |    2

TeamProcessDetails
--------------------
id | teamProcessId | proccessDetailsId | value
 1 |       5       |         1         | circle
 2 |       5       |         2         | carrot
 3 |       5       |         3         | apple
 4 |       6       |         4         | dog

我要生成的数据透视表是这个:

The pivot table I'm trying to produce is this one:

Pivot Table
------------
teamId | processId |  shape  | vegetable |  fruit  | animal
   1   |     1     |  circle |  carrot   |  apple  |  NULL
   1   |     2     |  NULL   |   NULL    |   NULL  |  dog

应该注意，键的数量是动态的，因此我认为我需要使用准备好的语句方法.另外，这些进程没有相同的键，因此它们应该只具有属于该进程的键的值.

It should be noted that the number of keys is dynamic so I think I need to use the prepared statement method. Also, the processes don't have the same keys so they should only have a value for a key that belongs to that process.

谢谢！

推荐答案

当您尝试透视动态值或未知值时，我总是建议您先从静态或硬编码版本的查询开始，然后再进行转换到动态SQL.

When you are trying to pivot dynamic or unknown value, I would always suggest that you start with a static or hard-coded version of the query first, then convert it to dynamic SQL.

MySQL没有PIVOT函数，因此您将需要使用带有CASE表达式的聚合函数来获取结果.该代码的静态版本将类似于以下内容:

MySQL doesn't have a PIVOT function so you will need to use an aggregate function with a CASE expression to get the result. The static version of the code will be similar to the following:

select t.id teamid, 
  t.name teamname, 
  p.id processid, 
  p.name processname,
  max(case when pd.keyname = 'shape' then tpd.value end) shape,
  max(case when pd.keyname = 'vegetable' then tpd.value end) vegetable,
  max(case when pd.keyname = 'fruit' then tpd.value end) fruit,
  max(case when pd.keyname = 'animal' then tpd.value end) animal
from teams t
inner join teamprocesses tp
  on t.id = tp.teamid
inner join TeamProcessDetails tpd
  on tp.id = tpd.teamProcessId
inner join processes p
  on tp.processid = p.id
inner join processdetails pd
  on p.id = pd.processid
  and tpd.processDetailsid = pd.id
group by t.id, t.name, p.id, p.name;

请参见带有演示的SQL提琴.

现在，如果您要转换成未知数的keynames想要转换为列，那么您将需要使用

Now if you are going to have an unknown number of keynames that you want to convert into columns, then you will need to use a prepared statement to generate dynamic SQL. The code will be similar to:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when pd.keyname = ''',
      keyname,
      ''' then tpd.value end) AS ',
      replace(keyname, ' ', '')
    )
  ) INTO @sql
from ProcessDetails;

SET @sql 
    = CONCAT('SELECT t.id teamid, 
                t.name teamname, 
                p.id processid, 
                p.name processname, ', @sql, ' 
              from teams t
              inner join teamprocesses tp
                on t.id = tp.teamid
              inner join TeamProcessDetails tpd
                on tp.id = tpd.teamProcessId
              inner join processes p
                on tp.processid = p.id
              inner join processdetails pd
                on p.id = pd.processid
                and tpd.processDetailsid = pd.id
              group by t.id, t.name, p.id, p.name;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

请参见带演示的SQL提琴.

请记住 GROUP_CONCAT 函数来创建列的字符串的默认最大长度为1024，因此，如果该字符串中包含很多字符，则可能必须更改group_concat_max_len的会话值

此查询将给出结果:

| TEAMID | TEAMNAME | PROCESSID | PROCESSNAME |  SHAPE | VEGETABLE |  FRUIT | ANIMAL |
|      1 |    teamA |         1 |    processA | circle |    carrot |  apple | (null) |
|      1 |    teamA |         2 |    processB | (null) |    (null) | (null) |    dog |

这篇关于mysql数据透视表的麻烦的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！