mysql - 处理多个 "duplicate rows"的 SQL 查询

Question

我有两个表:用户用户许可证。它们的结构如下:

+------------+-----------+----------+
|             users table           |
+------------+-----------+----------+
| identifier | firstname | lastname |
+------------+-----------+----------+
|        122 | John      | Doe      |
|        123 | Johnny    | Dough    |
|        124 | Martin    | Pie      |
+------------+-----------+----------+

+------------+-----------+------------+
|         user_licenses table         |
+----+-------------+-------+----------+
| id |    type     | owner | warnings |
+----+-------------+-------+----------+
|  1 | drive       |   122 |        2 |
|  2 | drive_bike  |   122 |        2 |
|  3 | drive_truck |   123 |        3 |
|  4 | drive_bike  |   124 |        2 |
|  5 | drive       |   123 |        2 |
|  6 | drive_truck |   124 |        3 |
+----+-------------+-------+----------+

问题是，我想创建一个输出以下内容的结果:

identifier, firstname, lastname, typesOfLicenses(Commaseperated?), HowManyWarningsForEachLicense(Commaseperated?)

我不知道该怎么做:我试过在这样的查询中加入两者:

SELECT users.identifier, users.firstname, users.lastname, user_licenses.type, user_licenses.warnings FROM users INNER JOIN user_licenses ON users.identifier=user_licenses.owner;

但这会为每个人及其许可证创建多个结果。我想要每个人一个结果，然后他们的许可证合并到类型行中(可能用逗号分隔)。我该怎么做？

Answer 1

您可以将 group_concat 函数与 group by 一起使用。

试试这个...

SELECT 
    users.identifier, 
    users.firstname, 
    users.lastname, 
    GROUP_CONCAT(CONCAT(user_licenses.type, ' - ', user_licenses.warnings) SEPARATOR ', ')
FROM users 
INNER JOIN user_licenses ON users.identifier=user_licenses.owner;
GROUP BY
    users.identifier, users.firstname, users.lastname