php - 我们如何获取重复日期的价格(总和)，但在 mysql 中也需要？

Question

我需要使用 chart.js 绘制图形。在 x 轴上，我需要显示 day。在左侧的 y 轴中，我需要显示 price，在右侧的 y 轴中，我需要显示 id。目前我的问题在 x 轴上重复了一天。请检查下面提到的查询和结果。

查询

SELECT od.id, sum(op.price), od.status, DATE_FORMAT(od.created_at, '%d %m') AS day 
FROM order_positions op 
INNER JOIN orders od ON od.id = op.order_id 
WHERE od.status = 'finished' 
GROUP BY op.order_id 
ORDER BY od.created_at LIMIT 20 

数据这是我得到的结果

id  sum(op.price)   status      day     
7   180.00         finished     09 06
17  70.00          finished     09 06
19  48.00          finished     09 06
20  20.00          finished     13 06
21  31.00          finished     13 06
22  20.00          finished     15 06
23  20.00          finished     15 06

表格是

order_positions

id  order_id price 
  1    7       100
  2    7        80
  3    17       35
  4    17       35

订单

id    status     date
7    finished    09 06
17   finished    09 06
19   finished    09 06
20   finished    13 06

Answer 1

您将要按 od.created_at 而不是 op.order_ID 对其进行分组>

SELECT od.id, 
   Sum(op.price), 
   od.status, 
   Date_format(od.created_at, '%d %m') AS day 
FROM   order_positions op 
   INNER JOIN orders od 
           ON od.id = op.order_id 
WHERE  od.status = 'finished' 
GROUP  BY od.created_at 
ORDER  BY od.created_at 
LIMIT  20