业务表很多的话,要用动态sql实现,如果表很多,造成语句很长,那还是用临时表处理
'select sum(JE) as JE,ID into #'+@tbname+ ' from '+@tbname+' group by id order by JE desc'
然后针对#tbname进行join on id
不难,但是写循环取表名比较烦.
'select sum(JE) as JE,ID into #'+@tbname+ ' from '+@tbname+' group by id order by JE desc'
然后针对#tbname进行join on id
不难,但是写循环取表名比较烦.
select 公司ID , sum(TABLE1业务的累计金额 ) as TABLE1业务的累计金额 ,sum(TABLE2业务的累计金额 ) as TABLE2业务的累计金额 ................,sum(TABLE1业务的累计金额 +TABLE2+....) as 所以业务的累计金额
from
(
select id , sum(TABLE1业务的累计金额 ) as TABLE1业务的累计金额, ,0 , 0 , 0 ,...from table1
union all
select id , 0 , sum(TABLE2业务的累计金额 ) as TABLE2业务的累计金额, ,0 , 0 , ...from table 2
union all
........
)
group by 公司ID
select id ,tablename,sum(je)
(select '表1' as tablename,ID,JE from table1
union all
select '表2' as tablename,ID,JE from table2
union all
select '表3' as tablename,ID,JE from table3
......
) b
group by id ,tablename
from company a left join ( select id,sum(je) as je1 from table1 group by id ) b
on a.id=b.id
left join ( select id,sum(je) as je2 from table2 group by id ) c
on a.id=c.id
......
left join ( select id,sum(je) as jeN from tableN group by id ) X
on a.id=X.id
order by isnull(je1,0)+isnull(je2,0)+...+isnull(jeN,0) desc
select id ,tablename,sum(je)
(select '表1' as tablename,ID,JE from table1
union all
select '表2' as tablename,ID,JE from table2
union all
select '表3' as tablename,ID,JE from table3
) b
group by id ,tablenameOK!