mysql数据透视表的麻烦

mysql数据透视表的麻烦

当您尝试使用动态或未知值时，我总是建议您先使用静态或硬编码版本的查询，然后再将其转换为动态SQL。

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

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) animalfrom teams tinner join teamprocesses tp  on t.id = tp.teamidinner join TeamProcessDetails tpd  on tp.id = tpd.teamProcessIdinner join processes p  on tp.processid = p.idinner join processdetails pd  on p.id = pd.processid  and tpd.processDetailsid = pd.idgroup by t.id, t.name, p.id, p.name;

请参阅带有演示的SQL Fiddle。

现在，如果

keynames

要转换为列的数量未知，则需要使用准备好的语句来生成动态SQL。该代码将类似于：

SET @sql = NULL;SELECt  GROUP_CONCAt(DISTINCT    CONCAt(      'max(case when pd.keyname = ''',      keyname,      ''' then tpd.value end) AS ',      replace(keyname, ' ', '')    )  ) INTO @sqlfrom 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 Fiddle。

请记住

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 |