如何用单条MySQL查询对比用户各稀有度物品拥有量与总量

可以通过子查询关联或窗口函数两种方式，将用户持有量统计与稀有度全量统计合并为单条查询，避免多次查询拼接的效率损耗：

方法1：子查询关联 #

先通过子查询预计算各稀有度的总物品数，再与用户持有统计的结果关联，一次查询输出所需数据：

SELECT 
    u.user_owned,
    c.rarity,
    COUNT(c.rarity) AS user_rarity_count,
    r.total_rarity_count
FROM tb_items c
INNER JOIN tb_inventories u ON u.item_name = c.item_name
INNER JOIN (
    SELECT rarity, COUNT(*) AS total_rarity_count
    FROM tb_items
    GROUP BY rarity
) r ON c.rarity = r.rarity
GROUP BY u.user_owned, c.rarity, r.total_rarity_count;

方法2：窗口函数（更简洁） #

利用MySQL的窗口函数，在统计用户持有量的同时直接计算对应稀有度的全量总数，无需额外子查询关联：

SELECT 
    u.user_owned,
    c.rarity,
    COUNT(c.rarity) AS user_rarity_count,
    COUNT(*) OVER (PARTITION BY c.rarity) AS total_rarity_count
FROM tb_items c
INNER JOIN tb_inventories u ON u.item_name = c.item_name
GROUP BY u.user_owned, c.rarity;

性能优化提示 #

为提升大数据量下的查询效率，建议创建以下索引：

• tb_items：INDEX idx_item_rarity (item_name, rarity)
• tb_inventories：INDEX idx_user_item (user_owned, item_name)

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