select id=identity(int,1,1),* into #temp from #tmp select case when id=1 then (select sum(本年完成) from #temp) else 0 end 合计,* from #temp
--这种一般前台处理比较好,后台的话,可以用: select 合计=isnull(合计,0),b.* from( select id=1,合计=sum(本年完成) from 表 ) a right join (select id=case 年份 when (select min(年份) from 表) then 1 else 0 end ,* from 表 ) b on a.id=b.id
谢谢,参考诸位的,我用下面的语句得到最后的结果 select case when 年份=(select min(年份) from #tmp) then (select sum(本年完成) from #tmp) else null end 合计, 年份,本年完成, (select sum(本年完成) as 累计完成 from #tmp where 年份<=tem.年份) as 累计完成 from #tmp as tem
select case when 年份='2001' then (select sum(本年完成) from 表) else ''end 合计,* from 表
--如果严格按照你的格式: select 合计=isnull(cast(合计 as varchar),''),b.年份,b.本年完成,b.累计完成 from( select id=1,合计=sum(本年完成) from #tmp ) a right join (select id=case 年份 when (select min(年份) from #tmp) then 1 else 0 end ,* from #tmp ) b on a.id=b.id
select case when id=1 then (select sum(本年完成) from #temp) else 0 end 合计,* from #temp
select 合计=isnull(合计,0),b.*
from(
select id=1,合计=sum(本年完成) from 表
) a right join
(select id=case 年份 when (select min(年份) from 表) then 1 else 0 end
,* from 表
) b on a.id=b.id
select case when 年份=(select min(年份) from #tmp) then (select sum(本年完成) from #tmp) else null end 合计,
年份,本年完成,
(select sum(本年完成) as 累计完成 from #tmp where 年份<=tem.年份) as 累计完成 from #tmp as tem
select 合计=isnull(cast(合计 as varchar),''),b.年份,b.本年完成,b.累计完成
from(
select id=1,合计=sum(本年完成) from #tmp
) a right join
(select id=case 年份 when (select min(年份) from #tmp) then 1 else 0 end
,* from #tmp
) b on a.id=b.id