主页 / dba / 问题 / 305858
Accepted
Krateng
Asked: 2022-01-10 13:14:57 +0800 CST

如何在另一个表中找到具有完全匹配关系的行

  • 772

这里是 SQL 初学者，请多多包涵。

假设我有一个songs看起来像这样的简化表

ID 标题
1 恶棍
2 更多的
3 更多（拉兹洛混音）

我也有一张桌子songartists：

歌曲编号 艺术家
1 K/DA
2 K/DA
2 塞拉芬
3 K/DA
3 塞拉芬
3 拉斯洛

假设我要查找 和 的歌曲K/DA，Seraphine这意味着没有只有两位艺术家中的一位的歌曲，也没有包含这两位艺术家以及其他艺术家的歌曲。

这是一些在技术上可以满足我要求的代码，但速度很慢：

SELECT title FROM songs WHERE
    EXISTS (SELECT * FROM songartists WHERE songartists.song_id == songs.id and songartists.artist == "K/DA")
    AND
    EXISTS (SELECT * FROM songartists WHERE songartists.song_id == songs.id and songartists.artist == "Seraphine")
    AND
    NOT EXISTS (SELECT * FROM songartists WHERE songartists.song_id == songs.id AND songartists.artist NOT IN ("K/DA","Seraphine"))

显然，我也可以对标题进行非常简单的查找，然后对艺术家进行比较并以编程方式进行比较。但是有没有一个高性能的 SQL 解决方案呢？

sqlite aggregate

2 个回答

  1. 这是一个非常经典的无余数关系除法问题。

    我建议您首先将输入列表放入一个临时表中，以便您可以以基于集合的方式引用它，尽管您也可以将其作为 CTE 执行。您可能还想索引它。

    CREATE TEMPORARY TABLE input AS
SELECT NULL AS artist WHERE 0
UNION ALL
SELECT *
FROM (VALUES
  ('K/DA'),
  ('Seraphine')
) v;

    然后有一些关系划分的解决方案。一个标准的一个是这个

    SELECT
  s.id,
  s.title
FROM songs s
JOIN songartists sa ON sa.song_id = s.id
LEFT JOIN input i ON i.artist = sa.artist
GROUP BY
  s.id,
  s.title
HAVING COUNT(*) = COUNT(i.artist)  -- no missing matches
   AND COUNT(*) = (SELECT COUNT(*) FROM input);  -- no extras

    db<>小提琴

    这是做什么的：

    • 把所有song的 s 和他们songartist的 s
    • 左加入输入列表
    • 分组song
    • 确保行数（艺术家）等于匹配的数量input（没有不匹配的行）
    • 并且总行数也等于总行数input（没有多余的行）
    • 2
  2. Best Answer

    您可以通过为每个所需的艺术家添加一个内部联接和另一个联接来排除除 2 个所需值之外的任何其他值来做到这一点。这第三个将是一个左连接，并且需要一个期望歌曲 id 为空的 where 条件（我假设歌曲表没有空 id）：

    select s.*
from songs as s
inner join songartists as sa_k on sa_k.song_id = s.id and sa_k.artist = 'K/DA'
inner join songartists as sa_s on sa_s.song_id = s.id and sa_s.artist = 'Seraphine'
left join songartists as sa_others on sa_others.song_id = s.id and sa_others.artist not in ('K/DA', 'Seraphine')
where sa_others.song_id is null

    编辑：我看到你刚刚编辑了你的帖子。这个建议的解决方案总是提供在songartists 和songartists.title 上存在适当的索引将是最佳的，只需要3 个表搜索songartists 和1 个搜索歌曲。

    • 1

相关问题