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

i have a details table with columns:

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

user_id int
code int
value int

And i want to build a summary table that looks like:

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

user_id int
valueA int
valueB int

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:

在详细信息表中,valueA对应于say,代码5,valueB对应于say,代码6,所以我正在寻找类似的东西:

insert into summary (user_id,valueA,valueB) VALUES ( SELECT ??? from details );

插入摘要(user_id,valueA,valueB)VALUES(SELECT from 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:

例如,如果我有详细的以下行:

I want to end up with the following in the summary table:

我想在摘要表中得到以下结果:

1  100   200
2  1000  2000

Any ideas?

3 个解决方案

#1

MySQL doesn't have PIVOT/UNPIVOT syntax, which leaves you to use a combination of GROUP BY and CASE expressions:

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

#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;

beware: you will end up with multiple summary columns if user_id+code is not unique.

注意:如果user_id +代码不唯一,您将最终得到多个摘要列。

EDIT:

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

#3

If you have a manageable set of codes (say just 5 and 6) you could do something like this:

如果你有一套可管理的代码(例如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);

You may need to modify your JOINs depending on if your codes are not required as 1 to 1 relationship (i.e. LEFT JOINs).

您可能需要修改您的JOIN,具体取决于您的代码是否需要为1对1关系(即LEFT JOIN)。

If you have a large set of codes, I would look into a cursor runs a similar query above over a result set of your codes or using a different technology, (i.e. PHP script).

如果你有一大堆代码,我会调查一个游标在你的代码的结果集上运行一个类似的查询或使用不同的技术(即PHP脚本)。

#1