MySQL -行到列

我试着搜索帖子，但我只找到SQL Server/Access的解决方案。我需要一个解决方案在MySQL (5.X)。

我有一个表(称为历史)3列:hostid, itemname, itemvalue。如果我执行select (select * from history)，它会返回

   +--------+----------+-----------+
   | hostid | itemname | itemvalue |
   +--------+----------+-----------+
   |   1    |    A     |    10     |
   +--------+----------+-----------+
   |   1    |    B     |     3     |
   +--------+----------+-----------+
   |   2    |    A     |     9     |
   +--------+----------+-----------+
   |   2    |    C     |    40     |
   +--------+----------+-----------+

如何查询数据库以返回类似的内容

   +--------+------+-----+-----+
   | hostid |   A  |  B  |  C  |
   +--------+------+-----+-----+
   |   1    |  10  |  3  |  0  |
   +--------+------+-----+-----+
   |   2    |   9  |  0  |  40 |
   +--------+------+-----+-----+

当前回答

使用子查询

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

但如果子查询结果超过一行，则会出现问题，在子查询中使用进一步的聚合函数

2011-11-02 06:00:53

其他回答

我的解决方案:

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

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

2014-01-29 21:06:37

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;

2009-08-10 13:12:13

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

CREATE EXTENSION tablefunc;

然后瞧!这里有大量的文档:PostgreSQL: documentation: 9.1: tablefunc或this查询

CREATE EXTENSION hstore;

然后，瞧!PostgreSQL: Documentation: 9.0: hstore

2019-05-21 21:54:31

使用子查询

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

但如果子查询结果超过一行，则会出现问题，在子查询中使用进一步的聚合函数

2011-11-02 06:00:53

我编辑阿贡Sagita的答案从子查询加入。我不确定这两种方式有多大区别，但只是作为另一个参考。

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'

2012-12-19 07:22:31

MySQL -行到列

推荐文章

最新文章

标签