select
T1.serialno,t1.dwmonth,t2.dwmonth,T1.balance,t2.balance as ReportItem
from
receive_badloan T1 left join a_contract T2 on
t1.dwmonth=t2.dwmonth and t2.dwmonth='2005/10/31' and
t1.serialno = t2.serialno and
t1.businesscurrency = t2.businesscurrency where operateorg like '1011%'.
上面这句sql的要求是:最终显示的余额(balance)来自t2,serialno,dwmonth,businesscurrency是t2的三个主键。看起来这样写好像没问题,但是查询结果如下:
serialno t1.dwmonth t2.dwmonth t1.balance reportItem
111998060004 2005-09-30 303697.6
111998060004 2005-10-31 2005-10-31 278697.6 278697.6
,t2.balance有为空的值。查看原因发现 t1.dwmonth=t2.dwmonth为多余的条件,因为现在的要求是查询dwmonth为2005/10/31的余额,如果t1.dwmonth=t2.dwmonth了,则2005-09-30去关联t2 中 t2.dwmonth='2005/10/31'的数据当然 没得关联所以,t2.balance为空。
正确sql应该为:
select
T1.serialno,t1.dwmonth,t2.dwmonth,T1.balance,t2.balance as ReportItem
from
receive_badloan T1 left join a_contract T2 on
t2.dwmonth='2005/10/31' and
t1.serialno = t2.serialno and
t1.businesscurrency = t2.businesscurrency where operateorg like '1011%'
正确结果如下:
111998060004 2005-09-30 2005-10-31 303697.6 278697.6
111998060004 2005-10-31 2005-10-31 278697.6 278697.6
T1.serialno,t1.dwmonth,t2.dwmonth,T1.balance,t2.balance as ReportItem
from
receive_badloan T1 left join a_contract T2 on
t1.dwmonth=t2.dwmonth and t2.dwmonth='2005/10/31' and
t1.serialno = t2.serialno and
t1.businesscurrency = t2.businesscurrency where operateorg like '1011%'.
上面这句sql的要求是:最终显示的余额(balance)来自t2,serialno,dwmonth,businesscurrency是t2的三个主键。看起来这样写好像没问题,但是查询结果如下:
serialno t1.dwmonth t2.dwmonth t1.balance reportItem
111998060004 2005-09-30 303697.6
111998060004 2005-10-31 2005-10-31 278697.6 278697.6
,t2.balance有为空的值。查看原因发现 t1.dwmonth=t2.dwmonth为多余的条件,因为现在的要求是查询dwmonth为2005/10/31的余额,如果t1.dwmonth=t2.dwmonth了,则2005-09-30去关联t2 中 t2.dwmonth='2005/10/31'的数据当然 没得关联所以,t2.balance为空。
正确sql应该为:
select
T1.serialno,t1.dwmonth,t2.dwmonth,T1.balance,t2.balance as ReportItem
from
receive_badloan T1 left join a_contract T2 on
t2.dwmonth='2005/10/31' and
t1.serialno = t2.serialno and
t1.businesscurrency = t2.businesscurrency where operateorg like '1011%'
正确结果如下:
111998060004 2005-09-30 2005-10-31 303697.6 278697.6
111998060004 2005-10-31 2005-10-31 278697.6 278697.6
左连接并非主键全部要等值起来!