CodePudding user response:

 

The CREATE TABLE # A (
ID VARCHAR (2) 
) 
The CREATE TABLE # B (
ID VARCHAR (2) 
) 
The CREATE TABLE # C (
ID VARCHAR (2) 
) 
# INSERT INTO A VALUES (' A '), (' A '), (" B "), (" B "), (" C "), (" C "), (' D '), (' G ') 
INSERT INTO # B VALUES (' B '), (" B "), (" B "), (" B "), (" C "), (' D '), (' H ') 
INSERT INTO # C VALUES (' A '), (' D '), (" F "), (" G "), (" G "), (' G ') 

SELECT A, B, ID FROM (
The SELECT A1. ID A, A1. RN, B1. ID FROM B (
SELECT, Anderson D ROW_NUMBER () OVER (PARTITION BY Anderson D ORDER BY 1) (SELECT) RN FROM A1 # A, A) 
FULL JOIN (
SELECT, Anderson D ROW_NUMBER () OVER (PARTITION BY Anderson D ORDER BY 1) (SELECT) RN FROM B1 # B A) ON A1. ID=B1. The ID AND A1. RN=B1. RN) A2 
FULL JOIN (
The SELECT, Anderson D ROW_NUMBER () OVER (PARTITION BY Anderson D ORDER BY 1) (SELECT) RN FROM C1 ON A2. # C A) A=C1. ID AND A2. RN=C1. RN 
The ORDER BY COALESCE (A, B, ID) 

# # # DROP TABLE A, B, C 

CodePudding user response:

 
SELECT * FROM (
SELECT, Anderson D ROW_NUMBER () OVER (PARTITION BY Anderson D ORDER BY 1) (SELECT) RN FROM # aa) A1 FULL JOIN (
SELECT, Anderson D ROW_NUMBER () OVER (PARTITION BY Anderson D ORDER BY 1) (SELECT) RN FROM B1 # B A) ON A1. ID=B1. The ID AND A1. RN=B1. RN FULL JOIN (
SELECT, Anderson D ROW_NUMBER () OVER (PARTITION BY Anderson D ORDER BY 1) (SELECT) RN FROM C1 ON C # A) 
(C1. ID=B1. ID AND C1. RN=B1. The RN) OR (C1) ID=A1) ID AND C1. RN=A1. RN) 
The ORDER BY COALESCE (A1. ID, B1. ID, C1. ID) 

Such as looks simple point

CodePudding user response:

Where have so complicated,
Three tables, is to use full join or Cross the Apply connection,

CodePudding user response:

Use the first floor data

 with t as (
Select * and # 't1' as TB from a 
Union all 
Select *, 't2' as TB from # b 
Union all 
Select *, 't3 as TB from # c 
) 
Select * from (
Select the match, TB, dense_rank () over (the order by the match, rids) as who 
The from (
Select *, ROW_NUMBER () over (partition by match, TB order by @ @ rowcount) as rids the from t 
) a 
) a 
The pivot (Max (match) for TB in (t1, t2, t3)) p