declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when t.flowtype='+cast(flowtype as varchar)+' then 1 else 0 end) as [作业类型'+cast(flowtype as varchar)+']'
from (
select flowtype from b
union all
select flowtype from c
) as t
group by flowtypeexec('
select a.id,a.flowcode as 作业编码,sum(1) as 作业总数'+@sql+',sum(case when flowstate=1 then 1 else 0 end) as 完成数量,sum(case when flowstate=0 then 1 else 0 end) as 剩余数量 from a,(select * from b union all select * from c) as t where a.flowcode=t.flowcode group by a.id,a.flowcode')--未测试
set @sql=''
select @sql=@sql+',sum(case when t.flowtype='+cast(flowtype as varchar)+' then 1 else 0 end) as [作业类型'+cast(flowtype as varchar)+']'
from (
select flowtype from b
union all
select flowtype from c
) as t
group by flowtypeexec('
select a.id,a.flowcode as 作业编码,sum(1) as 作业总数'+@sql+',sum(case when flowstate=1 then 1 else 0 end) as 完成数量,sum(case when flowstate=0 then 1 else 0 end) as 剩余数量 from a,(select * from b union all select * from c) as t where a.flowcode=t.flowcode group by a.id,a.flowcode')--未测试
set @sql=''
select @sql=@sql+',sum(case when t.flowtype='+cast(flowtype as varchar)+' then 1 else 0 end) as [作业类型'+cast(flowtype as varchar)+']'
from (
select flowtype from b
union all
select flowtype from c
) as t
group by flowtypeexec('
select a.id,a.flowcode as 作业编码,sum(1) as 作业总数'+@sql+',sum(case when flowstate=1 then 1 else 0 end) as 完成数量,sum(case when flowstate=0 then 1 else 0 end) as 剩余数量 from a left join (select * from b union all select * from c) as t on a.flowcode=t.flowcode group by a.id,a.flowcode')--未测试
所以不能用视图,必须用动态sql语句,你可以轻易把它改成存储过程使用