我写了这么一个脚本
SELECT da_sp.spdm, da_sp.sp, da_sp.jldw, da_sp.ggxh, da_sp.wbdw,
da_sp.wbbzl, da_sp.nbdw, da_sp.nbbzl, da_sp.cbj, da_sp.zhjj,
da_sp.pddw, da_sp.pdxs, da_sp.fdyclbj,
da_sp.zjpsbj, xs_jxcrb.spnm, sum(xs_jxcrb.cksl) as cksl, isnull(c.bqsl,0) as bqsl,
0.00 as ygsl, 0.00 as ygkcdwsl, 0.00 as qyyl,
0.00 as yjxhsl
FROM da_sp, xs_jxcrb, (select spnm,sum(bqsl) as bqsl from kc_zz GROUP by spnm) c
WHERE ( xs_jxcrb.spnm = da_sp.spnm ) and ( da_sp.qybj = 1 ) AND ( da_sp.jkcbj > 0 ) and
( da_sp.spnm *= c.spnm) and xs_jxcrb.xsrq>=20141211 and xs_jxcrb.xsrq<=20141211
and datepart(dw,left(convert(char(8),xs_jxcrb.xsrq),4) + '-' + substring(convert(char(8),xs_jxcrb.xsrq),5,2) +'-' + right(convert(char(8),xs_jxcrb.xsrq),2))=5
and da_sp.fdyclbj=1
group by xs_jxcrb.spnm,da_sp.spdm,da_sp.sp,da_sp.jldw,da_sp.ggxh,da_sp.wbdw,da_sp.wbbzl,da_sp.nbdw,da_sp.nbbzl,da_sp.cbj,da_sp.zhjj,da_sp.pddw,da_sp.pdxs,c.bqsl,da_sp.fdyclbj,da_sp.zjpsbj having sum(xs_jxcrb.cksl)>0 order by da_sp.spdm
但是显示的结果是没有按照单个的spnm汇总显示，而是有很多重复记录，原因就在于where 子句中包含了and datepart(dw,left(convert(char(8),xs_jxcrb.xsrq),4) + '-' + substring(convert(char(8),xs_jxcrb.xsrq),5,2) +'-' + right(convert(char(8),xs_jxcrb.xsrq),2))=5 的关系，如果去掉这个条件的话group by正常，不知道怎么解决
