mysql - 如何在GROUP_CONCAT中实现SQL查询，以'，'分隔并以'＆'结尾(How can I acheive SQL query in GROUP_CONCAT to seperate with ',' and ends with '&')

Question

Im trying to acheive and SQL statement to query to GROUP_CONCAT all the classes which separates with commas if more than 2 classes and also ends with '&' for the last class and If no comma if only 1 class.

(我试图让SQL语句查询GROUP_CONCAT的所有类，如果有两个以上类，则用逗号分隔，最后一个类也以“＆”结尾，如果只有一个类，则以逗号分隔。)

SELECT `tbl_perfumes`.`pk_int_perfumeID` AS `Perfume ID`,
GROUP_CONCAT(DISTINCT `tbl_notes`.`txt_noteName`  ORDER BY `tbl_notes`.`txt_noteName` ASC SEPARATOR ', 
' ) AS `Notes` FROM `tbl_notes` JOIN `tbl_perfumeNotes`
ON `tbl_notes`.`pk_int_noteID` = `tbl_perfumeNotes`.`fk_int_noteID`
JOIN `tbl_perfumes` ON `tbl_perfumeNotes`.`fk_int_perfumeID` = `tbl_perfumes`.`pk_int_perfumeID`
WHERE `tbl_perfumes`.`pk_int_perfumeID`=1
GROUP BY `tbl_perfumes`.`pk_int_perfumeID`

ie; (1)
    (1 & 2)
    (1, 2, 3 & 4)

  ask by Amir Hussain translate from so

Answer 1

If you want a mysql solution you could use a proper replace

(如果您想要mysql解决方案，则可以使用适当的替换)

select t.`Perfume ID`,   
    replace( t.`Notes` , concat(', ' , 
      SUBSTRING_INDEX( t.`Notes` ,',',-1) 
    ) , 
    concat('& ' , 
      SUBSTRING_INDEX( t.`Notes` ,', ',-1)
    ))
from ( 

SELECT `tbl_perfumes`.`pk_int_perfumeID` AS `Perfume ID`,
      GROUP_CONCAT(DISTINCT `tbl_notes`.`txt_noteName`  
          ORDER BY `tbl_notes`.`txt_noteName` ASC SEPARATOR ', ' ) AS `Notes` 
FROM `tbl_notes` JOIN `tbl_perfumeNotes`
    ON `tbl_notes`.`pk_int_noteID` = `tbl_perfumeNotes`.`fk_int_noteID`
JOIN `tbl_perfumes` ON `tbl_perfumeNotes`.`fk_int_perfumeID` = `tbl_perfumes`.`pk_int_perfumeID`
WHERE `tbl_perfumes`.`pk_int_perfumeID`=1
GROUP BY `tbl_perfumes`.`pk_int_perfumeID`
) t