使用嵌套查询和内部联接清理 SQL 查询

2022-01-15 00:00:00 sql database mariadb mysql

一直试图通过我为自己开发的一些练习题重新介绍自己使用 SQL，但努力寻找解决以下问题的更好方法:

Been trying to reintroduce myself to SQL through some practice questions I've developed for myself, but struggling to find a better way of approaching the following problem:

播放列表

id           title
1            Title1
2            Title2

playlist_clips

id  playlist_id clip_id
1   Title1      3 
2   Title2      1

playlist_tags

playlist_id tag_id
1           1
1           2
2           2

Clips 和 Tags 是两个完全独立的表，我使用 playlist_tags 和 playlist_clips 进行连接将它们添加到 playlists 表中，以表示双向一对多关系.

Clips and Tags are two entirely separate tables, and I am using the playlist_tags and playlist_clips to connect them to the playlists table, to represent the two-way one-to-many relationships.

我想选择所有具有给定标题的播放列表，并在查询中提供所有标签(在本例中为 [1, 2])，而不仅仅是至少其中一个".

I wanted to select all the playlists that have a given title, and have ALL of the tags provided in the query (in this example [1, 2]), not just "at least one of them".

这是我想出的:

select p_clips.* from
    (
        select p.id, p.title, count(pc.id) as number_of_clips
        from playlists p
        left join playlist_clips pc on p.id = pc.playlist_id
        where p.title like "Test1"
        group by id
    ) as p_clips

    inner join

    (
        select *
        from playlists p
        left join playlist_tags pt on p.id = pt.playlist_id
        where pt.tag_id in (1, 2)
        group by id
        having count(*) = 2
    ) as p_tags

    on p_clips.id = p_tags.id

虽然从我的测试中我发现它可以工作，但它看起来并不是特别优雅，而且我还认为它在性能方面并不是非常有效.(我已经从本示例的代码中删除了不相关的参数，例如 select 参数.)

Whilst, from my testing I've found this to work, it doesn't look particularly elegant, and I also assume it's not terribly efficient performance-wise. (I've removed irrelevant parameters from the code for this example, such as select parameters.)

什么是更清洁的方法，或者至少是更优化的方法?

What would be a cleaner way of approaching this, or at the least, a more optimized approach?

预期结果:

id  title
260 Title1

对于我最初令人困惑的帖子，我深表歉意，我已尝试清理我的表格及其包含的信息.

I apologize for my initial confusing post, I've tried to clean up my tables and the information they contain.

推荐答案

我想选择所有具有给定标题的播放列表，并在查询中提供所有标签(在本例中为 [1, 2])，而不仅仅是至少其中一个".

I wanted to select all the playlists that have a given title, and have ALL of the tags provided in the query (in this example [1, 2]), not just "at least one of them".

您根本不需要剪辑表.您不需要 left joins 或子查询中的 playlists 表.

You don't need the clips table at all. You don't need left joins or the playlists table in the subquery.

这表明:

select p.*
from playlists p join
     (select pt.playlist_id
      from playlist_tags pt 
      where pt.tag_id in (1, 2)
      group by id
      having count(*) = 2
     ) pt
     on p.id = pt.playlist_id
where p.title like 'Test1';

你也可以不用子查询来表达这个:

You could phrase this without a subquery as well:

select p.*
from playlists p join
     playlist_tags pt 
     on p.id = pt.id
where p.title like 'Test1' and
      pt.tag_id in (1, 2)
group by p.id
having count(*) = 2

相关文章