sql语句为select a.cpbm,a.dhsl,if(b.rksl is null,a.dhsl,a.dhsl-b.rksl) from dh_dhxx_mx a left outer join kf_rkd b on a.dhxh = b.dhdh where a.dhxh = '20080126100012' and a.zt <> '3';a表中有3行记录,b表中有2行记录,得到的结果为6行记录,出现了笛卡尔现象。请高手指点。
调试欢乐多
+----------------+---------------+------+----+
| dhxh | cpbm | dhsl | zt |
+----------------+---------------+------+----+
| 20080126100012 | 9787302092673 | 5 | 1 |
| 20080126100012 | 9787504552907 | 6 | 1 |
| 20080126100012 | 6901028314947 | 6 | 1 |
+----------------+---------------+------+----+
kf_rkd表中的数据(rkxh) 为主键
+--------+----------------+---------------+------+------+----------------+------+------------+
| rkxh | rkdxh | cpbm | rksl | thbz | dhdh | thly | xgrq |
+--------+----------------+---------------+------+------+----------------+------+------------+
| 100001 | 20080128100002 | 9787302092673 | 5 | N | 20080126100012 | NULL | 2008-01-28 |
| 100002 | 20080128100002 | 9787504552907 | 4 | N | 20080126100012 | NULL | 2008-01-28 |
+--------+----------------+---------------+------+------+----------------+------+------------+
期望的到的结果
+----------------+---------------+------+------+----+
| dhxh | cpbm | dhsl | rksl | ce |
+----------------+---------------+------+------+----+
| 20080126100012 | 9787302092673 | 5 | 5 | 0 |
| 20080126100012 | 9787504552907 | 6 | 4 | 2 |
| 20080126100012 | 6901028314947 | 6 | | 6 |
+----------------+---------------+------+------+----+
from dh_dhxx_mx a
left outer join kf_rkd b on a.dhxh = b.dhdh
and a.cpbm = b.cpbm -- add this condition
where a.dhxh = '20080126100012' and a.zt <> '3'
dh_dhxx_mx表中的数据(dhxh,cpbm)为主键
kf_rkd表中的数据要对应到dh_dhxx_mx的记录上应该将两个字段同时关联上:( on a.dhxh = b.dhdh and a.cpbm = b.cpbm )才能完成这种逻辑表达。