如何在Cards表中查询拥有最多过期卡片的cid？

现有数据表Cards，列定义如下：

• cid：客户ID
• status：卡片状态，取值为exp（过期）或vld（有效）
• card_id：卡片ID

下面给出不同数据库环境下的SQL实现：

针对MySQL/MariaDB #

SELECT cid, COUNT(*) AS exp_card_count
FROM Cards
WHERE status = 'exp'
GROUP BY cid
ORDER BY exp_card_count DESC
LIMIT 1;

注：这个写法会先筛选所有过期卡片，按客户ID分组统计数量，再按数量从多到少排序，最后取第一条。如果有多个客户过期卡片数并列最多，只会返回其中一个。

针对支持窗口函数的数据库（PostgreSQL、SQL Server、Oracle等） #

WITH exp_card_stats AS (
    SELECT 
        cid,
        COUNT(*) AS exp_card_count,
        RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
    FROM Cards
    WHERE status = 'exp'
    GROUP BY cid
)
SELECT cid, exp_card_count
FROM exp_card_stats
WHERE rnk = 1;

注：用CTE先统计每个客户的过期卡片数并排名，RANK()会让并列第一的客户都拿到相同排名，最终能返回所有过期卡片数量最多的客户，不会漏掉并列情况。

额外说明 #

• 如果要包含没有过期卡片的客户（统计数为0），可以改用COUNT(CASE WHEN status='exp' THEN 1 END)来统计，不过一般“最多过期卡片”的需求默认只关注有过期卡片的客户。
• 如果status字段值存在大小写不一致的情况，可以改成LOWER(status) = 'exp'来兼容。

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