SQL INNER JOIN/ON for help-CodePudding

Table 1:
Name and category number
Zhang SAN AAAA 10
Zhang SAN 8
Li si BBBB 5
Zhang SAN AAAA 5
Li si BBBB 10

Table 2:
Zhang SAN AAAA 25
Zhang SAN 10
Li si BBBB
15
Use name + category as conditions, want to compare the difference of table 1 and table 2, table 3:
Zhang SAN AAAA - 5
Zhang SAN - 2
Li si BBBB 10

Actual use ON conditions, because the second line of the table 1 and table 2 categories is NULL, so don't table 3 of the second row, the actual result is:
Zhang SAN AAAA - 5
Li si BBBB 10

The SQL statement is

Insert into table 3 select table 1. Name, table 1. The categories, the sum (table 1. The number - table 2.) as the number of the from table 1 inner join table 2 ON table 1. The name=table 2. The name AND table 1. Table 2.=
category
Could you tell me if you want to distinguish between "zhang SAN AAAA" and "zhang", how to write SQL statements,

Thank you!

CodePudding user response:

 if not object_id (' Tempdb for.. # T1 ') is null 
# drop table T1 
Go 
# the Create table T1 ([name] nvarchar (2), [category] nvarchar (4), [number] int) 
Insert # T1 
Select N 'Joe', N 'AAAA, 10 union all 
Select N 'zhang SAN, null, 8 union all 
Select N 'bill', N 'BBBB, 5 union all 
Select N 'Joe', N 'AAAA, 5 union all 
Select N 'bill', N 'BBBB, 10 
Go 
--> --> 

If not object_id (' Tempdb for.. # T2 ') is null 
Drop table # T2 
Go 
The Create table # T2 ([name] nvarchar (2), [category] nvarchar (4), [number] int) 
Insert # T2 
Select N 'Joe', N 'AAAA, 25 union all 
Select N 'zhang SAN, null, 10 union all 
Select N 'bill', N 'BBBB', 15 
Go 


Select 
A. a. [name], [category], a. b. [number] - [number] as [number] 
The from 
(Select [name], [category]=isnull ([category], ' '), sum ([number]] as [number] # from T1 group by [name], isnull ([category], ' ') as a 
Inner join # T2 as b on a. [name]=b. [name] and a. [category]=isnull (b. [category], ' ') 

/* 
Name and category number 
Li si BBBB 0 
Zhang SAN - 2 
Zhang SAN AAAA - 10 
*/