使用MySQL的数据透视表

小编典典

使用MySQL的数据透视表

mysql

我有两个表三元组和标签

三元组表具有以下列

  id  PostID  TagID   Value
   1   1        1     Murder
   2   1        2     New Brunswick
   3   2        1     Theft
   4   2        3     Gun

标签表具有以下列

  id   TagName
   1   Incident
   2   Location
   3   Weapon

我正在尝试编写sql创建带有动态标题的数据透视表

输出应该是这样的

       PostID  Incident   Location         Weapon        
           1   Murder     New Brunswick    
           2   Theft                        Gun

编写SQL的任何帮助将不胜感激。我在网上看到了示例，但无法弄清楚这一示例

阅读 1374

2020-05-17

共1个答案

小编典典

为了转动在MySQL中的数据，您需要同时使用聚合函数和CASE表达。

如果您知道已知的列数，则可以对查询进行硬编码：

select p.postid,
  max(case when t.tagname = 'Incident' then p.value end) Incident,
  max(case when t.tagname = 'Location' then p.value end) Location,
  max(case when t.tagname = 'Weapon' then p.value end) Weapon
from triples p
left join tags t
  on p.tagid = t.id
group by p.postid;

参见带有演示的SQL Fiddle

但是，如果您的列数未知，则需要使用一条准备好的语句来生成动态SQL：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN TagName = ''',
      TagName,
      ''' THEN p.value END) AS `',
      TagName, '`'
    )
  ) INTO @sql
FROM tags;


SET @sql 
  = CONCAT('SELECT p.postid, ', @sql, ' 
           from triples p
           left join tags t
             on p.tagid = t.id
           group by p.postid');

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

请参阅带有演示的SQL Fiddle。

两者都会给出结果：

| POSTID | INCIDENT |      LOCATION | WEAPON |
----------------------------------------------
|      1 |   Murder | New Brunswick | (null) |
|      2 |    Theft |        (null) |    Gun |

2020-05-17