SQL Server - 使用聚集索引时如何存储数据页

Question

Kinyanjui Kamau

Asked: 2016-04-06 01:55:15 +0800 CST2016-04-06 01:55:15 +0800 CST 2016-04-06 01:55:15 +0800 CST

加入 2 个查询以并排匹配月份

772

我有 2 个要连接的 SQL 语句。第一个获取营业额帐户每月的实际数据。第二个查询获取相同营业额帐户的预算（如果有）。

查询 1 实际值

/* Turnover 1 */
SELECT * FROM
(
SELECT T0.AcctCode AS 'SAP Code', T0.AcctName AS 'Description', T0.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
MONTH(T1.RefDate) AS Month, SUM(T1.Debit - T1.Credit) AS 'Amount'
FROM OACT T0
LEFT JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account] 
LEFT JOIN OBGT T2 ON T0.[AcctCode] = T2.[AcctCode] 
WHERE T0.GroupMask = 4 AND T0.Levels = 5
AND (T1.RefDate BETWEEN '20150101' AND '20151231') 
GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate) AS q
PIVOT
(
    SUM(Amount) 
    FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query

样本数据

查询 2 预算

/* Turnover 1 */
SELECT * FROM
(
SELECT T0.AcctCode AS 'AcctCode', T3.AcctName AS 'Description', T3.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
CASE WHEN (T1.CredLTotal IS NULL OR T1.CredLTotal = 0) THEN T1.DebLTotal ELSE T1.CredLTotal END AS BudgetAmt, T1.Line_ID AS 'LineID'
FROM OBGT T0 INNER JOIN BGT1 T1 ON T0.[AbsId] = T1.[BudgId]
INNER JOIN OACT T3 ON T0.[AcctCode] = T3.[AcctCode] 
WHERE T3.GroupMask = 4) AS q
PIVOT
(
    SUM(BudgetAmt)
    FOR LineID IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11])
) AS P

样本数据

对于第二个查询，0 代表一月，1 代表二月……而 11 代表十二月。

如何组合这 2 个查询以便我可以获得如下数据？

不重复，因为它包含检索预算的查询。

1 个回答

Voted

Marcello Miorelli · Answer 1 · 2016-04-06T03:52:41+08:00

使用union all

与您在其他问题中使用的方式相同，结合 2 个查询

这个想法是完全一样的。

你所要做的就是

select * from 
(
select [set of columns]
from query1
union all
select [same set of columns as before]
from query2

) as Q
PIVOT
(
SUM(Amount) 
FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query

我想，如果我理解正确的话，我已经为你修改了它，见下图。

它是一个 UNION ALL 加上一个 CASE。

创建表格来完成练习有点麻烦。

    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    declare @JDT1 table(

    ContraAct char(6) primary key clustered,
    Account int,
    RefDate datetime,
    credit money,
    debit money

    )


    declare @OACT table(
    AcctCode int primary key clustered,
    AcctName varchar(108),
    FatherNum char(6),
    GroupMask int,
    Levels int,
    AbsId int
    )


    declare @OBGT table(
    AcctCode int primary key clustered,
    AbsId int,
    CredLTotal int, 
    DebLTotal int,
    Line_ID int
    )

    declare @BGT1 table(

    AcctCode int primary key clustered,
    AcctName varchar(108),
    FatherNum char(6),
    GroupMask int,
    Levels int,
    AbsId int,
    CredLTotal int, 
    DebLTotal int,
    Line_ID int

    )



    insert into @BGT1 values  (1,4,'London','412100',5,1,100,70,1)
    insert into @BGT1 values  (2,4,'London','412100',5,1,100,70,2)
    insert into @BGT1 values  (3,4,'London','412100',5,1,100,70,3)
    insert into @BGT1 values  (4,4,'London','412100',5,1,100,70,4)
    insert into @BGT1 values  (5,4,'London','412100',5,1,100,70,5)
    insert into @BGT1 values  (6,4,'London','412100',5,1,100,70,6)
    insert into @BGT1 values  (7,4,'London','412100',5,1,100,70,7)
    insert into @BGT1 values  (8,4,'London','412100',5,1,100,70,8)
    insert into @BGT1 values  (9,4,'London','412100',5,1,100,70,9)
    insert into @BGT1 values  (10,4,'London','412100',5,1,100,70,10)
    insert into @BGT1 values  (11,4,'London','412100',5,1,100,70,11)
    insert into @BGT1 values  (12,4,'London','412100',5,1,100,70,0)



    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311202',1,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311203',1,'20151231', 10,7)


    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311204',2,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311205',2,'20151231',10,7)


    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311206',1,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311207',1,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311208',2,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311209',2,'20151031',10,7)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum,Levels )
    values (2,4,'London','412100',5)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum,Levels )
    values (3,4,'New York','412101',5)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (4,4,'Rome','412102',5,1)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (5,4,'Paris','412103',5,1)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (6,4,'Moscow','412103',5,1)


    update @BGT1
    set GroupMask  = 4

    update @OACT
    set GroupMask  = 4


    insert into @OBGT 
    select 
    AcctCode,
    AbsId,
    CredLTotal, 
    DebLTotal,
    Line_ID
    from @BGT1


    --select * from @OBGT 

    --select * from @OACT

    --select * from @BGT1


    SELECT T0.AcctCode AS 'SAP Code', T0.AcctName AS 'Description', T0.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
    MONTH(T1.RefDate) AS Month, SUM(T1.Debit - T1.Credit) AS 'Amount'
    FROM @OACT T0
    LEFT JOIN @JDT1 T1 ON T0.[AcctCode] = T1.[Account] 
    --LEFT JOIN @OBGT T2 ON T0.[AcctCode] = T2.[AcctCode] -- this is not being used here
    WHERE T0.GroupMask = 4 AND T0.Levels = 5
    AND (T1.RefDate BETWEEN '20150101' AND '20151231') 
    GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate



    SELECT T0.AcctCode AS 'SAP Code', 
           T3.AcctName AS 'Description', 
           T3.FatherNum, 1 AS 'Group', 
           'Turnover' [Definition],

            T1.Line_ID+1 AS 'Month',

           CASE WHEN (T1.CredLTotal IS NULL OR T1.CredLTotal = 0) 
THEN T1.DebLTotal ELSE T1.CredLTotal END AS Amount

          FROM @OBGT T0 
    INNER JOIN @BGT1 T1 ON T0.[AbsId] = T1.[AbsId]
    INNER JOIN @OACT T3 ON T0.[AcctCode] = T3.[AcctCode] 
    WHERE T3.GroupMask = 4



    SELECT * FROM
    (

    SELECT T0.AcctCode AS 'SAP Code', T0.AcctName AS 'Description', T0.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
    MONTH(T1.RefDate) AS Month, SUM(T1.Debit - T1.Credit) AS 'Amount'
    FROM @OACT T0
    LEFT JOIN @JDT1 T1 ON T0.[AcctCode] = T1.[Account] 
    --LEFT JOIN @OBGT T2 ON T0.[AcctCode] = T2.[AcctCode] -- this is not being used here
    WHERE T0.GroupMask = 4 AND T0.Levels = 5
    AND (T1.RefDate BETWEEN '20150101' AND '20151231') 
    GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate

    UNION ALL

    SELECT T0.AcctCode AS 'SAP Code', 
           T3.AcctName AS 'Description', 
           T3.FatherNum, 1 AS 'Group', 
           'Turnover' [Definition],

            T1.Line_ID+1 AS 'Month',

           CASE WHEN (T1.CredLTotal IS NULL OR T1.CredLTotal = 0) 
           THEN T1.DebLTotal ELSE T1.CredLTotal END AS Amount

          FROM @OBGT T0 
    INNER JOIN @BGT1 T1 ON T0.[AbsId] = T1.[AbsId]
    INNER JOIN @OACT T3 ON T0.[AcctCode] = T3.[AcctCode] 
    WHERE T3.GroupMask = 4


    ) AS q
    PIVOT
    (
        SUM(Amount) 
        FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
    ) AS query

我相信，如果我理解得很好，你想要像上图这样的东西。

如果那不对，那么请创建一张你需要的图片，即使你在 excel 中这样做，但请尝试根据下面脚本中的表格使用这些值。或者用一些脚本将数据插入其中来发布您的表定义，那么我们肯定会在同一页面上。

另外，我没有包括您的所有专栏，如果它们真的很重要，您可以将它们添加到下面的脚本中。

 SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    declare @JDT1 table(

    ContraAct char(6) primary key clustered,
    Account int,
    RefDate datetime,
    credit money,
    debit money

    )


    declare @OACT table(
    AcctCode int primary key clustered,
    AcctName varchar(108),
    FatherNum char(6),
    GroupMask int,
    Levels int,
    AbsId int
    )


    declare @OBGT table(
    AcctCode int primary key clustered,
    AbsId int,
    CredLTotal int, 
    DebLTotal int,
    Line_ID int
    )

    declare @BGT1 table(

    AcctCode int primary key clustered,
    AcctName varchar(108),
    FatherNum char(6),
    GroupMask int,
    Levels int,
    AbsId int,
    CredLTotal int, 
    DebLTotal int,
    Line_ID int

    )



    insert into @BGT1 values  (1,4,'London','412100',5,1,100,70,1)
    insert into @BGT1 values  (2,4,'London','412100',5,1,100,70,2)
    insert into @BGT1 values  (3,4,'London','412100',5,1,100,70,3)
    insert into @BGT1 values  (4,4,'London','412100',5,1,100,70,4)
    insert into @BGT1 values  (5,4,'London','412100',5,1,100,70,5)
    insert into @BGT1 values  (6,4,'London','412100',5,1,100,70,6)
    insert into @BGT1 values  (7,4,'London','412100',5,1,100,70,7)
    insert into @BGT1 values  (8,4,'London','412100',5,1,100,70,8)
    insert into @BGT1 values  (9,4,'London','412100',5,1,100,70,9)
    insert into @BGT1 values  (10,4,'London','412100',5,1,100,70,10)
    insert into @BGT1 values  (11,4,'London','412100',5,1,100,70,11)
    insert into @BGT1 values  (12,4,'London','412100',5,1,100,70,0)



    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311202',1,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311203',1,'20151231', 10,7)


    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311204',2,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311215',4,'20151231',5,2)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311216',4,'20151231',5,3)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311219',4,'20151030',5,3)


    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311217',5,'20151231',8,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311218',6,'20151231',4,6)


        insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311205',2,'20151231',10,7)


    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311206',1,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311207',1,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311208',2,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311209',2,'20151031',10,7)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum,Levels )
    values (2,4,'London','412100',5)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum,Levels )
    values (3,4,'New York','412101',5)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (4,4,'Rome','412102',5,1)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (5,4,'Paris','412103',5,1)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (6,4,'Moscow','412103',5,1)


    update @BGT1
    set GroupMask  = 4

    update @OACT
    set GroupMask  = 4


    insert into @OBGT 
    select 
    AcctCode,
    AbsId,
    CredLTotal, 
    DebLTotal,
    Line_ID
    from @BGT1


    --select * from @OBGT 

    --select * from @OACT

    --select * from @BGT1

    declare @months table(
     i smallint primary key clustered,
     name varchar(25))

     insert into @months
     select 1,'jan'
        union all select
            2, 'feb'
        union all select 
            3, 'mar'
        union all select 
            4, 'apr'
        union all select 
            5, 'may'
        union all select 
            6, 'jun'
        union all select 
            7, 'jul'
        union all select 
            8, 'aug'
        union all select 
            9, 'sep'
        union all select 
            10, 'out'
        union all select 
            11, 'nov'
        union all select 
            12, 'dec'


SELECT 
 K.[SAP Code]
,K.Description,
[jan_Budget],
[jan_Amount],[feb_Budget],
[feb_Amount],[mar_Budget],
[mar_Amount],[apr_Budget],
[apr_Amount],[may_Budget],
[may_Amount],[jun_Budget],
[jun_Amount],[jul_Budget],
[jul_Amount],[aug_Budget],
[aug_Amount],[sep_Budget],
[sep_Amount],[out_Budget],
[out_Amount],[nov_Budget],
[nov_Amount],[dec_Budget],
[dec_Amount]

FROM

(
    SELECT A.* 
      FROM (

                SELECT K.[SAP Code]
                      ,K.[Description]
                      ,AMOUNT = COALESCE(K.Amount,0)
                      ,PIVOT_KEY = M.NAME + '_Amount'
                FROM @months M 
                     LEFT OUTER JOIN 

                        (   SELECT T0.AcctCode AS 'SAP Code', 
                                   T0.AcctName AS 'Description', 
                                   T0.FatherNum, 1 AS 'Group', 
                                   'Turnover' AS [Definition],
                            MONTH(T1.RefDate) AS 'Month', 
                            SUM(T1.Debit - T1.Credit) AS 'Amount'
                            FROM @OACT T0
                            LEFT JOIN @JDT1 T1 ON T0.[AcctCode] = T1.[Account] 
                            WHERE T0.GroupMask = 4 AND T0.Levels = 5
                            AND (T1.RefDate BETWEEN '20150101' AND '20151231') 
                            GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate
                        ) K
                        ON K.Month = M.i

           ) RADHE
    PIVOT (SUM([AMOUNT]) FOR PIVOT_KEY IN ([jan_Amount],[feb_Amount],[mar_Amount],
                                         [apr_Amount],[may_Amount],[jun_Amount],
                                         [jul_Amount],[aug_Amount],[sep_Amount],
                                         [out_Amount],[nov_Amount],[dec_Amount])) A
    where [SAP Code] is not null


) K  

INNER JOIN

(

SELECT *
  FROM (

              SELECT K.[SAP Code]
                  ,K.[Description]
                  ,AMOUNT = COALESCE(K.Amount,0)
                  ,PIVOT_KEY = M.NAME + '_Budget'
            FROM @months M 
                 LEFT OUTER JOIN 

                    (   

                        SELECT T0.AcctCode AS 'SAP Code', 
                               T3.AcctName AS 'Description', 
                               T3.FatherNum, 1 AS 'Group', 
                                T1.Line_ID+1 AS 'Month',

                               CASE WHEN (T1.CredLTotal IS NULL OR T1.CredLTotal = 0) 
                    THEN T1.DebLTotal ELSE T1.CredLTotal END AS Amount

                              FROM @OBGT T0 
                        INNER JOIN @BGT1 T1 ON T0.[AbsId] = T1.[AbsId]
                        INNER JOIN @OACT T3 ON T0.[AcctCode] = T3.[AcctCode] 
                        WHERE T3.GroupMask = 4

                    ) K
                    ON K.Month = M.i

       ) RADHE
PIVOT (SUM([AMOUNT]) FOR PIVOT_KEY IN ([jan_Budget],[feb_Budget],[mar_Budget],
                                     [apr_Budget],[may_Budget],[jun_Budget],
                                     [jul_Budget],[aug_Budget],[sep_Budget],
                                     [out_Budget],[nov_Budget],[dec_Budget])) B

) R
ON 1=1
AND  K.[SAP Code] = R.[SAP Code]

加入 2 个查询以并排匹配月份

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

加入 2 个查询以并排匹配月份

1 个回答

相关问题