SQL产品/产品销售

Question

一个常见的(我假设是？)查询类型：

我有两个表('products'，'productsales')。“productsales”中的每个记录代表一笔销售(因此它有“date”、“quantity”、“product_id”)。如何高效地执行如下查询：

检索日期Y和日期Z之间销售超过X次的所有产品的产品名称。

(X是售出数量，而不是交易数量)

Answer 1

 SELECT p.[name]
 FROM products p
 WHERE p.product_id in (SELECT s.product_id
     FROM productsales s
     WHERE s.[date] between @dateStart and @dateEnd
     GROUP BY s.product_id
     HAVING Sum(s.quantity) > @X )

Answer 2

以上查询并不完全正确...

SELECT Name FROM Products
WHERE ProductId IN
( SELECT ProductId 
  FROM ProductSales
  WHERE ProductSales.Date BETWEEN  Y AND Z 
  GROUP BY ProductId
  HAVING SUM(ProductSales.Qty) > x
)

Answer 3

实际上，我认为你应该学习group by statement

最简单的语句是：

SELECT ps.product_id, COUNT(*) 
           FROM product_sales ps 
           WHERE ps.product_id = id AND ps.Date BETWEEN @Y AND @Z
           GROUP_BY product_id;