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):

1. 选择感兴趣的列，即 y值和 x值
2. 用额外的列扩展基本表-每个 x值
3. 分组并汇总扩展表-每个 y值
一组
4. (可选)整理汇总表

1. select the columns of interest, i.e. y-values and x-values
2. extend the base table with extra columns -- one for each x-value
3. group and aggregate the extended table -- one group for each y-value
4. (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 NULLs -- 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 NULLs.

第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 be 0 or "", depending on your exact situation
• what aggregate function to use when grouping. I used sum, but count and max 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 to select them)

已知限制:

• 此解决方案不允许数据透视表中有n列-扩展基表时，需要手动添加每个数据透视列.因此，对于5或10个x值，此解决方案很好.为100，不是很好.有一些存储过程生成查询的解决方案，但是它们很丑陋，很难正确处理.当数据透视表需要有很多列时，我目前不知道解决此问题的好方法.

这篇关于MySQL-行到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！