使用SQL查询查找订购了> x类型的产品 [英] Using SQL query to find details of customers who ordered > x types of products
问题描述
请注意，我已经看到类似的查询这里，但是我认为我的查询是足够不同，值得一个单独的问题。
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（键字段），customer_name
- orders_table with order_ID ，customer_ID，product_ID
现在假设我想找到所有订购了超过10种不同类型产品的客户的姓名，以及他们订购的产品类型的数量。同一产品的多个订单不计算。
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.
我认为下面的查询应该可以工作，但有以下问题：
I think the query below should work, but have the following questions:
- 是否使用count（distinct xxx）一般允许使用group by语句？
- 是否使用标准方法？是否有任何人有更好的想法（例如没有涉及临时表）？
非常感谢。
Andy
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
推荐答案
这不是你想要的？似乎有点简单。
Isn't that what you are looking for? Seems to be a little bit simpler. Tested it on SQL Server - works fine.
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
这篇关于使用SQL查询查找订购了> x类型的产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
