MySQL-行到列 [英] MySQL - Rows to Columns
问题描述
我尝试搜索帖子,但只找到了SQL Server/Access的解决方案.我需要MySQL(5.X)中的解决方案.
I tried to search posts, but I only found solutions for SQL Server/Access. I need a solution in MySQL (5.X).
我有一个表(称为历史记录),其中包含3列:hostid,itemname,itemvalue.
如果我选择(select * from history
),它将返回
I have a table (called history) with 3 columns: hostid, itemname, itemvalue.
If I do a select (select * from history
), it will return
+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
| 1 | A | 10 |
+--------+----------+-----------+
| 1 | B | 3 |
+--------+----------+-----------+
| 2 | A | 9 |
+--------+----------+-----------+
| 2 | c | 40 |
+--------+----------+-----------+
如何查询数据库以返回类似的内容
How do I query the database to return something like
+--------+------+-----+-----+
| hostid | A | B | C |
+--------+------+-----+-----+
| 1 | 10 | 3 | 0 |
+--------+------+-----+-----+
| 2 | 9 | 0 | 40 |
+--------+------+-----+-----+
推荐答案
我将对解决该问题所需采取的步骤添加一些较长且更详细的说明.如果太长,我深表歉意.
I'm going to add a somewhat longer and more detailed explanation of the steps to take to solve this problem. I apologize if it's too long.
我将从您给出的基础开始,并用它来定义几个术语,这些术语将在本文的其余部分中使用.这将是基本表:
I'll start out with the base you've given and use it to define a couple of terms that I'll use for the rest of this post. This will be the base table:
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值.
Values in the history.hostid
column will become y-values in the pivot table. Values in the history.itemname
column will become x-values (for obvious reasons).
当我必须解决创建数据透视表的问题时,我可以通过三步过程(包括可选的第四步)来解决它:
When I have to solve the problem of creating a pivot table, I tackle it using a three-step process (with an optional fourth step):
- 选择感兴趣的列,即 y值和 x值
- 用额外的列扩展基本表-每个 x值
- 分组并汇总扩展表-每个 y值 一组
- (可选)整理汇总表
- select the columns of interest, i.e. y-values and x-values
- extend the base table with extra columns -- one for each x-value
- group and aggregate the extended table -- one group for each y-value
- (optional) prettify the aggregated table
让我们将这些步骤应用于您的问题,看看我们能得到什么:
Let's apply these steps to your problem and see what we get:
第1步:选择感兴趣的列.在期望的结果中,hostid
提供 y值,itemname
提供 x值.
Step 1: select columns of interest. In the desired result, hostid
provides the y-values and itemname
provides the x-values.
步骤2:使用额外的列扩展基本表.每个x值通常需要一列.回想一下,我们的x值列为itemname
:
Step 2: extend the base table with extra columns. We typically need one column per x-value. Recall that our x-value column is 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 |
+--------+----------+-----------+------+------+------+
请注意,我们没有更改行数-我们只是添加了额外的列.还要注意NULL
的模式-带有itemname = "A"
的行的新列A
具有非空值,其他新列具有空值.
Note that we didn't change the number of rows -- we just added extra columns. Also note the pattern of NULL
s -- a row with itemname = "A"
has a non-null value for new column A
, and null values for the other new columns.
步骤3:对扩展表进行分组和汇总.我们需要group by hostid
,因为它提供了y值:
Step 3: group and aggregate the extended table. We need to group by hostid
, since it provides the y-values:
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
s.
(Note that we now have one row per y-value.) Okay, we're almost there! We just need to get rid of those ugly NULL
s.
第4步:美化.我们将用零替换所有空值,以便更好地查看结果集:
Step 4: prettify. We're just going to replace any null values with zeroes so the result set is nicer to look at:
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建立了一个漂亮的,漂亮的数据透视表.
And we're done -- we've built a nice, pretty pivot table using MySQL.
应用此过程时的注意事项:
Considerations when applying this procedure:
- 在额外的列中使用什么值.在此示例中,我使用了
itemvalue
- 在额外的列中使用什么中性"值.我使用了
NULL
,但也可能是0
或""
,具体取决于您的具体情况 - 分组时要使用的聚合函数.我使用了
sum
,但也经常使用count
和max
(在构建散布在许多行中的单行对象"时,经常使用max
) - 使用多个列作为y值.此解决方案不仅限于对y值使用单个列-只需将多余的列插入
group by
子句即可(不要忘记对它们进行select
)
- what value to use in the extra columns. I used
itemvalue
in this example - what "neutral" value to use in the extra columns. I used
NULL
, but it could also be0
or""
, depending on your exact situation - what aggregate function to use when grouping. I used
sum
, butcount
andmax
are also often used (max
is often used when building one-row "objects" that had been spread across many rows) - using multiple columns for y-values. This solution isn't limited to using a single column for the y-values -- just plug the extra columns into the
group by
clause (and don't forget toselect
them)
已知限制:
- 此解决方案不允许数据透视表中有n列-扩展基表时,需要手动添加每个数据透视列.因此,对于5或10个x值,此解决方案很好.为100,不是很好.有一些存储过程生成查询的解决方案,但是它们很丑陋,很难正确处理.当数据透视表需要有很多列时,我目前不知道解决此问题的好方法.
这篇关于MySQL-行到列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!