(請參閱我在本文底部測試的數據庫結構.)

我執行這個查詢：

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);

總結

以上是生活随笔為你收集整理的多表连接去重复 mysql_连接多个表时避免重复条目(MySQL)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。