时间戳

首页 > 解决方案 > SQL分组更多表

mysql - SQL分组更多表

问题描述

我想帮助解决以下问题。
postp 在第一行，为什么不 ¤Postproc1。分组描述、签名、comp 和 postp。我创建了查询，但其中一个分组没有按照我想要的方式显示。

SELECT 
GROUP_CONCAT(cl.description SEPARATOR '¤') AS description, 
GROUP_CONCAT(t.name SEPARATOR '¤') AS sign,
GROUP_CONCAT(c.name SEPARATOR '¤') AS comp,
GROUP_CONCAT(p.name SEPARATOR '¤') AS postp,
v.version, 
v.date
FROM changelog cl
INNER JOIN versions v ON cl.version = v.id
INNER JOIN cl_type_conn clt ON clt.changelog = cl.id
LEFT JOIN cl_comp_conn cc ON cc.changelog = cl.id
LEFT JOIN cl_postproc_conn cp ON cp.changelog = cl.id
INNER JOIN types t ON t.id = clt.type
LEFT JOIN companies c ON c.id = cc.company
LEFT JOIN postprocessors p ON p.id = cp.postprocessor
GROUP BY cl.version

结果：

+------------+-----------+-------------------+---------+-------+----------+
|description |       sign|               comp|    postp|version|date      |
+------------+-----------+-------------------+---------+-------+----------+
|Desc 1¤Desc2|Type3¤Type1|Company 2¤Company 1|Postproc1|  ver 1|1631484000|
|      Desc 3|      Type4|               NULL|     NULL|  ver 2|1631570400|
+------------+-----------+-------------------+---------+-------+----------+

表：

变更日志

+--+-----------+-------+
|id|description|version|
+--+-----------+-------+
| 1|     desc 1|     10|
| 2|     desc 2|     10|
| 3|     desc 3|     20|
+--+-----------+-------+

版本

+--+-----------+----------+
|id|version    |date      |
+--+-----------+----------+
| 1|      ver 1|1631484000|
| 2|      ver 2|1631570400|
+--+-----------+----------+

cl_type_conn

+--+---------+----+
|id|changelog|type|
+--+---------+----+
| 1|        1|   1|
| 2|        2|   3|
| 3|        3|   4|
+--+---------+----+

cl_comp_conn

+--+---------+-------+
|id|changelog|company|
+--+---------+-------+
| 1|        1|    435|
| 2|        2|     23|
+--+---------+-------+

cl_postproc_conn

+--+---------+-------------+
|id|changelog|postprocessor|
+--+---------+-------------+
| 1|        2|            1|
+--+---------+-------------+

公司

+---+---------+
|id |name     |
+---+---------+
|435|Company 1|
| 23|Company 2|
+---+---------+

后处理器

+---+---------+
|id |name     |
+---+---------+
|  1|PostProc1|
+---+---------+

类型

+---+---------+
|id |name     |
+---+---------+
|  1|   Type 1|
|  2|   Type 2|
|  3|   Type 3|
|  4|   Type 4|
+---+---------+

标签： mysqlsqldatabase

解决方案

解决方案：

GROUP_CONCAT(p.name SEPARATOR '¤') AS postp,

改变

GROUP_CONCAT(CONCAT(IFNULL(p.name,'')) SEPARATOR '¤') AS postp,

推荐阅读