declare @cs varchar(30),@hsyear varchar(10),@hsmonth varchar(10),@ywbm varchar(10)
select @cs='khmc',@hsyear='2014',@hsmonth='10',@ywbm='国际业务部'
drop table #test
create table #test(cs varchar(12),sjslm decimal(12,2),bz decimal(12,2),sj decimal(12,2),dwcb decimal(12,2),dwfy decimal(12,2),dwlr decimal(12,2))
insert into #test Exec('select cs,sjslm, sjslm/zsjslm,rmbcjje/sjslm,cbje/sjslm,fyze/sjslm,(rmbcjje - cbje - fyze)/sjslm from (
select '''+@cs+'''as cs ,sum(sjslm)sjslm,sum(rmbcjje)rmbcjje,sum(cbje)cbje,sum(ddfy+glfy+cwfy+xsfy+cktsce+zycb+isnull((mlckjf/mlckjc/ljts*sjslm*ts),0))fyze,
(select SUM(sjslm) from mlcbfx_report_xslrfx where hsyear='''+@hsyear+''' and hsmonth='''+@hsmonth+''')as zsjslm from
mlcbfx_report_xslrfx where hsyear='''+@hsyear+''' and hsmonth='''+@hsmonth+''' and ywbm='''+@ywbm+''' group by '''+@cs+''' ) as a')你print出来可以看到它不是变量或者列名而是常量。如果都是常量,没必要group by
select @cs='khmc',@hsyear='2014',@hsmonth='10',@ywbm='国际业务部'
drop table #test
create table #test(cs varchar(12),sjslm decimal(12,2),bz decimal(12,2),sj decimal(12,2),dwcb decimal(12,2),dwfy decimal(12,2),dwlr decimal(12,2))
insert into #test Exec('select cs,sjslm, sjslm/zsjslm,rmbcjje/sjslm,cbje/sjslm,fyze/sjslm,(rmbcjje - cbje - fyze)/sjslm from (
select '''+@cs+'''as cs ,sum(sjslm)sjslm,sum(rmbcjje)rmbcjje,sum(cbje)cbje,sum(ddfy+glfy+cwfy+xsfy+cktsce+zycb+isnull((mlckjf/mlckjc/ljts*sjslm*ts),0))fyze,
(select SUM(sjslm) from mlcbfx_report_xslrfx where hsyear='''+@hsyear+''' and hsmonth='''+@hsmonth+''')as zsjslm from
mlcbfx_report_xslrfx where hsyear='''+@hsyear+''' and hsmonth='''+@hsmonth+''' and ywbm='''+@ywbm+''' group by '''+@cs+''' ) as a')你print出来可以看到它不是变量或者列名而是常量。如果都是常量,没必要group by
sjslm,
sjslm / zsjslm,
rmbcjje / sjslm,
cbje / sjslm,
fyze / sjslm,
(rmbcjje - cbje - fyze) / sjslm
FROM ( SELECT 'khmc' AS cs,
SUM(sjslm) sjslm,
SUM(rmbcjje) rmbcjje,
SUM(cbje) cbje,
SUM(ddfy + glfy + cwfy + xsfy + cktsce + zycb + Isnull((mlckjf / mlckjc / ljts * sjslm * ts),0)) fyze,
(SELECT SUM(sjslm)
FROM mlcbfx_report_xslrfx
WHERE hsyear = '2014'
AND hsmonth = '10') AS zsjslm
FROM mlcbfx_report_xslrfx
WHERE hsyear = '2014'
AND hsmonth = '10'
AND ywbm = '国际业务部'
GROUP BY 'khmc' -- 字符串常量不能用做分组;如果mlcbfx_report_xslrfx中的确有khmc字段,那么就不要加引号。
) AS a