怎么样把这么两个查询写成一个视图或者存储过程.....在线等
select '合计' as tname,ltrim(a.aa) + '+' + ltrim(b.bb) + '*台' as yi
from (select sum(cast(yi as int))aa from #b1 where yi not like '%*%')a,
(select sum(cast(replace(yi,'*','') as int))bb from #b1 where yi like '%*%')bselect '合计' as tname,ltrim(a.aa) + '+' + ltrim(b.bb) + '*台' as er
from (select sum(cast(er as int))aa from #b2 where er not like '%*%')a,
(select sum(cast(replace(er,'*','') as int))bb from #b2 where er like '%*%')b
select '合计' as tname,ltrim(a.aa) + '+' + ltrim(b.bb) + '*台' as yi
from (select sum(cast(yi as int))aa from #b1 where yi not like '%*%')a,
(select sum(cast(replace(yi,'*','') as int))bb from #b1 where yi like '%*%')bselect '合计' as tname,ltrim(a.aa) + '+' + ltrim(b.bb) + '*台' as er
from (select sum(cast(er as int))aa from #b2 where er not like '%*%')a,
(select sum(cast(replace(er,'*','') as int))bb from #b2 where er like '%*%')b
create proc sp_getresult
as
begin
select '合计' as tname,ltrim(a.aa) + '+' + ltrim(b.bb) + '*台' as yi
from (select sum(cast(yi as int))aa from #b1 where yi not like '%*%')a,
(select sum(cast(replace(yi,'*','') as int))bb from #b1 where yi like '%*%')b
union all
select '合计' as tname,ltrim(a.aa) + '+' + ltrim(b.bb) + '*台' as er
from (select sum(cast(er as int))aa from #b2 where er not like '%*%')a,
(select sum(cast(replace(er,'*','') as int))bb from #b2 where er like '%*%')b
end-- ???
from (select sum(cast(yi as int))aa from #b1 where yi not like '%*%')a,
(select sum(cast(replace(yi,'*','') as int))bb from #b1 where yi like '%*%')b
union all
select '合计' as tname,ltrim(a.aa) + '+' + ltrim(b.bb) + '*台' as er
from (select sum(cast(er as int))aa from #b2 where er not like '%*%')a,
(select sum(cast(replace(er,'*','') as int))bb from #b2 where er like '%*%')b
cerate view v_name
as
begin
select '合计' as tname,ltrim(a.aa) + '+' + ltrim(b.bb) + '*台' as yi
from (select sum(cast(yi as int))aa from #b1 where yi not like '%*%')a,
(select sum(cast(replace(yi,'*','') as int))bb from #b1 where yi like '%*%')b
union all
select '合计' as tname,ltrim(a.aa) + '+' + ltrim(b.bb) + '*台' as er
from (select sum(cast(er as int))aa from #b2 where er not like '%*%')a,
(select sum(cast(replace(er,'*','') as int))bb from #b2 where er like '%*%')b
end
消息 208,级别 16,状态 0,过程 proc_tb1,第 4 行
对象名 '#b1' 无效。