mysql - SQL选择: How to multiply a column with the count of an ID

Question

我有以下(这里是简化的)数据库，并且想要获得收入最高的月份。

发票
- id
- order_id
- 发布(时间戳)

订单
- id

订单商品
- id
- order_id
- 文章_id

文章
- id
- 价格

到目前为止我得到了以下声明:

Select articles.price * orderItems.order_id as revenue, Extract(month 
from invoices.issued) 
FROM orderItems
INNER JOIN articles ON orderItems.article_id = articles.id
Inner JOIN orders ON orderItems.order_id = orders.id
Inner JOIN invoices ON orders.id = invoices.order_id
GROUP BY year(issued), month(issued)
Order by revenue DESC Limit 1

计算的收入是错误的，因为价格与 order_id 相乘，但实际上应该与相应 order_id 的计数相乘。我尝试实现 count(orderItems.order_id) 但它不起作用。有任何想法吗？谢谢!

Answer 1

我想你想要:

SELECT year(i.issued), month(i.issued), SUM(a.price) as revenue, 
FROM orderItems oi JOIN
     articles a
     ON oi.article_id = a.id JOIN
     orders o
     ON oi.order_id = o.id JOIN
     invoices i
     ON o.id = i.order_id
GROUP BY year(i.issued), month(i.issued)
ORDER BY revenue DESC
LIMIT 1;

换句话说，这是一个简单的聚合查询。永远不需要乘以 orderid。另请注意，此查询引入了表别名，因此查询更易于编写和阅读。