mysql - 将 GROUP_CONCAT mysql 中的最后一个逗号替换为 AND

Question

我想从 MySQL 表中检索数据并将其格式化为“Bruno、Phil Sturgeon 和 Simon”。具体来说，我想用逗号连接所有值，除了最后一个，我想用“和”连接。

现在我只能得到所有用逗号连接的值。如何将最后一个实例替换为“and”？

这是我的查询:

SELECT
   b.title,
   GROUP_CONCAT(a.name ORDER BY name SEPARATOR ',') AS name                   
FROM `book` AS b
LEFT JOIN `book_authors` AS ba
  ON b.id = ba.book_id
INNER JOIN `author` AS a
  ON a.id = ba.author_id
GROUP BY b.id
ORDER BY b.title

Answer 1

将值取到最后一个逗号，CONCAT 你的'AND'然后添加最后一个值

SELECT
   b.title,
  CONCAT(SUBSTRING_INDEX( GROUP_CONCAT(a.name ORDER BY name SEPARATOR ','),',',length( GROUP_CONCAT(a.name ORDER BY name SEPARATOR ','))-length(replace( GROUP_CONCAT(a.name ORDER BY name SEPARATOR ','),',',''))),
              ' AND ',SUBSTRING_INDEX( GROUP_CONCAT(a.name ORDER BY name SEPARATOR ','),',',-1)) AS name                   
FROM `book` AS b
LEFT JOIN `book_authors` AS ba
  ON b.id = ba.book_id
INNER JOIN `author` AS a
  ON a.id = ba.author_id
GROUP BY b.id
ORDER BY b.title

Here它是硬编码的