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

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

  •  3
  • 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 年前
        1
  •  1
  •   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 表两次-每种类型一次。

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

        2
  •  1
  •   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个查询。 还有其他可能的答案可以克服这个问题,但它们会更加复杂(我怀疑您可以对派生表做些什么,但目前我还无法理解)。

        3
  •  0
  •   Paul Sonier    16 年前

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