按月加入 3 个表组

Question

我是Mysql的新手。我想制作一个基于数据库的图表。我有 3 个表：发票、购买和月份。我想加入所有这些并按月对他们进行分组。 我已经加入了2：发票和月份和购买和月份。成功了。但是当我尝试加入其中的 3 个时，出现了错误。 下面是我的代码。

function get_chart(){
$query = $this->db->query("SELECT month.month_name as month, 
SUM(table_purchase.subtotal) AS total1,
SUM(table_invoice.subtotal) AS total2  
FROM month 
LEFT JOIN table_purchase ON (month.month_num = MONTH(table_purchase.date_pur)  
LEFT JOIN table_invoice ON (month.month_num = MONTH(table_invoice.date_inv)
GROUP BY month.month_name ORDER BY month.month_num");
$res = array();
foreach($query->result_array() as $data){
$res[] = array(
"month" => $data['month'],
"total1" => $data['total1'],
"total2" => $data['total2'],
);
}
return $res;
}

错误： 您的 SQL 语法有误;检查与您的MySQL服务器版本相对应的手册，了解在第6行的"左联接table_invoice(month.month_num = MONTH(table_invoice.date_inv("附近使用的正确语法

Answer 1

查看您的代码，您在 ON 子句的开头有一个错误的(.. 尝试删除

$query = $this->db->query("SELECT month.month_name as month, 
SUM(table_purchase.subtotal) AS total1,
SUM(table_invoice.subtotal) AS total2  
FROM month 
LEFT JOIN table_purchase ON month.month_num = MONTH(table_purchase.date_pur)
LEFT JOIN table_invoice ON month.month_num = MONTH(table_invoice.date_inv)
GROUP BY month.month_name 
ORDER BY month.month_num");