concat将一个SELECT分组到另一个SELECT中 - concat group a SELECT in another SELECT

concat group a SELECT in another SELECT

本文关键字：SELECT 另一个一个 concat | 更新日期: 2023-09-27

简单问题：

共有2张表：

流派[名称，歌曲ID]
歌曲[id，title，userID，status]

SELECT id，name，title，userID，status FROM songs INNER JOIN流派ON song。id=genre.songID ORDER BY id ASC；

从获得结果的查询是什么

+----+-------------+----------------------+--------+--------+
| id | genre.name  | song.title           | userID | status |
+----+-------------+----------------------+--------+--------+
|  1 | tech        | Feel it all          |      1 |      1 |
|  2 | tech        | Tester               |      1 |      1 |
|  3 | music       | Sejujurnya           |      1 |      1 |
|  4 | music       | Not Done             |      1 |      1 |
|  5 | life        | Cinta                |      1 |      1 |
|  6 | life        | Feel it all          |      1 |      1 |
|  7 | life        | Not Done             |      1 |      1 |
|  8 | truth       | Tester               |      1 |      1 |
|  9 | tree        | Tester               |      1 |      1 |
| 10 | climb       | Tester               |      1 |      1 |
+----+-------------+----------------------+--------+--------+

至

+----+-------------+---------------------------------+--------+--------+
| id | genre.name  | song.title                      | userID | status |
+----+-------------+---------------------------------+--------+--------+
|  1 | tech        | Feel it all,Tester              |      1 |      1 |
|  2 | music       | Sejujurnya, Not Done            |      1 |      1 |
|  3 | life        | Cinta, Feel it all, Note Done   |      1 |      1 |
|  4 | truth       | Tester                          |      1 |      1 |
|  5 | tree        | Tester                          |      1 |      1 |
|  6 | climb       | Tester                          |      1 |      1 |
+----+-------------+---------------------------------+--------+--------+

感谢

将GROUP_CONCT与GROUP BY 一起使用

SELECT 
   id,
   genre.name,
   GROUP_CONCAT(title) as title,
   userID,
   status 
FROM 
   songs 
INNER JOIN 
   genre 
ON 
   song.id=genre.songID 
GROUP BY 
   genre.name 
ORDER BY 
   id ASC

SELECT
    `Songs`.`id`,
    `Genre`.`Name`,
    GROUP_CONCAT(`Songs`.`Title`) as Title,
    `Songs`.`userID`,
    `Songs`.`status`
FROM
    `Genre`,
    `Songs`
WHERE
    `Genre`.`SongID` = `Songs`.`id`
GROUP BY
    `Genre`.`Name`