使用SQL查找超过X个订单的客户总数 [英] Using SQL to find the total number of customers with over X orders

问题描述

我一直在用有限的SQL知识来绞尽脑汁，同时尝试提出查询以对订单数据库运行统计信息.

I've been roasting my brain with my limited SQL knowledge while attempting to come up with a query to run a statistic on my orders database.

表ORDERS的布局如下:

Table ORDERS is laid out like this:

CustomerID ProductID (etc)
1              10
1              10
1              11
2              10
4              9

每次购买都记录有客户ID和产品ID-同一位客户可能有多条记录，甚至同一客户和产品也可能有多条记录.

Each purchase is recorded with the customer id and the product ID - there CAN be multiple records for the same customer, and even multiple records with the same customer and product.

我需要提出一个查询，该查询可以返回在X个和X个不同的产品之间购买的客户数量-例如，3个客户购买的商品少于5个不同的产品，10个产品从5-10个不同的产品中购买，1个购买了超过10种不同的产品.

I need to come up with a query that can return the amount of customers who bought between X and X distinct products - for example, 3 customers bought less then 5 different products, 10 bought from 5-10 different products, 1 bought over 10 different products.

我很确定这与派生表有关，但是高级SQL对我来说是一种新手.任何帮助将不胜感激！

I'm pretty sure this has something to do with derived tables, but advanced SQL is a new fairly craft to me. Any help would be appreciated!

推荐答案

尝试一下:

SELECT T1.products_bought, COUNT(T2.cnt) AS total
FROM (
    SELECT '<5' AS products_bought, 0 AS a, 4 AS b
    UNION ALL
    SELECT '5-10', 5, 10
    UNION ALL
    SELECT '>10', 11, 999999
) T1
LEFT JOIN
(
    SELECT COUNT(DISTINCT ProductID) AS cnt
    FROM ORDERS
    GROUP BY CustomerID
) T2
ON T2.cnt BETWEEN T1.a AND T1.b
GROUP BY a, b

结果:

products_bought  total
<5               3    
5-10             0    
>10              0    

这篇关于使用SQL查找超过X个订单的客户总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！