我想帮助解决下面的问题。
postp在第一行,为什么不¤Postproc1。分组描述、符号、标题和帖子。我创建了查询,但是其中一个分组没有按我想要的方式显示。
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|
+---+---------+
解决方案:
GROUP_CONCAT(p.name SEPARATOR '¤') AS postp,
改变GROUP_CONCAT(CONCAT(IFNULL(p.name,'')) SEPARATOR '¤') AS postp,