A表需要sum不?
create proc p_test
as
begin
select a.[name],a.[count] - b.[count] from tbA a
left join (select [name],sum([count]) as [count] from tbB group by [name]) b on b.[name] = a.[name]
end
go
create proc p_test
as
begin
select a.[name],a.[count] - b.[count] from tbA a
left join (select [name],sum([count]) as [count] from tbB group by [name]) b on b.[name] = a.[name]
end
go
INSERT INTO @TA SELECT 'AA',20
UNION ALL SELECT 'BB',30
UNION ALL SELECT 'CC',80
UNION ALL SELECT 'DD',50
UNION ALL SELECT 'EE',40
DECLARE @TB TABLE(NAME VARCHAR(10),ACOUNT INT)
INSERT INTO @TB SELECT 'AA',10
UNION ALL SELECT 'BB',10
UNION ALL SELECT 'BB',5
UNION ALL SELECT 'EE',5
UNION ALL SELECT 'EE',10
SELECT A.NAME,A.ACOUNT-ISNULL(B.ACOUNT,0) FROM @TA A
LEFT JOIN (SELECT NAME,SUM(ACOUNT) AS ACOUNT FROM @TB GROUP BY NAME) B
ON A.NAME=B.NAME/*
NAME
---------- -----------
AA 10
BB 15
CC 80
DD 50
EE 25(所影响的行数为 5 行)*/
LEFT JOIN (SELECT NAME,SUM(ACOUNT) AS ACOUNT FROM @TB GROUP BY NAME) B
ON A.NAME=B.NAME