我有一表,有INT,VARCHAR,DATE各种类型,字段有几十上百个。。
现在我想合计其他部分字段,基本上是前面几个字段不需要合计,其他都需要。
如果使用SELECT SUM(..) ..,sum ....这样写会死掉,而且字段是不固定的,有时多有时少。我想合计某几行的值,求一快速的方法,谢谢。
现在我想合计其他部分字段,基本上是前面几个字段不需要合计,其他都需要。
如果使用SELECT SUM(..) ..,sum ....这样写会死掉,而且字段是不固定的,有时多有时少。我想合计某几行的值,求一快速的方法,谢谢。
set @s='' SET @sumSql='sum(FSumAmount*FAddLess) as FSumAmount,0,'''''
Select @s=@s+','+quotename(FName)+'=sum(case when FName='+quotename(FName,'''')+' then FsumAmount else 0 end)'
,@sumSql=@sumSql+','+quotename(FName)+'=sum(['+FName+']*FAddLess) '@s 是取行转换列使用的,@sumSql是取出SUM的,这样我就把@sumSql变量作为汇总的就行了但是我想问,如果不是行转成列的,不能用我这样的方法。楼上有说表结构不行,如果我几十个字段需要汇总的,这很正常吧,如果这些是固定的字段,一个个写SUM()是不是很麻烦呢?有没有更好的方法?谢谢
declare @sql1 varchar(8000)
declare @num dec
set @sql='select spid,spbh ' /*spid,spbh 是你说所的固定的char字段 不进行sum的,有几项你自己确定但是你要确定他是在字段的前几位*/
set @sql1= 'from spkfk order by spid,spbh ' /*spkfk 是你所需要的表*/
select @num=count(d.name) from syscolumns a,sysobjects d where a.id=d.id and d.name ='spkfk'
declare @tagint int set @tagint=3 /*set @tagint=3 '3' 根据你的char字段的个数来设置*/
declare @td int
set @td=0DECLARE @A TABLE (id INT ,zd CHAR(50))
INSERT INTO @A
SELECT row_number() over (partition by a.km order by a.km ) id ,case when a.zd='spid' then a.zd when a.zd='spbh' then a.zd else ',sum('+a.zd+')' end zd
FROM
(select d.name km,a.name zd /*km 代表库名,zd 字段名*/
from syscolumns a,sysobjects d
where a.id=d.id and d.name ='spkfk') a
while @tagint<=@num
begin
select @sql=@sql+zd from @a where id=@tagint
set @tagint=@tagint+1
end
select @sql+@sql1
declare @sql varchar(8000)
declare @sql1 varchar(8000)
declare @num dec
set @sql='select spid,spbh ' /*spid,spbh 是你说所的固定的char字段 不进行sum的,有几项你自己确定但是你要确定他是在字段的前几位*/
set @sql1= 'from spkfk order by spid,spbh ' /*spkfk 是你所需要的表*/
select @num=count(d.name) from syscolumns a,sysobjects d where a.id=d.id and d.name ='spkfk'
declare @tagint int set @tagint=3 /*set @tagint=3 '3' 根据你的char字段的个数来设置*/
declare @td int
set @td=0DECLARE @A TABLE (id INT ,zd CHAR(50))
INSERT INTO @A
SELECT row_number() over (partition by a.km order by a.km ) id ,case when a.zd='spid' then a.zd when a.zd='spbh' then a.zd else ',sum('+a.zd+')' end zd
FROM
(select d.name km,a.name zd /*km 代表库名,zd 字段名*/
from syscolumns a,sysobjects d
where a.id=d.id and d.name ='spkfk') a
while @tagint<=@num
begin
select @sql=@sql+zd from @a where id=@tagint
set @tagint=@tagint+1
end
select @sql+@sql1
declare @sql varchar(8000)
declare @sql1 varchar(8000)
declare @num dec
set @sql='select spid,spbh '
set @sql1= 'from spkfk order by spid,spbh '
select @num=count(d.name) from syscolumns a,sysobjects d where a.id=d.id and d.name ='spkfk'
declare @tagint int --定义标志字段,用于结束循环 set @tagint=1
set @tagint=3
declare @td int --定义标志字段,用于结束循环 set @tagint=1
set @td=0DECLARE @A TABLE (id INT ,zd CHAR(50))
INSERT INTO @A
SELECT row_number() over (partition by a.km order by a.km ) id ,case when a.zd='spid' then a.zd when a.zd='spbh' then a.zd else ',sum('+a.zd+')' end zd
FROM
(select d.name km,a.name zd
from syscolumns a,sysobjects d
where a.id=d.id and d.name ='spkfk') a
while @tagint<=@num
begin
select @sql=@sql+rtrim(ltrim(zd)) from @a where id=@tagint --加入了去空格
set @tagint=@tagint+1
end
select @sql+@sql1
执行后结果:、
select spid,spbh ,sum(sptm),sum(spmch),sum(zjm),sum(beactive),sum(yishj),sum(is_gdsj),sum(is_gdzk),sum(is_tjsp),sum(is_hysp),sum(is_gmp),sum(is_gsp),sum(is_sy),sum(is_yp),sum(jingd),sum(denglrq),sum(gengxshj),sum(delrq),sum(dw),sum(shpchd),sum(shpgg),sum(kemuhao),sum(oldcode),sum(rkhw),sum(ckhw),sum(bzqfs),sum(fenlbh),sum(guojbh),sum(changjbh),sum(huaxm),sum(changym),sum(suming),sum(xiwname),sum(tongym),sum(shengccj),sum(pizhwh),sum(zhucsb),sum(jixing),sum(shiyzh),sum(bulfy),sum(chuczysx),sum(chuffl),sum(yaofpd),sum(yongfyyl),sum(jinjzh),sum(zhuysx),sum(yaowchf),sum(youxq),sum(shangplx),sum(guizbz),sum(yongyfl),sum(leibie),sum(jlgg),sum(otd),sum(ordercycle),sum(stkquot),sum(shlv),sum(hshjj),sum(jj),sum(shj),sum(hshsj),sum(lshj),sum(koul),sum(kcsx),sum(kcxx),sum(helkc),sum(bzqts),sum(yujts),sum(zgshl),sum(zhdcbj),sum(zdshj),sum(zgshj),sum(zgjjxz),sum(maolv),sum(huiytj),sum(itemlength),sum(itemheight),sum(itemwidth),sum(weight),sum(casevolume),sum(caseweight),sum(zuixdwtj),sum(zuixdwzl),sum(pfpj),sum(dispcolor),sum(jx),sum(bzgg),sum(lastmodifytime),sum(is_cmd),sum(lsgg),sum(is_zdyh),sum(is_jkyp),sum(minshl),sum(cx_dj_kl),sum(is_cx_dj),sum(zdyh),sum(zdhyzklshj),sum(is_cyhydz),sum(huiyshl),sum(nocalcopys),sum(fuzr),sum(is_jsh),sum(is_lc),sum(is_zhongyao),sum(type_bh),sum(zdlshj),sum(guanjzjh),sum(shiyq),sum(abbrImageURL),sum(cbflbh),sum(imageUrl),sum(is_ybsp),sum(yb_zfbl),sum(yb_spbh),sum(username),sum(chuchj),sum(pifj),sum(zhilbz),sum(chargelb),sum(is_sendybzx),sum(tj_ylshj),sum(is_hj),sum(is_yxbz),sum(wdid),sum(chbjs),sum(dwbh),sum(zengp),sum(is_ch),sum(slhes),sum(bh),sum(spid_old),sum(spbh_old),sum(zhbz),sum(jwh),sum(is_tc),sum(tichbl)from spkfk order by spid,spbh