MySQL 合并多行并维护列名

Question

表格

用于说明问题的表格和一些虚拟数据。

成员

存储成员的基本信息。

------------------------------------
| member_id  | email               |
------------------------------------
| 1          | 1@a.com             |
------------------------------------
| 2          | 2@a.com             |
------------------------------------
| 3          | 3@a.com             |
------------------------------------
| 4          | 4@a.com             |
------------------------------------
| 5          | 4@a.com             |
------------------------------------

成员数据

为每个成员存储一些额外的元数据

----------------------------------------
| member_id | name | surname | company |
----------------------------------------
|  1        | A    |    A    |   A     |
----------------------------------------
|  2        | B    |    B    |   B     |
----------------------------------------
|  3        | C    |    C    |   C     |
----------------------------------------
|  4        | D    |    D    |   D     |
----------------------------------------
|  5        | E    |    E    |   E     |
----------------------------------------

类别

系统内的不同类别。

------------------------------------
| cat_id | cat_name                |
------------------------------------
|   1    |  Cars                   |
------------------------------------
|   2    |  Bikes                  |
------------------------------------
|   3    |  Boats                  |
------------------------------------

许可证

成员(member)必须拥有许可才能访问类别。

-----------------------------------------------------------------------
| id  |  subid | catid | start_date  | end_date    | description      |
-----------------------------------------------------------------------
|  1  |   1    |   1   | 2014-01-01  | 2020-12-31  | Premium          |
-----------------------------------------------------------------------
|  2  |   1    |   2   | 2014-01-01  | 2015-12-31  | Premium          |
-----------------------------------------------------------------------
|  3  |   1    |   3   | 2014-01-01  | 2018-12-31  | Premium          |
-----------------------------------------------------------------------
|  4  |   2    |   1   | 2014-01-01  | 2016-12-31  | Premium          | 
-----------------------------------------------------------------------
|  7  |   3    |   1   | 2014-01-01  | 2014-01-02  | Premium          | 
-----------------------------------------------------------------------
|  8  |   3    |   2   | 2014-01-01  | 2014-01-02  | Premium          | 
-----------------------------------------------------------------------
|  9  |   3    |   3   | 2014-01-01  | 2020-01-31  | Premium          | 
-----------------------------------------------------------------------
|  10 |   5    |   1   | 2014-01-01  | 2014-01-02  | Premium          | 
-----------------------------------------------------------------------
|  11 |   5    |   2   | 2014-01-01  | 2014-01-02  | Premium          | 
-----------------------------------------------------------------------
|  12 |   5    |   3   | 2014-01-01  | 2014-01-02  | Premium          | 
-----------------------------------------------------------------------

关于数据

成员 1 拥有类别 1,2 和 3 的许可证。它们都是有效的。成员 2 仅拥有类别 1 的许可。它是活跃的。成员 3 拥有 1,2 和 3 的许可。只有类别 3 的许可有效。成员 4 没有执照。成员 5 拥有类别 1、2 和 3 的许可证，但它们都已过期。

我想要达到的目标

我想获取每个成员(member)执照的记录，以及他们各自的成员(member)数据和类别。许可证必须存在并且对类别有效，成员才能为该许可证返回数据。

此外，我希望返回的每个许可证都作为一行返回，其中包含以下格式所需的所有数据:

输出格式

我想输出持有有效许可证的成员，并返回他们在某个类别的到期日期，或者如果他们没有某个类别的许可证但持有另一个类别的许可证，则不输出。即:

----------------------------------------------------------------------------------
| Company | Name | LicenceType | Cars         | Bikes          | Boats           |
----------------------------------------------------------------------------------
|   A     |A A   |  Premium    |2020-12-31    | 2015-12-31     |  2018-12-21     |
----------------------------------------------------------------------------------
|   B     |B B   |  Premium    |2016-12-31    |                |                 |
----------------------------------------------------------------------------------
|   C     |C C   |  Premium    |              |                |  2020-01-31     |
----------------------------------------------------------------------------------

我尝试过的

SELECT
    md.company as Company,  
CONCAT(md.name,' ', md.surname) as Name,
    l.description as LicenceType,
    (CASE WHEN (c.cat_name='Cars') THEN l.end_date ELSE '' END)AS Cars,
    (CASE WHEN (c.cat_name='Bikes') THEN l.end_date ELSE '' END)AS Bikes,
    (CASE WHEN (c.cat_name='Boats') THEN l.end_date ELSE '' END)AS Boats
FROM 
    licences as l
JOIN
    categories as c ON c.cat_id=l.catid
JOIN    
    member_data as md ON md.member_id=l.subid
WHERE
    l.end_date>='2014-12-17'    
AND 
    (l.description='Premium')   
ORDER BY Company ASC    

当前输出

这是当前数据的显示方式:

----------------------------------------------------------------------------------
| Company | Name | LicenceType | Cars         | Bikes          | Boats           |
----------------------------------------------------------------------------------
|   A     |A A   |  Premium    |2020-12-31    |                |                 |
----------------------------------------------------------------------------------
|   A     |A A   |  Premium    |              | 2015-12-31     |                 |
----------------------------------------------------------------------------------
|   A     |A A   |  Premium    |              |                |  2018-12-21     |
----------------------------------------------------------------------------------
|   B     |B B   |  Premium    |2016-12-31    |                |                 |
----------------------------------------------------------------------------------
|   C     |C C   |  Premium    |              |                |  2020-01-31     |
----------------------------------------------------------------------------------

正如您在公司 A 的记录中看到的那样，问题是显示为三个不同的行。按照上面显示的输出格式，我希望将三行中的每一行都作为一行返回。

对于如何实现这一点的任何想法，我将不胜感激。谢谢。

Answer 1

使用聚合:

SELECT
    md.company as Company,  
    CONCAT(md.name,' ', md.surname) as Name,
    l.description as LicenceType,
    MAX(CASE WHEN (c.cat_name='Cars') THEN l.end_date ELSE '' END)AS Cars,
    MAX(CASE WHEN (c.cat_name='Bikes') THEN l.end_date ELSE '' END)AS Bikes,
    MAX(CASE WHEN (c.cat_name='Boats') THEN l.end_date ELSE '' END)AS Boats
FROM 
    licences as l
JOIN
    categories as c ON c.cat_id=l.catid
JOIN    
    member_data as md ON md.member_id=l.subid
WHERE
    l.end_date>='2014-12-17'    
AND 
    (l.description='Premium')   
GROUP BY Company, Name, l.description
ORDER BY Company ASC;