SQL:将左连接行重新格式化为列[重复]

我将对解决这个问题需要采取的步骤进行更长更详细的解释。如果时间太长,我道歉。

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

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值 (原因显而易见)。

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

1. 选择感兴趣的列,即。 y值 和 x值
2. 用额外的列扩展基表——每列一列 x值
3. 对扩展表进行分组和聚合——每个表一组 y值
4. (可选)美化聚合表

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

步骤1:选择感兴趣的列 .在期望的结果中, hostid 提供 y值 和 itemname 提供 x值 .

第2步:用额外的列扩展基表 .我们通常每个x值需要一列。回想一下,我们的x值列是 项目名称 :

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 ,以及其他新列的空值。

步骤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值有一行。) 好了,我们快到了!我们只需要摆脱那些丑陋的 无效的 s

第四步:美化 .我们只是将任何空值替换为零,因此结果集更易于查看:

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 在这个例子中
• 在额外列中使用什么“中性”值。我曾经 无效的 ,但也可能是 0 或 "" ,这取决于你的具体情况
• 分组时使用的聚合函数。我曾经 sum 但是 count 和 max 也经常使用( 最大值 通常在构建一行“对象”时使用,该对象已分布在多行中)
• 对y值使用多列。这个解决方案并不局限于对y值使用单个列——只需将额外的列插入 group by 条款(别忘了 select (他们)

已知的限制:

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