MySQL 在同一个表上有 2 个 LEFT JOIN
我正在尝试运行此查询:
SELECT
Destaque.destaque, Noticia.id, Noticia.antetitulo,
Noticia.titulo, Noticia.lead, Noticia.legenda,
Noticia.publicacao, Seccao.descricao, Album.pasta,
Foto.ficheiro, Foto.descricao, Cronista.nome,
Cronista.profissao, Cronista.ficheiro,
AudioFile.*, AudioCollection.*, VideoFile.*, VideoCollection.*
FROM
nt_highlights AS Destaque
LEFT JOIN nt_noticias AS Noticia ON Destaque.noticia_id = Noticia.id
LEFT JOIN mm_fotos AS Foto ON Noticia.foto_id = Foto.id
LEFT JOIN nt_temas AS Seccao ON Noticia.tema_id = Seccao.id
LEFT JOIN mm_albuns AS Album ON Foto.album_id = Album.id
LEFT JOIN nt_cronistas AS Cronista ON Cronista.id = Noticia.cronista_id
LEFT JOIN ntNoticias_mmFiles AS Rel ON Rel.noticia_id = Noticia.id
LEFT JOIN mm_files AS AudioFile ON AudioFile.id = Rel.file_id
LEFT JOIN mm_coleccoes AS AudioCollection ON AudioFile.coleccao_id = AudioCollection.id
LEFT JOIN mm_files AS VideoFile ON VideoFile.id = Rel.file_id
LEFT JOIN mm_coleccoes AS VideoCollection ON VideoFile.coleccao_id = VideoCollection.id
WHERE
Destaque.area_id = 1
AND Noticia.paraPublicacao = 1
AND Noticia.publicacao <= NOW()
AND (AudioFile.mimeType != '' OR AudioFile.id IS NULL)
AND (VideoFile.mimeType = '' OR VideoFile.id IS NULL)
ORDER BY
Destaque.destaque
这将为我提供一些文章(来自 nt_noticias
),其想法是同时获取 Video
和 < mm_files
表中的 code>Audio 文件。
发生的情况是,当我有一篇带有声音和视频的文章时,MySQL 将返回 4 行:
- 带有声音(视频为空)
- 带有视频(声音为空)
- 带有所有空值
- 带有声音和视频
我怎样才能“强制”它在每篇文章中只返回一行与任何现有的视频和音频相关的内容? 我在这里做错了什么?
I'm trying to run this query:
SELECT
Destaque.destaque, Noticia.id, Noticia.antetitulo,
Noticia.titulo, Noticia.lead, Noticia.legenda,
Noticia.publicacao, Seccao.descricao, Album.pasta,
Foto.ficheiro, Foto.descricao, Cronista.nome,
Cronista.profissao, Cronista.ficheiro,
AudioFile.*, AudioCollection.*, VideoFile.*, VideoCollection.*
FROM
nt_highlights AS Destaque
LEFT JOIN nt_noticias AS Noticia ON Destaque.noticia_id = Noticia.id
LEFT JOIN mm_fotos AS Foto ON Noticia.foto_id = Foto.id
LEFT JOIN nt_temas AS Seccao ON Noticia.tema_id = Seccao.id
LEFT JOIN mm_albuns AS Album ON Foto.album_id = Album.id
LEFT JOIN nt_cronistas AS Cronista ON Cronista.id = Noticia.cronista_id
LEFT JOIN ntNoticias_mmFiles AS Rel ON Rel.noticia_id = Noticia.id
LEFT JOIN mm_files AS AudioFile ON AudioFile.id = Rel.file_id
LEFT JOIN mm_coleccoes AS AudioCollection ON AudioFile.coleccao_id = AudioCollection.id
LEFT JOIN mm_files AS VideoFile ON VideoFile.id = Rel.file_id
LEFT JOIN mm_coleccoes AS VideoCollection ON VideoFile.coleccao_id = VideoCollection.id
WHERE
Destaque.area_id = 1
AND Noticia.paraPublicacao = 1
AND Noticia.publicacao <= NOW()
AND (AudioFile.mimeType != '' OR AudioFile.id IS NULL)
AND (VideoFile.mimeType = '' OR VideoFile.id IS NULL)
ORDER BY
Destaque.destaque
This will get me a number of articles (from nt_noticias
) and the idea is to get at the same time a Video
and an Audio
file from the mm_files
table.
What happens is that when I have an article with a sound and a video, MySQL will return 4 rows:
- with the sound (video is null)
- with the video (sound is null)
- with all nulls
- with the sound AND the video
How can I "force" it to return just one row per article with any existing video AND audio associated? What am I doing wrong here?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
我认为您想要这样的东西:
我的想法是这样的:
您最多需要一个音频文件和一个视频文件。 每个
Noticia
有多个可用文件,因此您需要确保每种类型最多有一个文件进入连接。 这也意味着您必须加入ntNoticias_mmFiles
表两次 - 每种类型一次。这就是连接条件中的子查询应该执行的操作:每种文件类型选择一行。 从那里继续,您将其余数据保留在其中,就像您已经做的那样。
I think you want something like this:
My thought was this:
You want one audio file and one video file, at most. There are several files available per
Noticia
, so you need to make sure that a maximum of one file per type gets into the join. This also means you have to join in thentNoticias_mmFiles
table twice — once per type.This is what the sub-queries in the join conditions are supposed to do: Select one row per file type. Going on from there you LEFT JOIN the rest of the data in, just like you already do.
JOIN 将返回所有组合,这就是问题所在。
如果每篇文章只有一个音频和/或视频文件,那么您可能需要查看子选择。
在 SQL Server 中,这看起来像(未经测试的代码):
请注意,在大型数据集上,这可能会表现不佳,因为此示例中的子选择是针对返回到外部查询的每一行单独执行的。 例如,如果您返回 10,000 篇文章,那么服务器上实际上将执行总共 20,001 个查询。
还有其他可能的答案来克服这个问题,但它们涉及更多(我怀疑您可以使用派生表做一些事情,但目前我无法做到)。
The JOIN will return all the combinations, that's the problem.
If you only have one audio and/or videofile per article then you might want to look at subselects.
In SQL Server this would look something like (untested code):
Be careful that on large datasets this can perform poorly as the subselects in this example are executed individually for each row returned to the outer query. For example, if you return 10,000 articles then a total of 20,001 queries would actually be executed on the server.
There are other possible answers to overcome this but they get more involved (I suspect you could do something with a derived table but it eludes me at the moment).
您可能希望将该连接查询优化到视图中。 这是一个很大的查询,并且有这么多连接,效率会非常低。 另外,视图可以帮助您调试连接,并且允许您分别编写连接(在视图中)和 WHERE 子句(在从视图中选择),从而基本上简化了操作,这有助于调试查询。
You probably want to optimize that join query into a view. It's a large query, and with that many joins, it's going to be pretty inefficient. Plus, a view helps you debug the joins and will basically simplifies by allowing you to write your joins (in the view) and the WHERE clause (in your select from the view) separately, which can help with debugging the queries.