SQL复杂选择 - 故障形成查询
问题描述:
我有三个表,客户,销售和产品。SQL复杂选择 - 故障形成查询
Sales将CustomerID与ProductID链接起来并具有SalesPrice。
select Products.Category, AVG(SalePrice) from Sales
inner join Products on Products.ProductID = Sales.ProductID
group by Products.Category
这让我看到所有类别销售的平均价格。但是,我只想包括在数据库中有超过3个销售记录或更多的客户。
我不知道最好的方式,或任何方式去做这件事。想法?
答
您没有提到的客户数据的任何地方,所以我会假设它是在销售表
您需要过滤和第一限制销售表为客户提供更多的3个销售,然后再加入得到的产品类别,并获得跨类
select
Products.Category, AVG(SalePrice)
from
(SELECT ProductID, SalePrice FROM Sales GROUP BY CustomerID HAVING COUNT(*) > 3) S
inner join
Products on Products.ProductID = S.ProductID
group by
Products.Category
答
我会尝试以下方法:
select Products.Category, AVG(SalePrice) from Sales s
inner join Products on Products.ProductID = s.ProductID
where
(Select Count(*) From Sales Where CustomerID = s.CustomerID) > 3
group by Products.Category
答
我想创建的“大客户ID”的伪表一中选择,然后将其加入到您的查询限制结果:
SELECT Products.Category, AVG(SalePrice) FROM Sales
INNER JOIN Products ON Products.ProductID = Sales.ProductID
INNER JOIN (
SELECT CustomerID FROM Sales WHERE COUNT(CustomerID) >= 3 GROUP BY CustomerID
) BigCustomer ON Sales.CustomerID = BigCustomer.CustomerID
GROUP BY Products.Category
懒得虽然测试了这一点,所以让我知道,如果它的工作原理; O)
答
平均的另一种方式
;WITH FilteredSales AS
(
SELECT Products.Category, Sales.SalesPrice, COUNT(Sales.CustomerId) OVER(PARTITION BY Sales.CustomerId) AS SaleCount
FROM Sales
INNER JOIN Products ON Products.ProductID = Sales.ProductID
)
select Category, AVG(SalePrice)
from FilteredSales
WHERE SaleCount > 3
group by Category
什么数据库引擎和版本? – gbn 2010-04-29 05:27:37