在两张表中匹配ID，找出用户拥有的完整图书收藏

表结构数据 #

table_book_user（用户图书关联表） #

book_id | user_id 
--------+---------
 b1     | u1      
 b3     | u1      
 b2     | u1      
 b4     | u2      
 b5     | u2      
 b5     | u3       

table_book_collection（图书收藏关联表） #

book_id | collection_id 
--------+--------------
b1      | c1      
b2      | c1      
b3      | c1      
b4      | c2      
b5      | c1      
b5      | c2      
b5      | c3 

需求说明 #

找出用户拥有的图书收藏，匹配条件为：用户必须拥有该收藏的全部图书。

示例分析 #

• u1缺少b5，无法拥有c1
• u2拥有c2和c3
• u3拥有c3

期望结果 #

user_id | collection_id 
--------+--------------
u2      | c2      
u2      | c3      
u3      | c3      

解决方案（SQL） #

WITH collection_book_count AS (
    SELECT collection_id, COUNT(DISTINCT book_id) AS total_books
    FROM table_book_collection
    GROUP BY collection_id
),
user_collection_book_count AS (
    SELECT 
        bu.user_id,
        bc.collection_id,
        COUNT(DISTINCT bu.book_id) AS owned_books
    FROM table_book_user bu
    JOIN table_book_collection bc ON bu.book_id = bc.book_id
    GROUP BY bu.user_id, bc.collection_id
)
SELECT ucc.user_id, ucc.collection_id
FROM user_collection_book_count ucc
JOIN collection_book_count cbc ON ucc.collection_id = cbc.collection_id
WHERE ucc.owned_books = cbc.total_books
ORDER BY ucc.user_id, ucc.collection_id;

逻辑说明 #

1. 统计收藏图书总数：通过collection_book_count计算每个收藏包含的唯一图书数量，避免同本图书多次关联导致计数错误。
2. 统计用户在收藏中的图书数：user_collection_book_count关联用户图书表和收藏表，统计每个用户在对应收藏下已拥有的图书数量。
3. 筛选完整匹配的收藏：关联两个统计结果，当用户拥有的图书数等于收藏总图书数时，说明该用户完全拥有这个收藏，最终输出符合条件的用户-收藏组合。

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