Mysql报错[Err] 1054 – Unknown column 'b.borrow_id' in 'on clause'

数据库 waitig 677℃ 百度已收录 0评论
    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  

本文由【waitig】发表在等英博客
本文固定链接:Mysql报错[Err] 1054 – Unknown column 'b.borrow_id' in 'on clause'
欢迎关注本站官方公众号,每日都有干货分享!
等英博客官方公众号
点赞 (0)分享 (0)