MySQL查询可以将行变成列吗? [英] Can a MySQL query turn rows into columns?

问题描述

我有很多表要与联接合并，但是这样，结果以许多行返回，而我希望将它们作为新列生成.

I have a number of tables I am trying to combine with joins but as such, the results are returned in a number of rows whereas I would like to have them generated as new columns.

MemberID | FirstName | LastName 
---------------------------------
   1     |   John    |  Harris
   2     |   Sarah   |  Thompson
   3     |   Zack    |  Lewis

member_dependent_information表

MemberID | FirstName | LastName | Type
---------------------------------------
   1     |   Amy     | Harris   |  1 
   2     |   Bryan   | Thompson |  1
   2     |   Dewey   | Thompson |  2
   2     |   Tom     | Thompson |  2
   3     |   Harry   | Lewis    |  2
   3     |   Minka   | Lewis    |  1

MySQL查询:

SELECT
    t1.FirstName,
    t1.LastName,
    t1.MemberID,
    IF(t2.Type = '1',CONCAT(t2.FirstName,' ',t2.LastName),'') AS Spouse_Name,
    IF(t2.Type = '2',CONCAT(t2.FirstName,' ',t2.LastName),'') AS Child_Name,

FROM
    member_dependent_information t2
INNER JOIN
    member_information t1
USING
    (MemberID)
ORDER BY
    t1.LastName ASC,
    t1.MemberID ASC;

理想的结果

MemberID | FirstName | LastName  | Spouse_Name    | Child_Name1   | Child_Name2 
--------------------------------------------------------------------------------
   1     |   John    |  Harris   | Amy Harris     |     NULL       |   NULL    
   2     |   Sarah   |  Thompson | Bryan Thompson | Dewey Thompson | Tom Thompson    
   3     |   Zack    |  Lewis    | Mika Lewis     | Harry Lewis    |   NULL

实际结果

MemberID | FirstName | LastName  | Spouse_Name    | Child_Name 
-------------------------------------------------------------------
   1     |   John    |  Harris   | Amy Harris     |    NULL
   2     |   Sarah   |  Thompson | Bryan Thompson |    NULL  
   2     |   Sarah   |  Thompson |      NULL      | Dewey Thompson  
   2     |   Sarah   |  Thompson |      NULL      | Tom Thompson    
   3     |   Zack    |  Lewis    | Mika Lewis     |    NULL      
   3     |   Zack    |  Lewis    |      NULL      | Harry Lewis    

虽然我的查询在多行中返回正确"数据，但并没有根据需要将结果合并为一行.

While my query returns the "correct" data in multiple rows, it does not combine the result into one single row as needed.

下面已经提到了有关数据透视表/交叉表的建议，但是我能够找到的每个参考文献都建议使用数学计算，或者知道要返回的字段数.我不会知道此信息，因为单个成员最多可能有100个受抚养人(尽管更像是4-8)

The suggestion for Pivot Tables / Crosstabs has been mentioned below but every reference I am able to find suggests using mathematic calculations or that the number of fields to be returned is known. I will not know this information as a single member COULD have up to 100 dependents (although more like 4-8)

我觉得我正在接近最终解决方案.我在查询中添加了GROUP_CONCAT函数，该函数在单个列中返回所有名字，在单个列中返回所有姓氏，但是仍然需要将它们分解成自己的单独列.

I feel I am getting closer to the final solution. I added the function GROUP_CONCAT to my query which returns ALL firstnames in a single column and ALL last names in a single column but still need to break them out into their own individual columns.

新功能是:

SELECT
  t1.MemberID,
  t1.FirstName,
  t1.LastName,
  GROUP_CONCAT(t2.FirstName) AS Dep_Firstnames,
  GROUP_CONCAT(t2.LastName) AS Dep_LastNames
FROM
  member_information t1
  LEFT OUTER JOIN member_dependent_information t2
    ON t1.MemberID = t2.MemberID
WHERE
  t1.Status = 1
GROUP BY
  t1.MemberID

推荐答案

有时候，解决问题的第一步就是知道它的名字.在那之后，这只是谷歌搜索的问题.您尝试创建的内容称为数据透视表或交叉表报表.这是一个链接，说明如何在MySQL中创建数据透视表. 教程.

Sometimes the first step to solving your problem is knowing what it's called. After that, it's simply a matter of googling. What you are trying to create is called a pivot table or crosstab report. Here is a link explaining how to create pivot tables in MySQL. And here is a more in depth tutorial.

现在您已经更新了问题，对于您要完成的工作，我有一个更清晰的了解.我将为您提供一个替代解决方案，该解决方案与基于MySQL的

Now that you've updated the question, I have a clearer idea of what you are trying to accomplish. I'll give you an alternative solution which is similar but not exactly what you want based on MySQL's GROUP_CONCAT function.

select t1.FirstName, t1.LastName, group_concat(concat(t2.FirstName, ' ', t2.LastName))
from member_information as t1
left outer join member_dependent_information as t2 on t2.MemberID=t1.MemberID
group by t1.MemberID;

我已经按照以下方式验证了此查询.首先进行设置:

I've verified this query as follows. First the setup:

create table member_information (
    MemberID int unsigned auto_increment primary key,
    FirstName varchar(32) not null,
    LastName varchar(32) not null
) engine=innodb;

create table member_dependent_information (
    MemberID int unsigned not null,
    FirstName varchar(32) not null,
    LastName varchar(32) not null,
    Type int unsigned not null,
    foreign key (MemberID) references member_information(MemberID)
) engine=innodb;

insert into member_information (MemberID, FirstName, LastName) values
(1, 'John', 'Harris'),
(2, 'Sarah', 'Thompson'),
(3, 'Zack', 'Lewis');

insert into member_dependent_information (MemberID, FirstName, LastName, `Type`) values
(1, 'Amy', 'Harris', 1),
(2, 'Bryan', 'Thompson', 1),
(2, 'Dewey', 'Thompson', 2),
(2, 'Tom', 'Thompson', 2),
(3, 'Harry', 'Lewis', 2),
(3, 'Minka', 'Lewis', 1);

现在查询和结果:

mysql> select t1.FirstName, t1.LastName, group_concat(concat(t2.FirstName, ' ', t2.LastName))from member_information as t1
    -> left outer join member_dependent_information as t2 on t2.MemberID=t1.MemberID
    -> group by t1.MemberID;
+-----------+----------+------------------------------------------------------+
| FirstName | LastName | group_concat(concat(t2.FirstName, ' ', t2.LastName)) |
+-----------+----------+------------------------------------------------------+
| John      | Harris   | Amy Harris                                           | 
| Sarah     | Thompson | Bryan Thompson,Dewey Thompson,Tom Thompson           | 
| Zack      | Lewis    | Harry Lewis,Minka Lewis                              | 
+-----------+----------+------------------------------------------------------+
3 rows in set (0.00 sec)

这篇关于MySQL查询可以将行变成列吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！