使用 SQL 查询查找订购了 > x 种产品的客户的详细信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7322322/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using SQL query to find details of customers who ordered > x types of products
提问by Andy
Please note that I have seen a similar query here, but think my query is different enough to merit a separate question.
请注意，我在这里看到了一个类似的查询，但认为我的查询足够不同，值得单独提出一个问题。
Suppose that there is a database with the following tables:
假设有一个包含以下表的数据库：
- customer_table with customer_ID (key field), customer_name
- orders_table with order_ID (key field), customer_ID, product_ID
- customer_table 与 customer_ID（关键字段），customer_name
- 带有 order_ID（关键字段）、customer_ID、product_ID 的 orders_table
Now suppose I would like to find the names of all the customers who have ordered more than 10 different types of product, and the number of types of products they ordered. Multiple orders of the same product does not count.
现在假设我想查找订购了 10 种以上不同类型产品的所有客户的姓名，以及他们订购的产品类型的数量。同一产品的多个订单不计算在内。
I think the query below should work, but have the following questions:
我认为下面的查询应该有效，但有以下问题：
- Is the use of count(distinct xxx) generally allowed with a "group by" statement?
- Is the method I use the standard way? Does anybody have any better ideas (e.g. without involving temporary tables)?
- “group by”语句通常允许使用 count(distinct xxx) 吗？
- 我使用的方法是标准方法吗？有没有人有更好的想法（例如，不涉及临时表）？
Below is my query
下面是我的查询
select T1.customer_name, T1.customer_ID, T2.number_of_products_ordered
from customer_table T1
inner join
(
select cust.customer_ID as customer_identity, count(distinct ord.product_ID) as number_of_products_ordered
from customer_table cust
inner join order_table ord on cust.customer_ID=ord.customer_ID
group by ord.customer_ID, ord.product_ID
having count(distinct ord.product_ID) > 10
) T2
on T1.customer_ID=T2.customer_identity
order by T2.number_of_products_ordered, T1.customer_name
回答by Andrei
Isn't that what you are looking for? Seems to be a little bit simpler. Tested it on SQL Server - works fine.
这不是你要找的吗？好像稍微简单了点。在 SQL Server 上对其进行了测试 - 工作正常。
SELECT customer_name, COUNT(DISTINCT product_ID) as products_count FROM customer_table
INNER JOIN orders_table ON customer_table.customer_ID = orders_table.customer_ID
GROUP BY customer_table.customer_ID, customer_name
HAVING COUNT(DISTINCT product_ID) > 10
回答by Gus Melo
You could do it more simply:
你可以更简单地做到这一点：
select
c.id,
c.cname,
count(distinct o.pid) as `uniques`
from o join c
on c.id = o.cid
group by c.id
having `uniques` > 10
