mysql 将多行转换为单行中的列

Question

我有一个包含列的详细信息表：

• user_id int
• code int
• value int

我想构建一个如下所示的汇总表：

• user_id int
• valueA int
• valueB int

在详细信息表中， valueA 将对应于代码 5， valueB 将对应就是说，代码 6，所以我正在寻找类似的内容：

插入摘要 (user_id,valueA,valueB) VALUES (从详细信息中选择 ???);

当然，问题是我正在查看“详细信息”表中的多行以填充“摘要”表中的一行。

例如，如果我有以下详细信息：

1  5  100
1  6  200
2  5  1000
2  6  2000

我想在汇总表中得到以下内容：

1  100   200
2  1000  2000

有什么想法吗？

Answer 1

MySQL 没有 PIVOT/UNPIVOT 语法，这使您只能使用 GROUP BY 和 CASE 表达式的组合：

INSERT INTO SUMMARY
  (user_id,valueA,valueB) 
  SELECT d.user_id,
         MAX(CASE WHEN d.code = 5 THEN d.value ELSE NULL END),
         MAX(CASE WHEN d.code = 6 THEN d.value ELSE NULL END),
    FROM DETAILS d
GROUP BY d.user_id

Answer 2

insert into summary (user_id,valueA,valueB) 
SELECT a.user_id, a.value, b.value 
from details a 
join details b on a.user_id = b.user_id 
WHERE a.code = 5 and b.code = 6;

请注意：如果 user_id+code 不唯一，您最终会得到多个摘要列。

编辑：

insert into summary (user_id,valueA,valueB) 
select u.user_id, ifnull(a.value,0), ifnull(b.value,0)
from (select distinct user_id from details /* where code in (5,6) */) u
left join details a on a.user_id = u.user_id and a.code = 5
left join details b on b.user_id = u.user_id and b.code = 6

Answer 3

如果您有一组可管理的代码（例如仅 5 和 6），您可以执行以下操作：

SELECT details.user_id, code5.value, code6.value
FROM details JOIN
  (SELECT user_id, value FROM details WHERE code = 5) AS code5 USING(user_id)
  JOIN
  (SELECT user_id, value FROM details WHERE code = 6) AS code6 USING(user_id);

您可能需要修改您的 JOIN，具体取决于您的代码是否不需要作为 1 对 1 关系（即LEFT JOIN）。

如果您有大量代码，我会研究游标在代码结果集上运行类似的查询或使用不同的技术（即 PHP 脚本）。