mysql - 从日期字段中过滤每个月的sql数据

Question

我的表是:

customer(cid,name,city,state)
orders(oid,cid,date)
product(pid,productname,price)
lineitem(lid,pid,oid,number,totalprice)

我想创建一个 View ProductMonth (PID, ProductName, Month, QuantitySold, NumberCustomer, Revenue) 为每个产品和每个月提供销售数量、购买产品的不同客户数量和销售金额对应于月份。

我使用 extract() 从 orders.date 中获取月份。我需要知道如何循环每个月和每个产品。我想不出一个有效的查询。甚至可以在 MySQL 中做到这一点吗？我的意思是循环每个月的产品名称并计算总价以及每种产品的销售额对整体的贡献。我知道如何针对特定产品或月份实现此操作，但不适用于每个月销售的产品。我尝试使用连接但失败了。我需要查询以及实现它的逻辑。

Answer 1

这假设 lineitem.number 是售出的单位数。

SELECT  P.pid
,p.productname
,Month=datepart(mm, O.date)
,QuantitySold = sum(L.number)
,NumberCustomer = count(distinct cid)
,Revenue = sum(L.totalprice)
FROM orders O 
JOIN customer C on C.cid = O.cid
JOIN lineitem L on L.oid = O.oid
JOIN product P on P.pid = L.pid
GROUP BY
 P.pid
,p.productname
,Month=datepart(mm, O.date)