mysql - 无法使用联接或联合合并两个表

Question

这些是我的表格:SELECT * FROM TableA和 SELECT * FROM TableB

    Table A             Table B
    +-------+------+    +-------+------+ 
    | FileA | StepA|    | FileB | StepB|
    +-------+------+    +-------+------+ 
    |  001  |  1   |    |  001  |   1  |
    |  001  |  2   |    |  001  |   2  |
    |  001  |  3   |    |  001  |   3  |
    |  001  |  4   |    |  002  |   1  |
    |  001  |  5   |    |  002  |   2  |    
    |  002  |  1   |    |  002  |   3  |    
    |  002  |  2   |    |  003  |   1  |
    |  002  |  3   |    +-------+------+
    |  002  |  4   |    
    |  002  |  5   |  
    |  004  |  1   |    
    |  004  |  2   |    
    |  004  |  3   | 
    |  004  |  4   |    
    |  004  |  5   |
    +-------+------+ 

我的结果:

SELECT * FROM TableA AS TA LEFT JOIN TableB AS TB ON TA.FileA != TB.FileB GROUP BY TA.FileA ORDER BY TA.StepA ASC

    +-------+------+-------+------+ 
    | FileA | StepA| FileA | StepB|
    +-------+------+-------+------+ 
    |  001  |  1   |  001  |   1  |
    |  001  |  2   |  001  |   1  |
    |  001  |  3   |  001  |   1  |
    |  001  |  4   |  001  |   1  |
    |  001  |  5   |  001  |   1  |  
    +-------+------+-------+------+ 

但这是我的预期结果:

    +-------+------+-------+------+ 
    | FileA | StepA| FileB | StepB|
    +-------+------+-------+------+ 
    |  001  |  1   |  001  |   1  |
    |  001  |  2   |  001  |   2  |
    |  001  |  3   |  001  |   3  |
    |  001  |  4   |  null | null |
    |  001  |  5   |  null | null |
    |  002  |  1   |  002  |   1  |    
    |  002  |  2   |  002  |   2  |    
    |  002  |  3   |  002  |   3  |       
    |  002  |  4   |  null | null |       
    |  002  |  5   |  null | null |  
    |  null | null |  003  |   1  |          
    |  004  |  1   |  null | null |    
    |  004  |  2   |  null | null |      
    |  004  |  3   |  null | null |    
    |  004  |  4   |  null | null |      
    |  004  |  5   |  null | null |    
    +-------+------+-------+------+ 

是否可以通过联接或联合来执行这些操作，以便我可以获得这些输出？

Answer 1

我认为这些 SQL 对你有用。

    SELECT * FROM (

       SELECT * FROM tablea AS TA LEFT JOIN tableb AS TB 
    ON TA.FileA = TB.FileB AND TA.stepA= TB.stepB 

UNION
     SELECT * FROM tablea AS TA RIGHT JOIN tableb AS TB 
    ON TA.FileA = TB.FileB AND TA.stepA= TB.stepB ) a
  ORDER BY FileA, stepA,fileb ASC

谢谢。