联合所有帮助需要

问题描述：

我有六种不同的select语句用于SSRS报告目的。联合所有帮助需要

以下是两条陈述。我需要关于如何对波纹管执行Union All的帮助，并将所有语句合并为一个。

DECLARE @FromDate AS DATE='04-Aug-2015' 
DECLARE @ToDate AS DATE='05-Aug-2015' 

SELECT 
    A.LBrCode AS BranchCode, 
    (SELECT B.Name 
    FROM D001003 B 
    WHERE A.LBrCode = B.PBrCode) AS BranchName, 
    C.PrdCd AS Product, 
    SUM(D.FcyTrnAmt) AS Amount 
FROM 
    D009022 A 
INNER JOIN 
    D009021 C ON substring(A.PrdAcctId, 1, 8) = C.PrdCd 
       AND A.LBrCode = C.LBrCode 
LEFT JOIN 
    D009040 D ON A.PrdAcctId = D.VcrAcctId 
       AND substring(D.VcrAcctId, 1, 8) = C.PrdCd 
       AND A.LBrCode = D.LBrCode 
WHERE 
    A.AcctStat <> 3 
    AND A.DateOpen >= @FromDate 
    AND A.DateOpen <= @ToDate 
    AND C.ModuleType = 11 
    AND D.DrCr = 'D' 
    AND D.CanceledFlag <> 'C' 
GROUP BY 
    A.LBrCode, C.PrdCd 
ORDER BY 
    A.LBrCode 

UNION ALL 

SELECT 
    A.LBrCode AS BranchCode, 
    (SELECT B.Name FROM D001003 B WHERE A.LBrCode = B.PBrCode) AS BranchName, 
    C.PrdCd AS Product, 
    SUM(A.ActTotBalFcy) AS Balance 
FROM 
    D009022 A 
INNER JOIN 
    D009021 C ON substring(A.PrdAcctId, 1, 8) = C.PrdCd 
       AND A.LBrCode = C.LBrCode 
WHERE 
    C.ModuleType = 11 
    AND A.AcctStat <> 3 
    AND A.DateOpen >= @FromDate 
    AND A.DateOpen <= @ToDate 
GROUP BY 
    A.LBrCode, C.PrdCd 
ORDER BY 
    A.LBrCode

可以使用CTE（公用表表达式） –

将D的条件从WHERE子句移到ON子句以获得真正的LEFT JOIN行为。（因为它现在它作为一个普通的内部连接执行......） – jarlh

答

你只能申请一个ORDER BY子句影响结果的顺序，它有适用于整个结果集。如果像我怀疑，你希望所有从上面查询的结果的从底部查询结果之前出现，则需要另一列添加到数据允许这样的事情发生：

SELECT A.LBrCode AS BranchCode, 
(SELECT B.Name FROM D001003 B WHERE A.LBrCode=B.PBrCode) AS BranchName, 
C.PrdCd AS Product, 
sum(D.FcyTrnAmt) AS Amount, 
1 as ResultSet 
FROM D009022 A INNER JOIN D009021 C 
ON substring(A.PrdAcctId,1,8)=C.PrdCd 
AND A.LBrCode=C.LBrCode 
LEFT JOIN D009040 D 
ON A.PrdAcctId=D.VcrAcctId 
AND substring(D.VcrAcctId,1,8)=C.PrdCd 
AND A.LBrCode=D.LBrCode 
WHERE A.AcctStat <> 3 AND A.DateOpen>[email protected] AND A.DateOpen<[email protected] 
AND C.ModuleType=11 
AND D.DrCr='D' 
AND D.CanceledFlag<>'C' 

GROUP BY A.LBrCode, C.PrdCd 
--ORDER BY A.LBrCode 

UNION ALL 

SELECT A.LBrCode AS BranchCode, 
(SELECT B.Name FROM D001003 B WHERE A.LBrCode=B.PBrCode) AS BranchName, 
C.PrdCd AS Product, 
sum(A.ActTotBalFcy) AS Balance, 
2 
FROM D009022 A INNER JOIN D009021 C 
ON substring(A.PrdAcctId,1,8)=C.PrdCd 
AND A.LBrCode=C.LBrCode 
WHERE C.ModuleType=11 
AND A.AcctStat <> 3 
AND A.DateOpen>[email protected] AND A.DateOpen<[email protected] 

GROUP BY A.LBrCode, C.PrdCd 
ORDER BY ResultSet,BranchCode

答

尝试这样的事情

declare @var1 = value1; 
declare @var2 = value2 
;with cte1 AS(
Select statement1 
UNION ALL 
Select statement2 
UNION ALL 
Select statement3 
UNION ALL 
Select statement4 
UNION ALL 
Select statement5 
UNION ALL 
Select statement6 
) 
Select * From CTE1

但是如何使用group？ –

答

对于工会要运行这两个查询必须具有相同数量的列并且是兼容的数据类型，您可能需要转换列数据类型以确保它们相同。例如，您可能遇到与您的金额和余额数据类型列不兼容的问题。

您不能在联合中对每个单独的语句进行排序，这是没有意义的，因为您将生成单个输出。

但是，考虑到这些要点，您的查询应该像现在这样工作。

declare @test table (a int, b varchar(10)) 

insert into @test values (10,'test1') 
insert into @test values (20,'test2') 

select 
a, b 
from 
@test 

UNION ALL 

select 
a, b 
from 
@test 

order by a

答

查看您的查询，我假设您的sum计算是所有查询之间的唯一区别。

例如，上述两个union all查询之间的唯一区别是用于summation的字段和where子句过滤器（D.DrCr='D' AND D.CanceledFlag<>'C'）。如果这是真的，那么你可以只使用CASE WHEN语句来处理所有的6个不同的场景是这样的：

DECLARE @FromDate AS DATE = '04-Aug-2015'; 
DECLARE @ToDate AS DATE = '05-Aug-2015'; 

SELECT A.LBrCode AS BranchCode 
    ,(
     SELECT B.NAME 
     FROM D001003 B 
     WHERE A.LBrCode = B.PBrCode 
     ) AS BranchName 
    ,C.PrdCd AS Product 
    ,CASE 
     WHEN D.DrCr = 'D' AND D.CanceledFlag <> 'C' 
      THEN sum(A.ActTotBalFcy) 
     /*add other remaining conditions here */ 
    ELSE sum(D.FcyTrnAmt) 
     END AS Amount 
FROM D009022 A 
INNER JOIN D009021 C ON substring(A.PrdAcctId, 1, 8) = C.PrdCd 
    AND A.LBrCode = C.LBrCode 
LEFT JOIN D009040 D ON A.PrdAcctId = D.VcrAcctId 
    AND substring(D.VcrAcctId, 1, 8) = C.PrdCd 
    AND A.LBrCode = D.LBrCode 
WHERE A.AcctStat <> 3 
    AND A.DateOpen >= @FromDate 
    AND A.DateOpen <= @ToDate 
    AND C.ModuleType = 11 
GROUP BY A.LBrCode 
    ,C.PrdCd 
ORDER BY A.LBrCode

答

我将创建2 CTE当时：

SELECT * 
FROM CTE1 

UNION ALL 

SELECT * 
FROM CTE2 
GROUP BY ____ 
ORDER BY ______

你不能有更多然后1 GROUP BY和ORDER BY在执行联合全部时

联合所有帮助需要

相关推荐