mysql - 统计有多少客户在 MySQL 中只有 1 个订单

Question

我正在使用 MySQL 数据库服务器。我的查询是:

Count how many customers that they just have 1 order and how many customers that they have more than 1 orders.

这是我的 SQL 查询:

SELECT 
    COUNT((SELECT 
            customer_code
        FROM
            customer AS c
                LEFT JOIN
            order_info AS oi ON (c.customer_code = oi.customer_code)
        GROUP BY customer_code
        HAVING COUNT(id_order) = 1)) AS New_customers

我怎样才能得到这个结果。

Answer 1

您在计数之前按 customer_code 进行分组，我认为这会将行分组在一起，从而有效地从结果集中删除所有订单。 HAVING 将始终使用结果集作为“数据源”。

SELECT 
    COUNT(DISTINCT customer_code)
FROM
    customer AS c
LEFT JOIN order_info AS oi ON (c.customer_code = oi.customer_code)

HAVING COUNT(id_order) = 1

或者更简单(但可能不是更有效)

SELECT 
    COUNT(customer_code)
FROM
    customer AS c
WHERE (
    SELECT 
        COUNT(*) 
    FROM `order_info` 
    WHERE 
        `customer_code` = `c`.`customer_code`
) = 1

要获取拥有多个订单的客户数量，只需将 = 更改为 > (在适当的情况下)。