主页 / dba / 问题 / 134063
Accepted
Kinyanjui Kamau
Asked: 2016-04-02 02:51:09 +0800 CST

合并 2 个 SQL 查询

  • 772

我在下面有这两个查询，用于计算从 1 月到 12 月的净利润。

查询 1

/* Profit for the Period 13 */
SELECT * FROM
(
SELECT MONTH(T0.RefDate) AS Month, '311999' AS 'SAP Code', 'Period Profit' AS 'Description', SUM (T0.[Credit] - T0.[Debit]) AS 'Amount',
'311200' AS 'FatherNum', 12 AS 'Group', 'Liabilities' [Liabilities] 
FROM JDT1 T0 WHERE (T0.RefDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,'20151231'), 0) AND '20151231') 
AND T0.[ContraAct] <> '311201'
GROUP BY T0.RefDate) AS q
PIVOT
(
SUM(Amount) 
FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query

结果：

在此处输入图像描述

查询 2

/* Profit for the Period 13 */
SELECT * FROM
(
SELECT MONTH(T1.RefDate) AS Month, '311999' AS 'SAP Code', 'Period Profit' AS 'Description', SUM (T1.[Credit] - T1.[Debit]) AS 'Amount',
'311200' AS 'FatherNum', 12 AS 'Group', 'Liabilities' [Liabilities] 
FROM OACT T0 INNER JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
WHERE (T1.RefDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,'20151231'), 0) AND '20151231') 
AND T0.GroupMask > 3 
GROUP BY T1.RefDate) AS q 
PIVOT
(
SUM(Amount) 
FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query

结果：

在此处输入图像描述

两者之间的差异出现在第 12 个月。查询 1 准确地获得了 Dec 利润，因为它是一个在 12 月关闭 2015 年的过程后存储数据的帐户。

如何将两者合并为一个查询，以便获得查询 2 的所有结果，第 12 个月除外，而不是 -4764758.550000 我得到 3280335.630000？

sql-server sql-server-2012

1 个回答

  1. Best Answer

    我创建了 2 个表变量以尝试匹配您的环境。我已将 UNION ALL 添加到您的原始查询中，一个查询选择除 12 月以外的所有其他月份，第二个查询仅选择 12 月。

    还有其他方法可以做到这一点，但无论如何，它看起来都是一个昂贵的查询。

    开始了：

    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,
GroupMask int
)

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)
values (2,4)

/* Profit for the Period 13 */
SELECT * FROM
(
        SELECT MONTH(T0.RefDate) AS Month, 
              '311999' AS 'SAP Code', 
              'Period Profit' AS 'Description', 
              SUM (T0.[Credit] - T0.[Debit]) AS 'Amount',
              '311200' AS 'FatherNum', 
              12 AS 'Group', 
              'Liabilities' [Liabilities] 
        FROM @JDT1 T0 
       WHERE (T0.RefDate 
              BETWEEN DATEADD(yy, DATEDIFF(yy,0,'20151231'), 0) 
              AND '20151231') 
        AND T0.[ContraAct] <> '311201'
        AND MONTH(T0.RefDate) = 12
        GROUP BY T0.RefDate

        UNION ALL


        SELECT MONTH(T1.RefDate) AS Month, 
              '311999' AS 'SAP Code', 
              'Period Profit' AS 'Description', 
              SUM (T1.[Credit] - T1.[Debit]) AS 'Amount',
              '311200' AS 'FatherNum', 
              12 AS 'Group', 
              'Liabilities' [Liabilities] 
        FROM @OACT T0 
             INNER JOIN @JDT1 T1 
                     ON T0.[AcctCode] = T1.[Account]
        WHERE (T1.RefDate 
               BETWEEN DATEADD(yy, DATEDIFF(yy,0,'20151231'), 0) 
                   AND '20151231') 
        AND T0.GroupMask > 3 
        AND MONTH(T1.RefDate) <> 12
        GROUP BY T1.RefDate

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

    结果，你可以比较十月和十二月。 在此处输入图像描述

    • 1

相关问题