sql - 多个表上没有主键的连接条件

Question

表1-

Yr  | Qtr  | T1_Data
2012    Q1   100
2012    Q2   400

表2-

Yr  | Qtr  | T2_Data
2012    Q1   200
2012    Q2   500

表 3 -

Yr  | Qtr  | T3_Data
2012    Q1   300
2012    Q2   600

输出表

 Yr   | Qtr | T1_Data | T2_Data | T3_Data
 2012   Q1     100      200       300
 2012   Q2     400      500       600

我正在尝试实现上述连接条件。我通过引用一些文章尝试使用左外连接并附带一个查询。但我不帮忙。

    select 
        t1.yr,
        t1.qtr,
        t1.t1_data,
        t2.t2_data,
        t3.t3_data
    from 
        table1 t1 
    inner join 
        table2 t2 on (t1.yr  = t2.yr and t1.qtr = t2.qtr) 
    inner join  
        table3 t3 on (t2.yr  = t3.yr and t2.qtr = t3.qtr)

Answer 1

您可以使用 PIVOT。运行此脚本，它应该会为您提供所需的内容。显然，您希望用您的实际表格替换我的虚拟语句。 (我修改以匹配您给定的数据和表结构。)

 SELECT 
            2012 AS 'Yr',
            'Q1' AS 'Qtr',      
            100 AS 'Val'
            INTO #tempTable1
    UNION
    SELECT 
            2012 AS 'Yr',
            'Q2' AS 'Qtr',
            400 AS 'Val'

    SELECT 
            2012 AS 'Yr',
            'Q1' AS 'Qtr',
            200 AS 'Val'
            INTO #tempTable2
    UNION
    SELECT 
            2012 AS 'Yr',
            'Q2' AS 'Qtr',
            500 AS 'Val'

    SELECT 
            2012 AS 'Yr',
            'Q1' AS 'Qtr',      
            300 AS 'Val'
            INTO #tempTable3
    UNION
    SELECT 
            2012 AS 'Yr',
            'Q2' AS 'Qtr',
            600 AS 'Val'


    SELECT Yr, Qtr, [Table1] AS T1_Data, [Table2] AS T2_Data, [Table3] AS T3_Data
    FROM 
    (
        SELECT *, 'Table1' AS 'TableID' FROM #tempTable1
        UNION
        SELECT *, 'Table2' AS 'TableID' FROM #tempTable2
        UNION
        SELECT *, 'Table3' AS 'TableID' FROM #tempTable3
    ) p
    PIVOT
    (
    SUM (Val)
    FOR TableID IN
    ( [Table1], [Table2], [Table3] )
    ) AS pvt
    ORDER BY pvt.Yr;


    DROP TABLE #tempTable1
    DROP TABLE #tempTable2
    DROP TABLE #tempTable3