A single number corresponding to the two as a result, how to combine the NULL result-CodePudding

 
The create table # a 
(
XSDH varchar (50), 
Bm int, 
Nr varchar (50) 
) 

Insert # a 
Select '20201105111', '1', '15 yuan cash coupon' 
The union 
Select '20201105111', '2', 'WeChat pay' 
The union 
Select '20201105222', '3', 'pay treasure to pay' 


The create table # b 
(
Bm int, 
Nr varchar (50), 
Bz varchar (50), 
) 

Insert # b 
Select '1', '15 yuan cash coupon', 'vouchers' 

Select * 
The from # a left join # # # on a. m=b b.b m 


The select XSDH, bz 
The from # a left join # # # on a. m=b b.b m 


Drop table # a 
Drop table # b

How to set the number corresponding to the two results of the NULL merger?

CodePudding user response:

 
With cte 
As 
(select a.x SDH, b.b z 
The from # a left join # b b on a. m=b.b m) 

Select * from cte where bz is not null 
Union all 
Select * from cte a where not the exists (select 1 from cte where XSDH=a.x SDH and bz is not null)