主页 / dba / 问题 / 97193
Accepted
Kinyanjui Kamau
Asked: 2015-04-08 05:35:09 +0800 CST

合并两个具有不同 where 语句的 Select 查询

  • 772

我在下面有这个查询，我试图在其中获取迄今为止的月份和年份（账户余额）的结果。

SELECT T3.AcctCode, T3.AcctName, SUM(T1.Debit - T1.Credit)* -1 AS 'Balance'
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
INNER JOIN OACT T3 ON T3.[AcctCode] = T2.[FatherNum]
WHERE T0.TaxDate BETWEEN '2014-09-01' AND '2014-09-30'
AND T3.AcctCode LIKE '611%'
GROUP BY T3.AcctCode, T3.AcctName
UNION 
SELECT  T3.AcctCode, T3.AcctName, SUM(T1.Debit - T1.Credit)* -1 AS 'YTD Balance'
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
INNER JOIN OACT T3 ON T3.[AcctCode] = T2.[FatherNum]
WHERE T0.TaxDate BETWEEN '2014-01-01' AND '2014-09-30'
AND T3.AcctCode LIKE '611%'
GROUP BY T3.AcctCode, T3.AcctName
ORDER BY T3.AcctCode

结果如下：

在此处输入图像描述

我遇到的问题是 Balance 和 Year to Date 余额列堆叠在另一个之上。如何修改此查询以具有 4 列，即：AcctCode、AcctName、Balance、YTD Balance？使用 SQL Server 2008

sql-server-2008 running-totals

1 个回答

  1. Best Answer

    WHERE由于除了第一个只是第二个限制的条件外，这两个部分是相同的，因此您可以使用一个查询，第二个条件和第一个总和的更改条件，即CASE表达式：

    SELECT T3.AcctCode, T3.AcctName, 
       SUM(CASE WHEN T0.TaxDate BETWEEN '2014-09-01' AND '2014-09-30'
             THEN T1.Debit - T1.Credit 
             ELSE 0 END)* -1          AS Balance,
       SUM(T1.Debit - T1.Credit)* -1  AS [YTD Balance] 

FROM OJDT T0
  INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
  INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
  INNER JOIN OACT T3 ON T3.[AcctCode] = T2.[FatherNum]
WHERE T0.TaxDate BETWEEN '2014-01-01' AND '2014-09-30'
  AND T3.AcctCode LIKE '611%'
GROUP BY T3.AcctCode, T3.AcctName ;
    • 2

相关问题