MySQL -行到列 - 开卷题库

小开

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;

小开

我把它变成Group By hostId，然后它将只显示第一行的值，
如:< / p >

A   B  C
1  10
2      3

小开

使用子查询

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

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

小开

我将对解决这个问题的步骤进行更长、更详细的解释。如果时间太长，我很抱歉。

我将从你给出的基础开始，用它来定义几个术语，我将在这篇文章的其余部分使用这些术语。这将是基表:

select * from history;


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

这就是我们的目标，漂亮的数据透视表:

select * from history_itemvalue_pivot;


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

history.hostid列中的值将变成数据透视表中的y的值。history.itemname列中的值将变成的交点(原因很明显)。

当我必须解决创建数据透视表的问题时，我使用了一个三步过程(可选的第四步)来解决它:

选择感兴趣的列，即y的值和的交点
用额外的列扩展基表——每个x值对应一列
组并聚合扩展表——每个y值对应一个组
(可选)修饰聚合表

让我们把这些步骤应用到你的问题中，看看会得到什么:

步骤1:选择感兴趣的列。在期望的结果中，hostid提供y的值，而itemname提供的交点。

步骤2:用额外的列扩展基本表。每个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 |
+--------+----------+-----------+------+------+------+

注意，我们没有改变行数——我们只是增加了额外的列。还要注意__abc0的模式——包含itemname = "A"的行对新列A具有非空值，对其他新列具有空值。

步骤3:对扩展表进行分组和聚合。我们需要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 |
+--------+------+------+------+

好了，我们快到了!我们只需要摆脱那些丑陋的__abc。

第四步:美化。我们只是将null值替换为0，这样结果集看起来会更好:

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
在额外列中使用什么“中性”值。我使用了NULL，但它也可以是0或""，这取决于你的确切情况
分组时使用什么聚合函数。我使用了sum，但count和max也经常被使用(max通常用于构建单行“对象”，该对象已分布在许多行中)
用多列表示y值。这个解决方案并不局限于为y值使用单列——只需将额外的列插入group by子句(并且不要忘记select它们)

已知的限制:

这个解决方案不允许在数据透视表中有n个列——每个主列都需要在扩展基表时手动添加。对于5或10个x值，这个解很好。100美元，不太好。有一些存储过程生成查询的解决方案，但它们很难看，而且很难正确使用。当数据透视表需要有很多列时，我目前不知道有什么好方法来解决这个问题。

小开

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

小开

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

小开

这不是你正在寻找的确切答案，但这是一个解决方案，我需要在我的项目，希望这有助于某人。这将列出用逗号分隔的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，查询产生如下内容

纬度
1,,,,,,,,,,,,,,,,,,,,,阿普尔顿，苏福斯普林斯35.4276242832293

小开

我的解决方案:

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

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

小开

另一个选择，尤其有用，如果你有很多项，你需要枢轴是让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;

提琴添加了一些额外的值，以看到它工作

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

小开

我找到了一种方法，使用简单的查询使我的报告几乎动态地将行转换为列。你可以看到并测试它在线这里。

查询列是固定的的个数，而不是价值是动态的和基于行的值。所以，我使用一个查询来构建表头，另一个查询来查看值:

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而不会混淆可视化:

sistema venda de passagens online e consumidor final e control de frota - xsl tecnologia - xsl.com.br

**票务系统的在线售票和存在

小开

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

CREATE EXTENSION tablefunc;

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

CREATE EXTENSION hstore;

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

小开

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

自从mariadb - 10.02之后，添加了一个名为连接的新存储引擎，它可以帮助我们将另一个查询或表的结果转换为数据透视表，就像你想要的那样: 你可以看看的文档.

首先，安装connect存储引擎。

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

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

现在我们可以从pivot_table中选择我们想要的:

select * from pivot_table

更多细节在这里

小开

你可以使用几个__abc。请使用此代码

SELECT t.hostid,
COALESCE(t1.itemvalue, 0) A,
COALESCE(t2.itemvalue, 0) B,
COALESCE(t3.itemvalue, 0) C
FROM history t
LEFT JOIN history t1
ON t1.hostid = t.hostid
AND t1.itemname = 'A'
LEFT JOIN history t2
ON t2.hostid = t.hostid
AND t2.itemname = 'B'
LEFT JOIN history t3
ON t3.hostid = t.hostid
AND t3.itemname = 'C'
GROUP BY t.hostid