Python操作SQLite3多对多关系表实现关键词AND查询的方案咨询

完全可以通过单条SELECT语句实现该需求，以下是两种常用实现方案：

方案1：分组+HAVING统计（推荐，适合关键词数量多的场景） #

这个方案的核心逻辑是先筛选出所有匹配任意目标关键词的资产关联记录，再按资产ID分组，统计每个资产匹配到的不同关键词数量，数量等于目标关键词总数的就是同时满足所有条件的资产。
SQL语句如下：

SELECT a.asset_id
FROM assets a
JOIN assets_keywords ak ON a.asset_id = ak.asset_id
JOIN keywords k ON k.keyword_id = ak.keyword_id
WHERE k.keyword IN ('BIG', 'SCARY')
GROUP BY a.asset_id
HAVING COUNT(DISTINCT k.keyword) = 2;

说明：

• 这里的2对应你要匹配的关键词总数量，如果要同时匹配3个关键词就改成3
• 因为你的assets_keywords表主键是(asset_id, keyword_id)，不会有重复关联，keywords表的keyword也有唯一约束，所以也可以把COUNT(DISTINCT k.keyword)简化成COUNT(*)，性能更高

方案2：多次JOIN关联（适合关键词数量少的场景） #

如果只需要匹配2-3个关键词，也可以通过多次关联关键词表来实现，每个关键词对应一次关联：

SELECT DISTINCT a.asset_id
FROM assets a
-- 第一次关联匹配BIG关键词
JOIN assets_keywords ak1 ON a.asset_id = ak1.asset_id
JOIN keywords k1 ON k1.keyword_id = ak1.keyword_id AND k1.keyword = 'BIG'
-- 第二次关联匹配SCARY关键词
JOIN assets_keywords ak2 ON a.asset_id = ak2.asset_id
JOIN keywords k2 ON k2.keyword_id = ak2.keyword_id AND k2.keyword = 'SCARY';

说明：这种方案在关键词数量少的时候执行效率通常更高，因为不需要分组聚合操作，但如果要匹配的关键词超过3个，语句会变得冗长，维护成本更高。

内容的提问来源于stack exchange，提问作者bvz