SQL 复杂选择 - 形成查询时遇到问题

发布于 2024-08-30 12:27:03 字数 349 浏览 3 评论 0原文

我有三个表：客户、销售和产品。

Sales 将 CustomerID 与 ProductID 链接起来并具有 SalesPrice。

select Products.Category, AVG(SalePrice) from Sales 
inner join Products on Products.ProductID = Sales.ProductID
group by Products.Category

这让我可以看到按类别划分的所有销售的平均价格。但是，我只想包含数据库中拥有 3 条以上销售记录或更多记录的客户。

我不确定最好的方法或任何方法来解决这个问题。有想法吗？

原文

I have three tables, Customers, Sales and Products.

Sales links a CustomerID with a ProductID and has a SalesPrice.

select Products.Category, AVG(SalePrice) from Sales 
inner join Products on Products.ProductID = Sales.ProductID
group by Products.Category

This lets me see the average price for all sales by category. However, I only want to include customers that have more than 3 sales records or more in the DB.

I am not sure the best way, or any way, to go about this. Ideas?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

雨后彩虹 2024-09-06 12:27:03

您没有在任何地方提到客户数据，所以我假设它在销售表中

您需要首先过滤销售表并将其限制为具有超过 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

You haven't mentioned the customer data anywhere so I'll assume it's in the Sales table

You need to filter and restrict the Sales table first to the customers with more the 3 sales, then join to get product category and get the average across categories

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

回复收藏 0 原文

嘿咻 2024-09-06 12:27:03

我会尝试以下操作：

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

I'd try the following:

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

回复收藏 0 原文

憧憬巴黎街头的黎明 2024-09-06 12:27:03

我会使用选择创建一个“大客户 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)

I'd create a pseudo-table of "big customer IDs" with a select, and then join it to your query to limit the results:

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

Too lazy to test this out though, so let me know if it works ;o)

回复收藏 0 原文

混吃等死 2024-09-06 12:27:03

另一种方式

;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

Another way

;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

回复收藏 0 原文

~没有更多了~