select left(A.[Sort],1) as 'sort' ,(A.value-B.value) as 'value' from (select sort,sum(value) as value from tb1 group by sort) A ,(select sort,sum(value) as value from tb2 group by sort) B where left(A.sort)=left(B.sort)
select Sort sum([value])[value] from ( select Sort, [value] from tb1 union all select Sort, -[value] from tb2)t group by Sort
select left(a.sort,1) sort,isnull(总收入,0)- isnull(总支出,0) 实际收入 from (select sum(isnull(a.value,0)) 总收入 ,sort from tb1 group by sort) a left join (select sum(isnull(b.value,0)) 总支出 ,sort from tb2 group by sort) b on left(a.sort,1)=left(b.sort,1)
if not object_id('tb1') is null drop table tb1 Go Create table tb1([Sort] nvarchar(1),[value] int) Insert tb1 select N'A','3488' union all select N'A','8589' Go if not object_id('tb2') is null drop table tb2 Go Create table tb2([Sort] nvarchar(1),[value] int) Insert tb2 select N'A',44 union all select N'B',995 union all select N'B',95 union all select N'A',959 Go select Sort, sum([value])[value] from ( select Sort, [value] from tb1 union all select Sort, -[value] from tb2)t group by Sort /* Sort value ---- ----------- A 11074 B -1090(2 個資料列受到影響) */
select left(A.[Sort],1) as 'sort' ,(A.value-B.value) as 'value' from (select sort,sum(value) as value from tb1 group by sort) A ,(select sort,sum(value) as value from tb2 group by sort) B where left(A.sort,1)=left(B.sort,1)
select Sort sum([value])[value] from ( select Sort, [value] from tb1 union all select Sort, -[value] from tb2)t group by Sort不错
(select sort,sum(value) as value from tb1 group by sort) A
,(select sort,sum(value) as value from tb2 group by sort) B
where left(A.sort)=left(B.sort)
sum([value])[value]
from (
select Sort,
[value]
from tb1
union all
select Sort,
-[value]
from tb2)t
group by Sort
(select sum(isnull(a.value,0)) 总收入 ,sort from tb1 group by sort) a left join
(select sum(isnull(b.value,0)) 总支出 ,sort from tb2 group by sort) b
on left(a.sort,1)=left(b.sort,1)
drop table tb1
Go
Create table tb1([Sort] nvarchar(1),[value] int)
Insert tb1
select N'A','3488' union all
select N'A','8589'
Go
if not object_id('tb2') is null
drop table tb2
Go
Create table tb2([Sort] nvarchar(1),[value] int)
Insert tb2
select N'A',44 union all
select N'B',995 union all
select N'B',95 union all
select N'A',959
Go
select Sort,
sum([value])[value]
from ( select Sort, [value] from tb1
union all
select Sort, -[value] from tb2)t
group by Sort
/*
Sort value
---- -----------
A 11074
B -1090(2 個資料列受到影響)
*/
(select sort,sum(value) as value from tb1 group by sort) A
,(select sort,sum(value) as value from tb2 group by sort) B
where left(A.sort,1)=left(B.sort,1)
sum([value])[value]
from (
select Sort,
[value]
from tb1
union all
select Sort,
-[value]
from tb2)t
group by Sort不错