27,580
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #Tmp_A(Money int,project nvarchar(20),bank nvarchar(20))
INSERT INTO #Tmp_A
Select 500,N'项目1',N'工行' union
Select 600,N'项目2',N'建行' union
Select 700,N'项目3',N'农行'
if not object_id(N'Tempdb..#Tmp_B') is null
drop table #Tmp_B
Go
CREATE TABLE #Tmp_B(Money int,project nvarchar(20),bank nvarchar(20))
GO
INSERT INTO #Tmp_B
Select 200,N'项目1',N'工行' union
Select 200,N'项目2',N'建行'
Select (a.Money-isnull(b.Money,0)) as Money,a.project,a.bank
From #Tmp_A a
Left Join #Tmp_B b on a.Project=b.Project And a.bank=b.bank
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([money] int,[project] nvarchar(23),[bank] nvarchar(22))
Insert #A
select 500,N'项目1',N'工行' union all
select 600,N'项目2',N'建行' union all
select 700,N'项目3',N'农行'
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([money] int,[project] nvarchar(23),[bank] nvarchar(22))
Insert #B
select 200,N'项目1',N'工行' union all
select 200,N'项目2',N'建行'
Go
--测试数据结束
SELECT #A.money - ISNULL(#b.money, 0) AS [money] ,
#A.project ,
#A.bank
FROM #A
LEFT JOIN #B ON #B.project = #A.project
AND #B.bank = #A.bank