使用 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:

1. customer_table with customer_ID(关键字段)，customer_name
2. 带有 order_ID(关键字段)、customer_ID、product_ID 的orders_table

现在假设我想查找订购了 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:

1. 通常允许在group by"语句中使用 count(distinct xxx) 吗?
2. 我使用的方法是标准方法吗?有没有人有更好的想法(例如，不涉及临时表)?

下面是我的查询

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

推荐答案

这不是您要找的吗?好像稍微简单了点.在 SQL Server 上测试过 - 工作正常.

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屋！