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