select a.zy_id AS zyblh, b.yaop_id AS xmbh, c.mc AS xmmc, NULL AS cd, NULL AS gg,
CASE WHEN c.zylb = 03 THEN 1
     WHEN c.zylb = 02 THEN 2
     WHEN c.zylb = 01 THEN 3
     WHEN c.zylb = 06 THEN 4
     ELSE 0 END AS lx,
           c.dw AS dwmc,
            '1' AS js,
           b.dj,
           b.sl,
           b.je AS zj,
           d.mc AS hjr,
           a.rq AS hjsj,
           a.zfbz
  from h_yaj a,h_sfdmx b,h_yaop c,h_yis d
  where a.djh = b.sfd_id
        and b.yaop_id = c.id
        and a.sfy = d.id
        and a.zfbz = '0'
        and a.zy_id = '00006951'

想在这个SQL明细汇总表的基础上加入退费的明细汇总，需要加入h_ckdmx.e表,。

a表与b表，和a表与e表的关系为： 如果没有退费时就为a.djh = b.sfd_id ；如有退费发生时，a.djh 同时包含b.sfd_id 和e.ckd_id  。   
b表与e表的关系为：b.id = e.sfdmx_id  。

并且 如果有退费发生时sl (数量) 需要 = (b.sl 减去 e表里b.id = e.sfdmx_id 项目的sl) ； zj（总价）需要 = (b.je 减去e表里b.id = e.sfdmx_id 项目的je)

求老师完善！

大神门啊，别这么吝啬好吧，帮帮忙啊

楼主，大概看懂了你的要求，你的程序也写得很清楚。
按你的要求，对你的程序做了一些修改，主要是提供一个思路吧，你可能要在你的数据环境中调试一下才能运行。

select a.zy_id AS zyblh, b.yaop_id AS xmbh, c.mc AS xmmc, NULL AS cd, NULL AS gg,
CASE WHEN c.zylb = 03 THEN 1
     WHEN c.zylb = 02 THEN 2
     WHEN c.zylb = 01 THEN 3
     WHEN c.zylb = 06 THEN 4
     ELSE 0 END AS lx,
           c.dw AS dwmc,
            '1' AS js,
           b.dj,
           b.sl-isnull(e.sl,0) as sl,
           b.je-isnull(e.je,0) AS zj,
           d.mc AS hjr,
           a.rq AS hjsj,
           a.zfbz
from h_yaj a
    inner join h_sfdmx b on a.djh = b.sfd_id
    inner join h_yaop c on b.yaop_id = c.id
    inner join h_yis d on a.sfy = d.id
    left join h_ckdmx e on b.id = e.sfdmx_id
where a.zfbz = '0'
    and a.zy_id = '00006951'