MySQL将多行转换为单行中的列 [英] mysql converting multiple rows into columns in a single row

问题描述

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

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将多行转换为单行中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！