问题描述
我有一个包含列的详细信息表:
i have a details table with columns:
- user_id int
- 代码诠释
- 值int
我想建立一个汇总表,如下所示:
And i want to build a summary table that looks like:
- user_id int
- valueA int
- valueB int
在详细信息表中,valueA对应于说代码5,而valueB对应于说代码6,所以我正在寻找类似的东西:
In the details table, valueA would correspond to say, code 5, and valueB would correspond to say, code 6, so i'm looking for something like:
插入摘要(user_id,valueA,valueB)VALUES(从详细信息中选择???);
insert into summary (user_id,valueA,valueB) VALUES ( SELECT ??? from details );
当然,问题在于我正在查看详细信息"表中的多行以填充摘要"表中的一行.
The problem of course is that i'm looking at multiple rows from the "details" table to populate one row in the "summary" table.
例如,如果我在详细信息中包含以下行:
Eg, if i had the following rows in details:
1 5 100
1 6 200
2 5 1000
2 6 2000
我想在汇总表中添加以下内容:
I want to end up with the following in the summary table:
1 100 200
2 1000 2000
有什么想法吗?
推荐答案
MySQL没有PIVOT/UNPIVOT语法,这使您可以结合使用GROUP BY和CASE表达式:
MySQL doesn't have PIVOT/UNPIVOT syntax, which leaves you to use a combination of GROUP BY and CASE expressions:
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
这篇关于MySQL将多行转换为单行中的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!