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行记录,出现了笛卡尔现象。请高手指点。

解决方案 »

  1.   

    dh_dhxx_mx表中的数据(dhxh,cpbm)为主键
    +----------------+---------------+------+----+
    | 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 |
    +----------------+---------------+------+------+----+
      

  2.   

    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 
      and a.cpbm = b.cpbm -- add this condition
    where a.dhxh = '20080126100012' and a.zt <> '3'
      

  3.   

    是因为dh_dhxx_mx中的主键的原因么?
      

  4.   

    那是因为主键的原因吗?只是写的sql语句本身就应该出6条记录吧!
      

  5.   

    主键是记录的标识。
    dh_dhxx_mx表中的数据(dhxh,cpbm)为主键
    kf_rkd表中的数据要对应到dh_dhxx_mx的记录上应该将两个字段同时关联上:( on a.dhxh = b.dhdh and a.cpbm = b.cpbm )才能完成这种逻辑表达。