MYSQL - 查询获取不同的发送者ID和接收者ID以及richesta_id

Question

我有这个表“消息”结果:

我尝试过:

SELECT idutente, richiesta_id
FROM (
  SELECT sender_id, richiesta_id
  FROM messages
  UNION
  SELECT receiver_id, richiesta_id
  FROM messages
) AS DistinctCodes (idutente)
WHERE idutente IS NOT NULL;

但不起作用或不正确。我怎样才能从你看到的 table 上得到表格为:

|    richiesta_id     |                users                |
|---------------------|-------------------------------------|
| 55                  | 2, 3, 4                             |
| other richiesta_id  | other users list separated by comma |

Answer 1

我认为您需要“GROUP BY”和“GROUP_CONCAT”的组合来存档此内容。

SELECT DistinctCodes.richiesta_id, GROUP_CONCAT(DISTINCT DistinctCodes.uid ORDER BY DistinctCodes.uid ASC)
FROM (
  SELECT sender_id AS uid, richiesta_id
  FROM messages
  UNION
  SELECT receiver_id AS uid, richiesta_id
  FROM messages
) AS DistinctCodes
GROUP BY DistinctCodes.richiesta_id