MySQL - 行到列

10 回答

• 1

  我的解决方案

  select h.hostid, sum(ifnull(h.A,0)) as A, sum(ifnull(h.B,0)) as B, sum(ifnull(h.C,0)) as  C from (
  select
  hostid,
  case when itemName = 'A' then itemvalue end as A,
  case when itemName = 'B' then itemvalue end as B,
  case when itemName = 'C' then itemvalue end as C
    from history 
  ) h group by hostid
  

  它在提交的案例中产生预期结果 .

  回复于 2024-05-07T06:28:18+08:00
• 11

  利用Matt Fenwick帮助我解决问题的想法（非常感谢），让我们将其简化为一个查询：

  select
      history.*,
      coalesce(sum(case when itemname = "A" then itemvalue end), 0) as A,
      coalesce(sum(case when itemname = "B" then itemvalue end), 0) as B,
      coalesce(sum(case when itemname = "C" then itemvalue end), 0) as C
  from history
  group by hostid
  

  回复于 2024-05-07T06:28:18+08:00
• 21

  另一个选项，如果你有很多需要转动的项目，特别有用的是让mysql为你构建查询：

  SELECT
    GROUP_CONCAT(DISTINCT
      CONCAT(
        'ifnull(SUM(case when itemname = ''',
        itemname,
        ''' then itemvalue end),0) AS `',
        itemname, '`'
      )
    ) INTO @sql
  FROM
    history;
  SET @sql = CONCAT('SELECT hostid, ', @sql, ' 
                    FROM history 
                     GROUP BY hostid');
  
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  

  FIDDLE添加了一些额外的值以使其正常工作

  GROUP_CONCAT 的默认值为1000，因此如果您有一个非常大的查询，请在运行之前更改此参数

  SET SESSION group_concat_max_len = 1000000;
  

  测试：

  DROP TABLE IF EXISTS history;
  CREATE TABLE history
  (hostid INT,
  itemname VARCHAR(5),
  itemvalue INT);
  
  INSERT INTO history VALUES(1,'A',10),(1,'B',3),(2,'A',9),
  (2,'C',40),(2,'D',5),
  (3,'A',14),(3,'B',67),(3,'D',8);
  
    hostid    A     B     C      D
      1     10      3     0      0
      2     9       0    40      5
      3     14     67     0      8
  

  回复于 2024-05-07T06:28:18+08:00
• 20

  我把它变成了 Group By hostId 然后它只会显示第一行的值，
  喜欢：

  A   B  C
  1  10
  2      3
  

  回复于 2024-05-07T06:28:18+08:00
• 1

  这不是您正在寻找的确切答案，但它是我在项目中需要的解决方案，并希望这有助于某人 . 这将列出以逗号分隔的1到n行项目 . Group_Concat使这在MySQL中成为可能 .

  select
  cemetery.cemetery_id as "Cemetery_ID",
  GROUP_CONCAT(distinct(names.name)) as "Cemetery_Name",
  cemetery.latitude as Latitude,
  cemetery.longitude as Longitude,
  c.Contact_Info,
  d.Direction_Type,
  d.Directions
  
      from cemetery
      left join cemetery_names on cemetery.cemetery_id = cemetery_names.cemetery_id 
      left join names on cemetery_names.name_id = names.name_id 
      left join cemetery_contact on cemetery.cemetery_id = cemetery_contact.cemetery_id 
  
      left join 
      (
          select 
              cemetery_contact.cemetery_id as cID,
              group_concat(contacts.name, char(32), phone.number) as Contact_Info
  
                  from cemetery_contact
                  left join contacts on cemetery_contact.contact_id = contacts.contact_id 
                  left join phone on cemetery_contact.contact_id = phone.contact_id 
  
              group by cID
      )
      as c on c.cID = cemetery.cemetery_id
  
  
      left join
      (
          select 
              cemetery_id as dID, 
              group_concat(direction_type.direction_type) as Direction_Type,
              group_concat(directions.value , char(13), char(9)) as Directions
  
                  from directions
                  left join direction_type on directions.type = direction_type.direction_type_id
  
              group by dID
  
  
      )
      as d on d.dID  = cemetery.cemetery_id
  
  group by Cemetery_ID
  

  这个墓地有两个通用名称，所以名称列在不同的行中，这些行由一个id但两个名称id连接，查询产生这样的东西

  CemeteryID Cemetery_Name纬度
  1 Appleton，Sulpher Springs 35.4276242832293

  回复于 2024-05-07T06:28:18+08:00
• 2

  我将为解决此问题的步骤添加一些更长更详细的解释 . 如果太长，我道歉 .

  ---

  我已经给出并使用它来定义一些我将用于本文其余部分的术语 . 这将是 base table ：

  select * from history;
  
  +--------+----------+-----------+
  | hostid | itemname | itemvalue |
  +--------+----------+-----------+
  |      1 | A        |        10 |
  |      1 | B        |         3 |
  |      2 | A        |         9 |
  |      2 | C        |        40 |
  +--------+----------+-----------+
  

  这将是我们的目标 pretty pivot table ：

  select * from history_itemvalue_pivot;
  
  +--------+------+------+------+
  | hostid | A    | B    | C    |
  +--------+------+------+------+
  |      1 |   10 |    3 |    0 |
  |      2 |    9 |    0 |   40 |
  +--------+------+------+------+
  

  history.hostid 列中的值将在数据透视表中变为 y-values . history.itemname 列中的值将变为 x-values （出于显而易见的原因） .

  ---

  当我必须解决创建数据透视表的问题时，我使用三步过程（可选的第四步）来解决它：

  • 选择感兴趣的列，即 y-values 和 x-values

  • 用额外的列扩展基表 - 每个列 x-value

  • 分组并聚合扩展表 - 每个 y-value 一组

  • （可选）美化聚合表

  让我们将这些步骤应用到您的问题中，看看我们得到了什么：

  Step 1: select columns of interest . 在期望的结果中， hostid 提供 y-values ， itemname 提供 x-values .

  Step 2: extend the base table with extra columns . 我们通常每x值需要一列 . 回想一下，我们的x值列是 itemname ：

  create view history_extended as (
    select
      history.*,
      case when itemname = "A" then itemvalue end as A,
      case when itemname = "B" then itemvalue end as B,
      case when itemname = "C" then itemvalue end as C
    from history
  );
  
  select * from history_extended;
  
  +--------+----------+-----------+------+------+------+
  | hostid | itemname | itemvalue | A    | B    | C    |
  +--------+----------+-----------+------+------+------+
  |      1 | A        |        10 |   10 | NULL | NULL |
  |      1 | B        |         3 | NULL |    3 | NULL |
  |      2 | A        |         9 |    9 | NULL | NULL |
  |      2 | C        |        40 | NULL | NULL |   40 |
  +--------+----------+-----------+------+------+------+
  

  请注意，我们没有更改行数 - 我们只添加了额外的列 . 另请注意 NULL 的模式 - 带有 itemname = "A" 的行对于新列 A 具有非空值，而对于其他新列具有空值 .

  Step 3: group and aggregate the extended table . 我们需要 group by hostid ，因为它提供了y值：

  create view history_itemvalue_pivot as (
    select
      hostid,
      sum(A) as A,
      sum(B) as B,
      sum(C) as C
    from history_extended
    group by hostid
  );
  
  select * from history_itemvalue_pivot;
  
  +--------+------+------+------+
  | hostid | A    | B    | C    |
  +--------+------+------+------+
  |      1 |   10 |    3 | NULL |
  |      2 |    9 | NULL |   40 |
  +--------+------+------+------+
  

  （注意，我们现在每y值有一行 . ）好的，我们差不多了！我们只需要摆脱那些丑陋的 NULL .

  Step 4: prettify . 我们只是用零替换任何空值，因此结果集更好看：

  create view history_itemvalue_pivot_pretty as (
    select 
      hostid, 
      coalesce(A, 0) as A, 
      coalesce(B, 0) as B, 
      coalesce(C, 0) as C 
    from history_itemvalue_pivot 
  );
  
  select * from history_itemvalue_pivot_pretty;
  
  +--------+------+------+------+
  | hostid | A    | B    | C    |
  +--------+------+------+------+
  |      1 |   10 |    3 |    0 |
  |      2 |    9 |    0 |   40 |
  +--------+------+------+------+
  

  我们已经完成了 - 我们使用MySQL构建了一个漂亮，漂亮的数据透视表 .

  ---

  应用此过程时的注意事项：

  • 在额外列中使用什么值 . 我在这个例子中使用了 itemvalue

  • 在额外列中使用的"neutral"值 . 我使用 NULL ，但也可能是 0 或 "" ，具体取决于您的具体情况

  • 分组时要使用的聚合函数 . 我使用了 sum ，但是经常使用 count 和 max （ max 经常用于构建遍布多行的一行"objects"）

  • 使用多个列作为y值 . 此解决方案不仅限于使用单个列作为y值 - 只需将额外的列插入 group by 子句（并且不要忘记 select 它们）

  已知限制：

  • 这个解决方案并不令人难以理解 . 当数据透视表需要有很多列时，我目前还不知道解决这个问题的好方法 .

  回复于 2024-05-07T06:28:18+08:00
• 33

  我编辑Agung Sagita 's answer from subquery to join. I'我不确定这两种方式有多大区别，但仅供另一个参考 .

  SELECT  hostid, T2.VALUE AS A, T3.VALUE AS B, T4.VALUE AS C
  FROM TableTest AS T1
  LEFT JOIN TableTest T2 ON T2.hostid=T1.hostid AND T2.ITEMNAME='A'
  LEFT JOIN TableTest T3 ON T3.hostid=T1.hostid AND T3.ITEMNAME='B'
  LEFT JOIN TableTest T4 ON T4.hostid=T1.hostid AND T4.ITEMNAME='C'
  

  回复于 2024-05-07T06:28:18+08:00
• 3

  SELECT 
      hostid, 
      sum( if( itemname = 'A', itemvalue, 0 ) ) AS A,  
      sum( if( itemname = 'B', itemvalue, 0 ) ) AS B, 
      sum( if( itemname = 'C', itemvalue, 0 ) ) AS C 
  FROM 
      bob 
  GROUP BY 
      hostid;
  

  回复于 2024-05-07T06:28:18+08:00
• 7

  我找到了一种方法，使用简单的查询使我的报表将行转换为几乎动态的列 . 你可以看到并测试它online here .

  columns of query is fixed 的数量，但 values are dynamic 并基于行的值 . 你可以构建它所以，我使用一个查询来构建表头，另一个查询值：

  SELECT distinct concat('<th>',itemname,'</th>') as column_name_table_header FROM history order by 1;
  
  SELECT
       hostid
      ,(case when itemname = (select distinct itemname from history a order by 1 limit 0,1) then itemvalue else '' end) as col1
      ,(case when itemname = (select distinct itemname from history a order by 1 limit 1,1) then itemvalue else '' end) as col2
      ,(case when itemname = (select distinct itemname from history a order by 1 limit 2,1) then itemvalue else '' end) as col3
      ,(case when itemname = (select distinct itemname from history a order by 1 limit 3,1) then itemvalue else '' end) as col4
  FROM history order by 1;
  

  你也可以总结一下：

  SELECT
       hostid
      ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 0,1) then itemvalue end) as A
      ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 1,1) then itemvalue end) as B
      ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 2,1) then itemvalue end) as C
  FROM history group by hostid order by 1;
  +--------+------+------+------+
  | hostid | A    | B    | C    |
  +--------+------+------+------+
  |      1 |   10 |    3 | NULL |
  |      2 |    9 | NULL |   40 |
  +--------+------+------+------+
  

  结果RexTester：

  

  http://rextester.com/ZSWKS28923

  对于一个真实的使用示例，此报告在下面的列中显示了带有视觉时间表的船/公共汽车的离港时间 . 你会看到一个额外的在最后一个col没有使用的列而不会混淆可视化：
  
  **票务系统在线和预售的售票

  回复于 2024-05-07T06:28:18+08:00
• 209

  使用子查询

  SELECT  hostid, 
      (SELECT VALUE FROM TableTest WHERE ITEMNAME='A' AND hostid = t1.hostid) AS A,
      (SELECT VALUE FROM TableTest WHERE ITEMNAME='B' AND hostid = t1.hostid) AS B,
      (SELECT VALUE FROM TableTest WHERE ITEMNAME='C' AND hostid = t1.hostid) AS C
  FROM TableTest AS T1
  GROUP BY hostid
  

  但是如果子查询产生多于一行，则在子查询中使用更多聚合函数将是一个问题

  回复于 2024-05-07T06:28:18+08:00