表格
表格和一些虚拟数据来说明这个问题。
成员
存储有关成员的基本信息。
------------------------------------
| member_id | email |
------------------------------------
| 1 | 1@a.com |
------------------------------------
| 2 | 2@a.com |
------------------------------------
| 3 | 3@a.com |
------------------------------------
| 4 | 4@a.com |
------------------------------------
| 5 | 4@a.com |
------------------------------------
member_data
为每个成员存储一些额外的元数据
----------------------------------------
| 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 |
------------------------------------
牌照
成员必须拥有许可证才能访问某个类别。
-----------------------------------------------------------------------
| 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_data和类别。许可证必须存在并且对于某个类别有效，成员才能返回该许可证的数据。
此外，我希望返回的每个许可证都作为一行返回，其中包含以下格式所需的所有数据：
输出格式
我想输出持有有效许可证的成员，如果他们没有某个类别的许可证，但确实持有另一个类别的许可证，则返回他们的到期日期或无输出。 即：
----------------------------------------------------------------------------------
| 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 的记录中看到的那样，问题是显示为三个不同的行。我希望按照上面显示的输出格式将三行中的每一行都作为一行返回。
我将不胜感激任何关于如何实现这一目标的想法。谢谢。
使用聚合：
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;