mysql 行 列_MySQL-行到列

匿名用户

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

我将从你给出的基础开始，并用它来定义几个术语，我将在这篇文章的其余部分中使用这些术语。 这将是基表: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列中的值将成为X值(原因显而易见)。

当我必须解决创建透视表的问题时，我使用三个步骤(还有可选的第四个步骤)来解决这个问题:选择感兴趣的列，即y值和x值

使用额外列扩展基表--每个X值对应一列

对扩展表进行分组和聚合--每个y值对应一个组

(可选)美化聚合表

让我们将这些步骤应用于您的问题，看看我们得到了什么:

步骤1:选择感兴趣的列。 在所需的结果中，hostid提供y值，itemname提供x值。

步骤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 |

+--------+----------+-----------+------+------+------+

注意，我们没有更改行数--我们只是添加了额外的列。 还要注意nullS的模式--具有itemname=“a”的行对于新列a具有非null值，对于其他新列具有null值。

步骤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 |

+--------+------+------+------+

(请注意，我们现在每个y值有一行。) 好了，我们快到了！ 我们只需要去掉那些难看的null。

第四步:美化。 我们只是将所有空值替换为零，这样结果集看起来更好: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

在额外列中使用什么“neutral”值。 我使用了null，但也可以是0或“”，这取决于您的具体情况

分组时使用的聚合函数。 我使用了sum，但也经常使用count和max(max经常用于构建分散在许多行上的单行“对象”)

对y值使用多列。 这个解决方案并不局限于对y值使用单个列--只需将额外的列插入GROUP BY子句(不要忘记selectthey)

已知限制:此解决方案不允许在透视表中包含n列--在扩展基表时需要手动添加每个透视列。 对于5或10个x值，这个解决方案很好。 一百块钱，不太好。 有一些使用存储过程生成查询的解决方案，但是它们很难看，很难正确处理。 当数据透视表需要有很多列时，我目前还不知道解决这个问题的好方法。