SELECT
br.product_id,
d.date,
round(sum(b.repayment_amount) / 100, 2)
FROM
s_repayment_detail b,
s_date d
LEFT JOIN s_borrow_repayment_detail br ON br.borrow_id = b.borrow_id
WHERE
br.product_id is not null and
date(b.repayment_time) <= d.date
AND d.date BETWEEN '2016-11-22'
AND '2016-11-22'
GROUP BY
br.product_id,
d.date
运行以上sql报错:
[Err] 1054 - Unknown column 'b.borrow_id' in 'on clause'
Mysql版本(select version();):5.6.23-log
解决:
MySQL5.0 Bug, 要把联合的表用括号包含起来才行。
修改后的sql:
SELECT
br.product_id,
d.date,
round(sum(b.repayment_amount) / 100, 2)
FROM
(s_repayment_detail b,
s_date d)
LEFT JOIN s_borrow_repayment_detail br ON br.borrow_id = b.borrow_id
WHERE
br.product_id is not null and
date(b.repayment_time) <= d.date
AND d.date BETWEEN '2016-11-22'
AND '2016-11-22'
GROUP BY
br.product_id,
d.date