在连接多个表时避免重复条目(MySQL)

Question

(请参阅本文底部我测试的数据库结构。)

我执行这个查询：

SELECT m.title, GROUP_CONCAT(DISTINCT(d.name) SEPARATOR ',') d FROM movies m
INNER JOIN movies_seen s
    ON s.object_id = m.id
LEFT JOIN movies_directors_connections dc
    ON dc.movie_id = m.id
LEFT JOIN movies_directors d
    ON d.id = dc.director_id

结果如下：

title        | d 
Pulp Fiction | Quentin Tarantino,George Butler,Robert Fiore

但我想要得到的是：

title        | d 
Pulp Fiction | Quentin Tarantino
Pumping Iron | George Butler,Robert Fiore

有什么建议？:)

CREATE TABLE `movies` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(90) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 ;

CREATE TABLE `movies_seen` (
`object_id` int(10) NOT NULL DEFAULT '0',
`date` varchar(10) NOT NULL DEFAULT '0');

CREATE TABLE `movies_directors` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 ;

CREATE TABLE IF NOT EXISTS `movies_directors_connections` (
  `movie_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `director_id` mediumint(8) unsigned NOT NULL DEFAULT '0'
) ENGINE=MyISAM;

然后是一些测试数据：

INSERT INTO `movies` (`id`, `title`) VALUES
(1, 'Pulp Fiction'), (2, 'Pumping Iron');

INSERT INTO `movies_seen` (`object_id`, `date`) VALUES
  (1, 1359511222), (2, 1359511223);

INSERT INTO `movies_directors` (`id`, `name`) VALUES
(1, 'Quentin Tarantino'),
(2, 'George Butler'),
(3, 'Robert Fiore');

INSERT INTO `movies_directors_connections` (`movie_id`, `director_id`) VALUES
(1, 1), (2, 2), (2, 3);

Answer 1

您只需要添加GROUP BY子句

SELECT  m.title, 
        GROUP_CONCAT(DISTINCT(d.name) SEPARATOR ',') d 
FROM    movies m
        INNER JOIN movies_seen s
          ON s.object_id = m.id
        LEFT JOIN movies_directors_connections dc
          ON dc.movie_id = m.id
        LEFT JOIN movies_directors d
          ON d.id = dc.director_id
GROUP   BY m.title

• SQLFiddle Demo

其他链接

• MySQL clause