php - 用于显示多对多数据库结果的 MySQL 语法

Question

我创建了一个这样的多对多数据库:

CREATE TABLE Films (  
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),    
Title VARCHAR(255));

CREATE TABLE Ambiences (  
id INT NOT NULL AUTO_INCREMENT,   
PRIMARY KEY(id),  
ambienceName VARCHAR(255));

CREATE TABLE Films_Ambiences (
film_id INT NOT NULL,  
ambience_id INT NOT NULL,  
PRIMARY KEY (film_id, ambience_id),  
FOREIGN KEY (film_id) REFERENCES Films(id) ON UPDATE CASCADE,  
FOREIGN KEY (ambience_id) REFERENCES Ambiences(id) ON UPDATE CASCADE);

现在:我已经插入了一些数据并加入了表格以获得:

╔════════╦════╦══════════╗  
║ Title  ║ id ║ Ambience ║  
╠════════╬════╬══════════╣  
║ film_1 ║  1 ║ Funny    ║
║ film_1 ║  1 ║ Sad      ║
║ film_2 ║  2 ║ Funny    ║
║ film_2 ║  2 ║ Sad      ║
╚════════╩════╩══════════╝  

但是，我的目标是在第一行显示 film_1，在第二行显示 film_2，如下所示:

╔════════╦════╦══════════╗  
║ Title  ║ id ║ Ambience ║  
╠════════╬════╬══════════╣  
║ film_1 ║  1 ║ Funny,Sad║
║ film_2 ║  2 ║ Funny,Sad║
╚════════╩════╩══════════╝  

我正在尝试使用 GROUP_CONCAT 但我得到:

╔════════╦════╦════════════════════════╗
║ Title  ║ id ║        Ambience        ║
╠════════╬════╬════════════════════════╣
║ film_1 ║  1 ║ funny, funny, sad, sad ║
╚════════╩════╩════════════════════════╝ 

它将所有行合并为一个行。这是一个 SQLFiddle 为此。

Answer 1

您需要添加GROUP BY:

SELECT title,
       GROUP_CONCAT(ambienceName SEPARATOR ' ') AS ambiences
FROM   Films AS f 
   INNER JOIN Films_Ambiences as fa
         ON f.id = fa.film_id           
   INNER JOIN Ambiences AS a
         ON a.id = fa.ambience_id
GROUP  BY title

Updated fiddle

结果是:

TITLE   AMBIENCES
film1   sad happy
film2   sad happy