There are A, B two tables, how in A table, on the basis of making A.A=B.A and a.=B.B D field data matching to A table, at the same time, does not meet the conditions of the corresponding field data is zero, will not be filtered (merger), A similar effect is in excel with the lookup function matching and AB to merge two tables,

Is the desired effect is as follows:

A B C D
Zhang M 100 FD1
Li si 200 0 F
Li si M 0 DF3
Wang Wu F 212 DFG1
Liu 7 M DSF1 DSF1


-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
In which A table data format is as follows:
A B C
Zhang M 100
Li si F 200
Wang Wu F 212


B table data format is as follows:

A B D
Zhang M FD1
Li si M DF3
Wang Wu F DFG1
Liu 7 M DSF1

CodePudding user response:

 
If OBJECT_ID (' tempdb for.. # ') is not A null 
The begin 
Drop table # A 
End 

If OBJECT_ID (' tempdb for.. # B ') is not null 
The begin 
Drop table # B 
End 

Select * # into A the from (
Select 'zhang' A, 'M B, 100 C union all 
Select 'bill', 'F', 200 union all 
Select 'Wang Wu', 'F', 212, 
) t 

Select * into the from # B (
Select 'zhang' A, 'M' B, 'FD1' D union all 
Select 'bill', 'M', 'DF3' union all 
Select 'Wang Wu', 'F', 'DFG1' union all 
Select 'liu qi', 'M', 'DSF1' 
) t 
; 

With cte as (
Select a.A, a. from # aa union 
The select b.A, b.B from # B B 
) 
The select cte. *, isnull (a.C, 0) C, isnull (b.D, 0) D from cte 
Left the join # aa on cte. A=a.A and cte. B=a. 
Left the join # B B on cte. A=b.A and cte. B=b.B 

CodePudding user response:

Your results liu seven C column should fill in the wrong

 CREATE TABLE # A (A VARCHAR (50), B VARCHAR (50), C INT) 
INSERT INTO # A 
SELECT 'zhang', 'M', 100 UNION ALL 
SELECT 'bill', 'F', 200 UNION ALL 
SELECT 'Wang Wu', 'F', 212, 

The CREATE TABLE # B (A VARCHAR (50), B VARCHAR (50), D VARCHAR (50)) 
INSERT INTO # B 
SELECT 'zhang', 'M', 'FD1' UNION ALL 
SELECT 'bill', 'M', 'DF3' UNION ALL 
SELECT 'Wang Wu', 'F', 'DFG1' UNION ALL 
SELECT 'liu qi', 'M', 'DSF1' 

SELECT DISTINCT c. *, ISNULL (C, 0) AS C, ISNULL (B.D, '0') AS D 
The FROM (
The SELECT A.A, a. FROM # aa 
UNION ALL 
The SELECT B.A, B.B FROM C # B B) 
LEFT the JOIN # aa ON A.A=c.a. AND a.=mount 
LEFT the JOIN # B B ON B.A=c.a. AND B.B=mount 

/* 
A B C D 
Li si 200 0 F 
Li si M 0 DF3 
Liu 7 M 0 DSF1 
Wang Wu F 212 DFG1 
Zhang M 100 FD1 
*/

CodePudding user response:

There is A problem I didn't say clear, in fact, A and B table each field has A very, very much, if have been use the select, this code is too long, can use other ways?

CodePudding user response:

 
The CREATE TABLE # A (A VARCHAR (50), B VARCHAR (50), C INT) 
INSERT INTO # A 
SELECT 'zhang', 'M', 100 UNION ALL 
SELECT 'bill', 'F', 200 UNION ALL 
SELECT 'Wang Wu', 'F', 212, 

The CREATE TABLE # B (A VARCHAR (50), B VARCHAR (50), D VARCHAR (50)) 
INSERT INTO # B 
SELECT 'zhang', 'M', 'FD1' UNION ALL 
SELECT 'bill', 'M', 'DF3' UNION ALL 
SELECT 'Wang Wu', 'F', 'DFG1' UNION ALL 
SELECT 'liu qi', 'M', 'DSF1' 

XX, XX. SELECT A, B, MAX (XX. C) C, MAX (XX. D) D the FROM (
SELECT A.A A, a. B, A.C C, '0' D # FROM A A 
UNION ALL 
SELECT B.A A, B.B B, 0 C, B.D D FROM # B B) XX, XX GROUP BY A, XX. B 

DROP TABLE # A, # B 

CodePudding user response:

 
The SELECT ISNULL (A.A, B.A) AS A, ISNULL (a., B.B) AS B, 
ISNULL (A.C, 0) AS C, ISNULL (B.D, 0) AS D 
The FROM TABLE_A A 
FULL JOIN TABLE_B ON A.A=B.A AND a. B=B.B 

CodePudding user response:

reference weixin_47397183 reply: 3/f

there is A problem I didn't say clear, in fact, A and B table each field has A very, very much, if have been use the select, this code is too long, can use other ways?

Are you a table number, or the number of rows in the table many
If the former, the table name no laws that will have to hand, SQL statement is to avoid too long; Table name regularly, you can also try the dynamic SQL statement
If the latter, in front of a few floors reply no problem at all