SQL需求：统计所有客户购买次数（含未购买客户，按次数降序）

表结构 #

• customer (id, name, email)
• product (id, product_category, material, price, purchase_id)
• purchase (id, purchase_date, customer_id)

任务要求 #

展示客户姓名及其购买次数（列名需为purchase_count），按购买次数降序排序，购买次数最多的客户排在最前。部分客户可能未产生任何购买，此时需显示购买次数为0，最终要创建包含name, purchase_count的视图。

现有错误代码 #

WITH CLIENTS_BUYS AS
(
    SELECT -- (NULL FOR THE CLIENTS WHO HAVE NOT MADE PURCHASE SHOW AS '0' HAVE NOT BEEN SHOWN IN THIS QUERY)
        CU.name, -- SO I TRIED TO USE 'CTE'
        CU.id,
        COUNT(CASE WHEN PU.id IS NOT NULL THEN PU.id ELSE CU.id END) AS PURCHASE_COUNT
    FROM 
        purchase PU
    JOIN 
        customer CU ON CU.id = PU.customer_id
    JOIN 
        product PR ON PR.purchase_id = PU.id
    WHERE 
        customer_id IS NOT NULL 
        OR customer_id IS NULL
    GROUP BY 
        CU.name, CU.id
),
CLIENTS_NOT_BUYS AS 
(
    SELECT 
        CU.name,
        CU.id,
        COUNT(CASE WHEN PU.id IS NULL THEN PU.id ELSE CU.id END) AS PURCHASE_COUNT
    FROM 
        purchase PU 
    JOIN 
        customer CU ON CU.id = PU.customer_id
    JOIN 
        product PR ON PR.purchase_id = PU.id
    WHERE 
        CU.id IN (SELECT customer_id 
                  WHERE CU.id != PU.customer_id)
    GROUP BY 
        CU.name, CU.id
)
SELECT
    name,
    PURCHASE_COUNT
FROM 
    CLIENTS_NOT_BUYS
JOIN 
    CLIENTS_BUYS ON CLIENTS_BUYS.id = CLIENTS_NOT_BUYS.id
GROUP BY 
    name
ORDER BY 
    PURCHASE_COUNT DESC

遇到的问题 #

• product表中purchase_id为NULL的客户无法通过现有JOIN语句展示；
• 使用CTE的方式无效，尝试LEFT JOIN也未成功；
• 最后连接CLIENTS_NOT_BUYS和CLIENTS_BUYS时出现错误：Errors near name and PURCHASE_COUNT Ambiguous column name；
• 现有CLIENTS_BUYS的查询结果仅包含有购买记录的客户，无法展示无购买记录的客户（需显示purchase_count为0）：

name            id  PURCHASE_COUNT
-----------------------------------
Alba Gomez       5      1
Amira Palmer     3      2
Anna Smith       7      2
Charlee Freeman  1      5
Christina Rivas  2      1
Michael Doe      6      2

原代码核心问题 #

• 使用内连接（JOIN）会自动过滤掉无匹配记录的客户，导致无购买记录的客户无法被查询到；
• 两个CTE逻辑混乱，CLIENTS_NOT_BUYS的WHERE子句写法错误，且最后JOIN两个CTE会引发列名冲突；
• 错误关联product表统计购买次数：购买次数应基于订单数（purchase表记录），而非商品数，一个订单对应多个商品时会重复统计。

正确SQL实现（按订单数统计购买次数） #

CREATE VIEW customer_purchase_counts AS
SELECT
    c.name,
    COUNT(p.id) AS purchase_count
FROM
    customer c
LEFT JOIN purchase p ON c.id = p.customer_id
GROUP BY
    c.id, c.name
ORDER BY
    purchase_count DESC;

代码说明 #

1. 从客户表出发：用LEFT JOIN关联purchase表，确保所有客户都被保留，无购买记录的客户对应p.id为NULL；
2. 统计逻辑：COUNT(p.id)会忽略NULL值，无购买记录的客户统计结果自动为0；
3. 分组规则：必须包含c.id（避免重名客户被合并）和c.name，符合SQL分组要求；
4. 创建视图：直接通过CREATE VIEW生成目标视图，无需复杂CTE；
5. 排序：按purchase_count降序排列，满足购买次数多的客户在前的要求。

扩展：按商品数量统计购买次数 #

如果业务要求一个订单买多个商品算多次购买，需关联product表，代码调整为：

CREATE VIEW customer_purchase_counts AS
SELECT
    c.name,
    COUNT(pr.id) AS purchase_count
FROM
    customer c
LEFT JOIN purchase p ON c.id = p.customer_id
LEFT JOIN product pr ON p.id = pr.purchase_id
GROUP BY
    c.id, c.name
ORDER BY
    purchase_count DESC;

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