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')
执行完报“每个 GROUP BY 表达式必须至少包含一个不是外部引用的列。”，请大神指教
------解决思路----------------------
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