在代码点火器中使用组查询连接 2 个表

Question

INVOICES TABLE
id  |   date        | Customer
1   |   2018-01-01  |    a
2   |   2018-01-01  |    b
3   |   2018-01-02  |    c
4   |   2018-01-02  |    d
5   |   2018-01-02  |    e

INVOICES_ITEMS TABLE
id  |   invoice_id  |   name       | total
1   |       1       | Billing      |  1500
2   |       2       | Billing      |  400
3   |       2       | Reconnection |  100
4   |       3       | Installation |  1000
5   |       4       | Billing      |  900
6   |       4       | Penalty      |  500
7   |       5       | Reconnection |  100

我的数据库中invoice_items表来存储所有发票项目。 我的Invoice Table里有date column，invoice_item table里有name column。我想要的是将所有具有相同名称和日期的数据分组并获取它们的总数。我很难实现我想要的东西。任何帮助将不胜感激。谢谢

预期输出：

Date   | Billing | Reconnection | Installation | Penalty
2018-01-01 |  1900   |   100        |      0       |    0
2018-01-02 |  900    |   100        |      1000    |    500

当前代码：

$q = $this->db
->select ( "invoices.date, 
SUM(invoices_items.total) as Billing,
SUM(invoices_items.total) as Reconnection,
SUM(invoices_items.total) as Installation,
SUM(invoices_items.total) as Penalty,
->from("invoices")
->join("invoices_items", "invoices.id=invoices_items.invoice_id")
->group_by(array("date","name"))
->get();

当前代码输出：

Date   | Billing | Reconnection | Installation | Penalty
2018-01-01 |  1900   |   1900       |      1900    |    1900
2018-01-01 |  100    |   100        |      100     |    100
2018-01-02 |  900    |   900        |      900     |    900
2018-01-02 |  100    |   100        |      100     |    100
2018-01-02 |  1000   |   1000       |      1000    |    1000
2018-01-02 |  500    |   500        |      500     |    500

Answer 1

我认为你应该在分组中使用 date((。你可以试试这个。它可能会起作用

$this->db
->select ("i.date, 
SUM(it.total) as Billing,
SUM(it.total) as Reconnection,
SUM(it.total) as Installation,
SUM(it.total) as Penalty")
->from("invoices i")
->join("invoices_items it", "i.id=it.invoice_id")
->group_by(['date(i.date)', 'it.name'])
->get();

Answer 2

尝试使用 if 条件求和...

$q = $this->db
->select ( "invoices.date, 
SUM(IF(invoices_items.name='Billing',invoices_items.total, 0)) as Billing,
SUM(IF(invoices_items.name='Reconnection',invoices_items.total, 0)) as Reconnection,
SUM(IF(invoices_items.name='Installation',invoices_items.total, 0)) as Installation,
SUM(IF(invoices_items.name='Penalty',invoices_items.total, 0)) as Penalty")
->from("invoices")
->join("invoices_items", "invoices.id=invoices_items.invoice_id")
->group_by(array("invoices.date"))
->get();