php - 如何对两个表中的字段求和并按日期分组

Question

如何获取两个表中的SUM字段，我有两个表，我的表是laporan和laporan_pengeluaran。

表laporan

id  shell    date_created
9   12000    2013-07-01
10  24000    2013-07-01
11  5500     2013-07-02

表 laporan_pengeluaran

id  laporan_id  harga
1   9           15000
2   9           29000
3   10          7500
4   10          5000

我的问题是，如何通过连接表和分组按天获取 SUM，关系是 laporan.id 和 laporan_pengeluaran.laporan_id。所以我想得到如下结果:

c_date_created  c_shell   c_harga
2013-07-01       36000    44000
2013-07-02       5500     12500

当前我的查询在下面，但没有成功:-(，这导致 c_shell 困惑

SELECT 
    l.date_created as c_date_created
    SUM(l.shell) as c_shell,
    SUM(lp.harga) as c_harga,
    l.*
    FROM laporan l
    LEFT JOIN laporan_pengeluaran lp ON l.id=lp.laporan_id 
    WHERE l.date_created BETWEEN '2013-07-01' AND '2013-07-05'
    GROUP BY l.date_created
    ORDER BY l.date_created ASC

谢谢。

Answer 1

在加入之前，您需要将第二个表分组到子查询中，因为它分组在不同的列上。

SELECT l.date_created as c_date_created,
SUM(l.shell) as c_shell,
SUM(lp.c_harga) as c_harga,
l.*
FROM laporan l
LEFT JOIN (SELECT laporan_id,
                  SUM(harga) as c_harga
           FROM laporan_pengeluaran
           GROUP BY laporan_id) as lp
ON l.id = lp.laporan_id
WHERE l.date_created BETWEEN '2013-07-01' AND '2013-07-05'
GROUP BY l.date_created
ORDER BY l.date_created ASC