代码之家 › 专栏 › 技术社区 › changelog

同一表上有2个左联接的MySQL

left-join mysql sql

changelog · 技术社区 · 16 年前

我正在尝试运行此查询:

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 和一个 Audio 文件从 mm_files 表。

如果我有一篇有声音和视频的文章,MySQL会返回4行:

带声音(视频为空)
带视频(声音为空)
所有空值
声音和视频

我如何才能“强制”它只返回一行与任何现有视频和音频相关的文章?我在这里做错什么了?

3 回复 | 直到 16 年前

Tomalak 16 年前

我认为你想要这样的东西:

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 AudioRel  ON Rel.noticia_id = Noticia.id
                                               AND AudioRel.file_id IN (
    SELECT file_id 
    FROM   ntNoticias_mmFiles 
    WHERE  noticia_id = Noticia.id AND IsAudioFile = 1 /* whatever the check is */
    LIMIT  1
  )
  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 ntNoticias_mmFiles AS VideoRel  ON VideoRel.noticia_id = Noticia.id
                                               AND VideoRel.file_id IN (
    SELECT file_id 
    FROM   ntNoticias_mmFiles 
    WHERE  noticia_id = Noticia.id AND IsVideoFile = 1  /* whatever the check is */
    LIMIT  1
  )
  LEFT JOIN mm_files     AS VideoFile       ON VideoFile.id = Rel.file_id
                                               AND VideoFile.IsVideoFile = 1
  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()   
ORDER BY 
  Destaque.destaque

我的想法是:

您最多需要一个音频文件和一个视频文件。每个 Noticia ,因此您需要确保每个类型最多有一个文件进入联接。这也意味着你必须加入 ntNoticias_mmFiles 表两次-每种类型一次。

这就是联接条件中的子查询应该做的:为每个文件类型选择一行。从那里开始,你离开了,加入剩下的数据,就像你已经做的那样。

flytzen 16 年前

连接将返回所有组合,这就是问题所在。
如果每篇文章只有一个音频和/或视频文件,那么您可能需要查看子选择。在SQL Server中,这看起来像(未测试的代码):

SELECT title, 
       (select TOP 1 audio from audio where audio.aid = articles.id) as Audio, 
       (select TOP 1 video from video where video.aid = articles.id) as Video
FROM articles

请注意,对于大型数据集,这可能会执行得很差,因为本例中的子选择是针对返回到外部查询的每一行单独执行的。例如,如果返回10000个项目,那么实际上将在服务器上执行总共20001个查询。还有其他可能的答案可以克服这个问题,但它们会更加复杂(我怀疑您可以对派生表做些什么,但目前我还无法理解)。

Paul Sonier 16 年前

您可能希望将该联接查询优化到视图中。这是一个很大的查询,有了这么多的连接,效率会非常低。此外,视图还可以帮助您调试联接,并通过允许您分别编写联接(在视图中)和WHERE子句(在视图中选择)来简化联接,这有助于调试查询。