当我在一些表之间进行内部联接时，我遇到了一些查询抛出重复的问题。

本质上，我加入了一些标签，以及基于 videoId 的 channel (想想流派)，这样一个特定的视频文件就可以有多个标签并成为多个 channel 的一部分。

不幸的是，出现了多个匹配 :( 我的直觉是查询与每个连接的 videoId 匹配并吐出双倍(正如您将在链接的 fiddle 中看到的，但考虑到我的 SQL Join 总计经验来自大约 3 小时前，到目前为止，我已经设法应付 OK，但现在可以听取一些专家的建议，从这里开始。

下面是我一直在研究的模式和查询，这里是一个 sqlfiddle链接，

架构:

create table videos 
(
   videoId int(1) AUTO_INCREMENT,
   videoUUID char(40),
   contentFolder varchar(50), 
   title varchar(50), 
   caption varchar(50),
   duration varchar(15),
   date TIMESTAMP,
   url varchar(55),
   text varchar(250), 
   PRIMARY KEY (videoId)
);

create table tags 
( 
   tagId int(1) AUTO_INCREMENT,
   tagName varchar(15),
   PRIMARY KEY (tagId)
);

create table channels
(
   channelId int(1) AUTO_INCREMENT,
   channelName varchar(15),
   PRIMARY KEY (channelId)
);

create table videoTags 
(
   videoTagId int(1) AUTO_INCREMENT,
   videoId int(1),
   tagId int(1),
   PRIMARY KEY (videoTagId)
);

create table videoChannels 
(
    videoChannelId int(1) AUTO_INCREMENT,
    videoId int (1),
    channelId int (1),
    PRIMARY KEY (videoChannelId)
 );

//
create trigger tuuid before insert on videos
for each row begin
set new.videoUUID = uuid();
end//

insert into videos 
  (contentFolder, title,caption,duration,url,text)
values ("someDir/","A Movie Title", "Headline for Movie",
    "00:05:11",
    "http://someserver.ip/somedir/test.mp4",
    "Some text as part of the video file description here");

insert into tags (tagName) values
('Flowers'),('Dogs'),('Cats'),('YaMum'),('orlyowl');

insert into channels (channelName) values
('General'), ('NotSoGeneral'), ('Specific'), ('Broad'), ('Narrow'),
('Obsolete'); 

insert into videoTags (videoId,tagId) values
(1,2),(1,5);

insert into videoChannels (videoId,channelId) values
(1,1),(1,4),(1,6);

查询:

select distinct v.*,group_concat(t.tagName)Tags,
group_concat(c.channelName)Channels
from videos as v 

inner join videoTags as vt on v.videoId = vt.videoid

inner join tags as t on t.tagId = vt.tagId

inner join videoChannels as vc on v.videoId = vc.videoId

inner join channels as c on c.channelId = vc.channelId

group by v.videoId;

我的做法是否正确？我构造查询的方式是否存在根本错误？还是架构对于我要完成的任务是错误的。

如有任何帮助，我们将不胜感激!

@J Lo 感谢您的回答!

答案: DISTINCT 关键字，我在 select 语句中尝试过，但不确定它的用法，所以没有完全理解它可以在哪里使用，@J Lo 如此简洁地指出，我可以在我的 group_concat 中使用它，从而给出查询:

select v.*,group_concat(distinct t.tagName)Tags,
group_concat(distinct c.channelName)Channels
from videos as v 

inner join videoTags as vt on v.videoId = vt.videoid

inner join tags as t on t.tagId = vt.tagId

inner join videoChannels as vc on v.videoId = vc.videoId

inner join channels as c on c.channelId = vc.channelId

group by v.videoId;

最佳答案

您的第一对连接 (Video/VideoTags/Tags) 会生成如下表格:

VideoID = 1 will bring in TagID = 2,5 (Dogs, orlyowl) so you have this

| 1 | Dogs
| 1 | orlyowl

当您加入 VideoChannels 时，它会为每个 channel 复制上述条目

| 1 | Dogs    | 1
| 1 | orlyowl | 1
| 1 | Dogs    | 4
| 1 | orlyowl | 4
| 1 | Dogs    | 6
| 1 | orlyowl | 6

group_concat 有一个 DISTINCT 属性

select v.*
  , group_concat(distinct t.tagName) Tags
  , group_concat(distinct c.channelName) Channels
from videos as v 
inner join videoTags as vt on v.videoId = vt.videoid
inner join tags as t on t.tagId = vt.tagId
inner join videoChannels as vc on v.videoId = vc.videoId
inner join channels as c on c.channelId = vc.channelId
group by v.videoId;

关于mysql - SQL多表连接 throw 欺骗，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/19353473/