declare @a table
(
[date] varchar(10),
[No] int,
am int
)
declare @b table
(
[date] varchar(10),
[No] int,
am int
)
insert @a
select '7-1',1,200 union
select '7-5',2,500 union
select '8-4',3,100
insert @b
select '7-15',1,200 union
select '8-5',2,100
--测试
select *,
[col5]=col2+col3-col4
from
(
select [col1]=A.[No],
[col2]=( sum(case when left(A.[date],1)<'8' then A.am else 0 end)
-
sum(case when left(B.[date],1)<'8' then isnull(B.am,0) else 0 end)
),
[col3]=sum(case when left(A.[date],1)='8' then A.am else 0 end),
[col4]=sum(case when left(B.[date],1)='8' then isnull(B.am,0) else 0 end)
from @a A
left join @b B on A.[No]=B.[No]
group by A.[No]
)t