我试着搜索帖子,但我只找到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 |
   +--------+------+-----+-----+

当前回答

我编辑阿贡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'

其他回答

我的解决方案:

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

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

利用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

如果你可以使用MariaDB,有一个非常非常简单的解决方案。

自MariaDB-10.02以来,添加了一个新的存储引擎CONNECT,可以帮助我们将另一个查询或表的结果转换为数据透视表,就像你想要的那样: 你可以看看这些文件。

首先安装connect存储引擎。

现在我们的表的主列是itemname,每一项的数据都位于itemvalue列中,所以我们可以使用这个查询得到结果数据透视表:

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

现在我们可以从数据透视表中选择我们想要的:

select * from pivot_table

详情请点击这里

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;

我编辑阿贡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'