无法解决此SQL查询 [英] Can't solve this SQL query

问题描述

我很难处理SQL查询。我使用PostgreSQL。

I have a difficulty dealing with a SQL query. I use PostgreSQL.

查询说：向至少显示一个订单的客户显示包含3个不同类别的产品。结果将是2列 CustomerID 和订单数量。我已经编写了这段代码，但我认为这是不正确的。

The query says: Show the customers that have done at least an order that contains products from 3 different categories. The result will be 2 columns, CustomerID, and the amount of orders. I have written this code but I don't think it's correct.

select SalesOrderHeader.CustomerID,
       count(SalesOrderHeader.SalesOrderID) AS amount_of_orders
from SalesOrderHeader 
inner join SalesOrderDetail on
       (SalesOrderHeader.SalesOrderID=SalesOrderDetail.SalesOrderID)
inner join Product on 
       (SalesOrderDetail.ProductID=Product.ProductID)

where SalesOrderDetail.SalesOrderDetailID in 
       (select DISTINCT count(ProductCategoryID)
        from Product
        group by ProductCategoryID
        having count(DISTINCT ProductCategoryID)>=3)

group by SalesOrderHeader.CustomerID;

以下是查询所需的数据库表：

Here are the database tables needed for the query:

推荐答案

尝试一下：

select CustomerID,count(*) as amount_of_order from 
  SalesOrder join 
 (
   select SalesOrderID,count(distinct ProductCategoryID) CategoryCount 
     from SalesOrderDetail JOIN Product using (ProductId)
  group by 1
 ) CatCount using (SalesOrderId) 
 group by 1 
 having bool_or(CategoryCount>=3) -- At least on CategoryCount>=3

这篇关于无法解决此SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！