SQL产品/产品销售 [英] SQL products/productsales

问题描述

常见的查询类型(我假设吗?)

A common (i assume?) type of query:

我有两个表(产品"，产品销售"). "productsales"中的每个记录都代表一个销售(因此它具有"date"，"quantity"，"product_id").我如何有效地进行如下查询:

I have two tables ('products', 'productsales'). Each record in 'productsales' represents a sale (so it has 'date', 'quantity', 'product_id'). How do i efficiently make a query like the following:

检索在日期Y和日期Z之间售出X次以上的所有产品的产品名称.

Retrieve the product names of all products that were sold more than X times between date Y and date Z.

(X是已售数量而不是交易数量)

(X is the quantity sold not the number of transactions)

推荐答案

 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 )

这篇关于SQL产品/产品销售的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！