mysql-行到列

x33g5p2x  于 2021-07-24  发布在  Java
关注(0)|答案(12)|浏览(390)

我试图搜索帖子,但只找到了针对sql server/access的解决方案。我需要一个mysql(5.x)的解决方案。
我有一个表(称为history),有3列:hostid、itemname、itemvalue。
如果我选择( select * from history ),它会回来的

  1. +--------+----------+-----------+
  2. | hostid | itemname | itemvalue |
  3. +--------+----------+-----------+
  4. | 1 | A | 10 |
  5. +--------+----------+-----------+
  6. | 1 | B | 3 |
  7. +--------+----------+-----------+
  8. | 2 | A | 9 |
  9. +--------+----------+-----------+
  10. | 2 | c | 40 |
  11. +--------+----------+-----------+

如何查询数据库以返回

  1. +--------+------+-----+-----+
  2. | hostid | A | B | C |
  3. +--------+------+-----+-----+
  4. | 1 | 10 | 3 | 0 |
  5. +--------+------+-----+-----+
  6. | 2 | 9 | 0 | 40 |
  7. +--------+------+-----+-----+
ssm49v7z

ssm49v7z1#

我将对解决这个问题所要采取的步骤做一个更长更详细的解释。如果时间太长,我道歉。
我将从你给出的基础开始,用它来定义几个术语,我将在本文的其余部分使用这些术语。这将是基表:

  1. select * from history;
  2. +--------+----------+-----------+
  3. | hostid | itemname | itemvalue |
  4. +--------+----------+-----------+
  5. | 1 | A | 10 |
  6. | 1 | B | 3 |
  7. | 2 | A | 9 |
  8. | 2 | C | 40 |
  9. +--------+----------+-----------+

这就是我们的目标,漂亮的透视表:

  1. select * from history_itemvalue_pivot;
  2. +--------+------+------+------+
  3. | hostid | A | B | C |
  4. +--------+------+------+------+
  5. | 1 | 10 | 3 | 0 |
  6. | 2 | 9 | 0 | 40 |
  7. +--------+------+------+------+

中的值 history.hostid 列将成为透视表中的y值。中的值 history.itemname 列将成为x值(原因很明显)。
当我必须解决创建透视表的问题时,我使用三步流程(可选的第四步)来解决它:
选择感兴趣的列,即y值和x值
用额外的列扩展基表——每个x值一列
分组并聚合扩展表——每个y值一个组
(可选)美化聚合表
让我们将这些步骤应用于您的问题,看看我们得到了什么:
步骤1:选择感兴趣的列。在期望的结果中, hostid 提供y值和 itemname 提供x值。
步骤2:用额外的列扩展基表。通常每个x值需要一列。回想一下,我们的x值列是 itemname :

  1. create view history_extended as (
  2. select
  3. history.*,
  4. case when itemname = "A" then itemvalue end as A,
  5. case when itemname = "B" then itemvalue end as B,
  6. case when itemname = "C" then itemvalue end as C
  7. from history
  8. );
  9. select * from history_extended;
  10. +--------+----------+-----------+------+------+------+
  11. | hostid | itemname | itemvalue | A | B | C |
  12. +--------+----------+-----------+------+------+------+
  13. | 1 | A | 10 | 10 | NULL | NULL |
  14. | 1 | B | 3 | NULL | 3 | NULL |
  15. | 2 | A | 9 | 9 | NULL | NULL |
  16. | 2 | C | 40 | NULL | NULL | 40 |
  17. +--------+----------+-----------+------+------+------+

请注意,我们没有更改行数--只是添加了额外的列。还要注意 NULL s—与 itemname = "A" 新列的值为非空 A ,以及其他新列的空值。
步骤3:对扩展表进行分组和聚合。我们需要 group by hostid ,因为它提供y值:

  1. create view history_itemvalue_pivot as (
  2. select
  3. hostid,
  4. sum(A) as A,
  5. sum(B) as B,
  6. sum(C) as C
  7. from history_extended
  8. group by hostid
  9. );
  10. select * from history_itemvalue_pivot;
  11. +--------+------+------+------+
  12. | hostid | A | B | C |
  13. +--------+------+------+------+
  14. | 1 | 10 | 3 | NULL |
  15. | 2 | 9 | NULL | 40 |
  16. +--------+------+------+------+

(请注意,现在每个y值有一行。)好的,我们就快到了!我们只需要除掉那些丑陋的东西 NULL s。
第四步:美化。我们只是将所有空值替换为零,这样结果集更易于查看:

  1. create view history_itemvalue_pivot_pretty as (
  2. select
  3. hostid,
  4. coalesce(A, 0) as A,
  5. coalesce(B, 0) as B,
  6. coalesce(C, 0) as C
  7. from history_itemvalue_pivot
  8. );
  9. select * from history_itemvalue_pivot_pretty;
  10. +--------+------+------+------+
  11. | hostid | A | B | C |
  12. +--------+------+------+------+
  13. | 1 | 10 | 3 | 0 |
  14. | 2 | 9 | 0 | 40 |
  15. +--------+------+------+------+

我们完成了——我们用mysql构建了一个漂亮的透视表。
应用此程序时的注意事项:
在额外列中使用什么值。我曾经 itemvalue 在这个例子中
在额外的列中使用什么“中性”值。我曾经 NULL ,但也可能是 0 或者 "" ,取决于你的具体情况
分组时要使用的聚合函数。我曾经 sum ,但是 count 以及 max 也经常使用( max 通常在构建一行“对象”时使用(对象已分布在多行中)
对y值使用多列。这个解决方案并不局限于对y值使用单个列——只需将额外的列插入到 group by 条款(别忘了 select (他们)
已知限制:
此解决方案不允许透视表中有n列—在扩展基表时,需要手动添加每个透视列。所以对于5或10个x值,这个解决方案很好。100块,不太好。有些解决方案使用存储过程生成查询,但它们很难看,而且很难获得正确的结果。当透视表需要有很多列时,我目前还不知道有什么好方法来解决这个问题。

展开查看全部
ruyhziif

ruyhziif2#

  1. SELECT
  2. hostid,
  3. sum( if( itemname = 'A', itemvalue, 0 ) ) AS A,
  4. sum( if( itemname = 'B', itemvalue, 0 ) ) AS B,
  5. sum( if( itemname = 'C', itemvalue, 0 ) ) AS C
  6. FROM
  7. bob
  8. GROUP BY
  9. hostid;
xkftehaa

xkftehaa3#

另一个选择,如果您有许多需要透视的项,那么让mysql为您构建查询尤其有用:

  1. SELECT
  2. GROUP_CONCAT(DISTINCT
  3. CONCAT(
  4. 'ifnull(SUM(case when itemname = ''',
  5. itemname,
  6. ''' then itemvalue end),0) AS `',
  7. itemname, '`'
  8. )
  9. ) INTO @sql
  10. FROM
  11. history;
  12. SET @sql = CONCAT('SELECT hostid, ', @sql, '
  13. FROM history
  14. GROUP BY hostid');
  15. PREPARE stmt FROM @sql;
  16. EXECUTE stmt;
  17. DEALLOCATE PREPARE stmt;

fiddle添加了一些额外的值来查看它的工作情况 GROUP_CONCAT 有一个默认值1000,所以如果您有一个非常大的查询,请在运行它之前更改此参数

  1. SET SESSION group_concat_max_len = 1000000;

测试:

  1. DROP TABLE IF EXISTS history;
  2. CREATE TABLE history
  3. (hostid INT,
  4. itemname VARCHAR(5),
  5. itemvalue INT);
  6. INSERT INTO history VALUES(1,'A',10),(1,'B',3),(2,'A',9),
  7. (2,'C',40),(2,'D',5),
  8. (3,'A',14),(3,'B',67),(3,'D',8);
  9. hostid A B C D
  10. 1 10 3 0 0
  11. 2 9 0 40 5
  12. 3 14 67 0 8
展开查看全部
niwlg2el

niwlg2el4#

利用matt fenwick帮助我解决问题的想法(非常感谢),让我们将其简化为一个查询:

  1. select
  2. history.*,
  3. coalesce(sum(case when itemname = "A" then itemvalue end), 0) as A,
  4. coalesce(sum(case when itemname = "B" then itemvalue end), 0) as B,
  5. coalesce(sum(case when itemname = "C" then itemvalue end), 0) as C
  6. from history
  7. group by hostid
nkhmeac6

nkhmeac65#

我从子查询中编辑agung sagita的答案以加入。我不确定这两种方式之间有多大的区别,但仅供参考。

  1. SELECT hostid, T2.VALUE AS A, T3.VALUE AS B, T4.VALUE AS C
  2. FROM TableTest AS T1
  3. LEFT JOIN TableTest T2 ON T2.hostid=T1.hostid AND T2.ITEMNAME='A'
  4. LEFT JOIN TableTest T3 ON T3.hostid=T1.hostid AND T3.ITEMNAME='B'
  5. LEFT JOIN TableTest T4 ON T4.hostid=T1.hostid AND T4.ITEMNAME='C'
gywdnpxw

gywdnpxw6#

使用子查询

  1. SELECT hostid,
  2. (SELECT VALUE FROM TableTest WHERE ITEMNAME='A' AND hostid = t1.hostid) AS A,
  3. (SELECT VALUE FROM TableTest WHERE ITEMNAME='B' AND hostid = t1.hostid) AS B,
  4. (SELECT VALUE FROM TableTest WHERE ITEMNAME='C' AND hostid = t1.hostid) AS C
  5. FROM TableTest AS T1
  6. GROUP BY hostid

但如果子查询产生的结果多于一行,则会出现问题,请在子查询中使用进一步的聚合函数

rvpgvaaj

rvpgvaaj7#

我的解决方案:

  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 (
  2. select
  3. hostid,
  4. case when itemName = 'A' then itemvalue end as A,
  5. case when itemName = 'B' then itemvalue end as B,
  6. case when itemName = 'C' then itemvalue end as C
  7. from history
  8. ) h group by hostid

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

8ehkhllq

8ehkhllq8#

我找到了一种方法,可以使用简单的查询使报表的行到列的转换几乎是动态的。你可以在这里在线查看和测试。
查询的列数是固定的,但值是动态的,并且基于行的值。您可以构建它,因此,我使用一个查询构建表头,另一个查询查看值:

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

你也可以总结一下:

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

测试结果:

http://rextester.com/zswks28923
作为一个实际使用的例子,本报告在下面的列中显示了船/巴士的出发时间和到达时间,并提供了一个可视化的时间表。您将看到最后一列中未使用的另一列,而不会混淆可视化效果:

**网上售票系统

展开查看全部
5ssjco0h

5ssjco0h9#

我把它变成 Group By hostId 然后它将只显示第一行的值,
比如:

  1. A B C
  2. 1 10
  3. 2 3
rn0zuynd

rn0zuynd10#

如果你能用mariadb有一个非常简单的解决方案。
自从mariadb-10.02以来,添加了一个名为connect的新存储引擎,它可以帮助我们将另一个查询或表的结果转换为pivot表,就像您想要的那样:您可以查看文档。
首先安装连接存储引擎。
现在表的透视列是 itemname 每个项目的数据位于 itemvalue 列,因此我们可以使用此查询获得结果透视表:

  1. create table pivot_table
  2. engine=connect table_type=pivot tabname=history
  3. option_list='PivotCol=itemname,FncCol=itemvalue';

现在我们可以从 pivot_table :

  1. select * from pivot_table

更多详情请点击此处

2w2cym1i

2w2cym1i11#

这不是确切的答案,你正在寻找,但这是一个解决方案,我需要在我的项目,希望这有助于某人。这将列出由逗号分隔的1到n行项目。在mysql中,groupconcat使这成为可能。

  1. select
  2. cemetery.cemetery_id as "Cemetery_ID",
  3. GROUP_CONCAT(distinct(names.name)) as "Cemetery_Name",
  4. cemetery.latitude as Latitude,
  5. cemetery.longitude as Longitude,
  6. c.Contact_Info,
  7. d.Direction_Type,
  8. d.Directions
  9. from cemetery
  10. left join cemetery_names on cemetery.cemetery_id = cemetery_names.cemetery_id
  11. left join names on cemetery_names.name_id = names.name_id
  12. left join cemetery_contact on cemetery.cemetery_id = cemetery_contact.cemetery_id
  13. left join
  14. (
  15. select
  16. cemetery_contact.cemetery_id as cID,
  17. group_concat(contacts.name, char(32), phone.number) as Contact_Info
  18. from cemetery_contact
  19. left join contacts on cemetery_contact.contact_id = contacts.contact_id
  20. left join phone on cemetery_contact.contact_id = phone.contact_id
  21. group by cID
  22. )
  23. as c on c.cID = cemetery.cemetery_id
  24. left join
  25. (
  26. select
  27. cemetery_id as dID,
  28. group_concat(direction_type.direction_type) as Direction_Type,
  29. group_concat(directions.value , char(13), char(9)) as Directions
  30. from directions
  31. left join direction_type on directions.type = direction_type.direction_type_id
  32. group by dID
  33. )
  34. as d on d.dID = cemetery.cemetery_id
  35. group by Cemetery_ID

这个公墓有两个公共名称,因此名称列在不同的行中,由一个id连接,但有两个名称id,查询产生如下结果
    墓地ID    墓地名称            纬度
    1                   阿普尔顿,萨弗斯普林斯  35.4276242832293

展开查看全部
6tr1vspr

6tr1vspr12#

我很抱歉这么说,也许我没有完全解决你的问题,但postgresql比mysql早10年,与mysql相比非常先进,有很多方法可以轻松实现这一点。安装postgresql并执行此查询

  1. CREATE EXTENSION tablefunc;

那你瞧!这里有大量的文档:postgresql:documentation:9.1:tablefunc或这个查询

  1. CREATE EXTENSION hstore;

再说一遍,瞧!postgresql:文档:9.0:hs存储

相关问题