2 wuzhong wuzhong 于 2014.12.12 00:15 提问

请教一个数据库分组问题

我写了这么一个脚本
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正常,不知道怎么解决

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!