代码之家  ›  专栏  ›  技术社区  ›  SP1

逐行连接两个表

  •  0
  • SP1  · 技术社区  · 6 年前

    我有两张桌子

     Col1  COl2  COl3  Col4
        1          3     
        3          5      
        4          6     
    

    第二张桌子也是一样的

    Col11  COl21  COl31  Col41
      1            3     
      3            5      
      4            6 
    

    我想把它作为一行输出

    Col11  Col21       COl31  Col41
     1     Col1/Col11    3    Col3/Col31
     3     Col1/Col11    5     ..
     4     Col1/Col11    6     ....
    

    我可以做一个内部联接并找到匹配的记录,但在这种情况下,我没有任何列可匹配,我只想逐行计算它。。有人能告诉我怎样才能做到这一点吗。

    1 回复  |  直到 6 年前
        1
  •  0
  •   Sreenu131    6 年前

    DECLARE @Tab1 AS TABLE (Col1 INT,COl2 INT,  COl3 INT,  Col4 INT)
    INSERT INTO @TAb1
    SELECT 1,NULL,3,NULL UNION ALL     
    SELECT 3,NULL,5,NULL UNION ALL      
    SELECT 4,NULL,6,NULL 
    
    DECLARE @Tab2 AS TABLE (Col11 INT,COl21 INT,  COl31 INT,  Col41 INT)
    INSERT INTO @TAb2
    SELECT 1,NULL,3,NULL UNION ALL     
    SELECT 3,NULL,5,NULL UNION ALL      
    SELECT 4,NULL,6,NULL 
    
    ;WITH CTE
    AS
    (
    SELECT Col11,'Col11'AS COl21  ,COl31 ,'COl31' AS Col41 
    FROM @Tab2   
    UNION ALL
    SELECT Col1,'Col1' ,COl3,'COl3' 
    FROM @Tab1
    )
        SELECT  
        Col11,
        STUFF((SELECT DISTINCT '/'+COl21 FROM CTE i WHERE i.Col11=o.Col11
            FOR XML PATH ('')),1,1,'') As COl21,
        COl31,
        STUFF((SELECT DISTINCT '/'+Col41 FROM CTE i WHERE i.COl31=o.COl31
            FOR XML PATH ('')),1,1,'') As Col41 
    FROM CTE o
    GROUP BY Col11,COl31
    

    结果

    Col11    COl21      COl31     Col41
    ----------------------------------------
    1       Col1/Col11    3     COl3/COl31
    3       Col1/Col11    5     COl3/COl31
    4       Col1/Col11    6     COl3/COl31