SQL - 全部联合而不是加入

Question

有人可以帮我弄清楚如何使用UNION ALL编写此脚本吗？目前它使用的是JOIN而不是UNION ALL。

SELECT
    ISNULL(#vol2016.Destination, #vol2017.Destination) AS [Destination],
    ISNULL(#vol2016.[Volume 2016],0) AS [Volume 2016],
    ISNULL(#vol2017.[Volume 2017],0) AS [Volume 2017],
    ISNULL(#vol2017.[Volume 2017],0) - ISNULL(#vol2016.[Volume 2016],0) AS [Developement]
FROM(
    SELECT
        [year], [Destination], CAST(SUM([Volume]) as INT) as [Volume 2016] 
    FROM [dbo].[dw_lc_full_aggregated_1]
    WHERE [entry_type] = 'Country upload'
    AND [year]='2016'
    GROUP BY [year], [Destination]) #vol2016
FULL JOIN    
(SELECT
     [year], [Destination], CAST(SUM([Volume]) as INT) as [Volume 2017]
 FROM [dbo].[dw_lc_full_aggregated_1]
 WHERE [entry_type] = 'Country upload' AND [year]='2017'
 GROUP BY [year], [Destination]) #vol2017
ON [#vol2016].Destination=[#vol2017].Destination

因此，输出应分为三列。目的地，2016 年卷，2017 年卷。

Answer 1

使用此查询：

SELECT [year]
     , [Destination]
     , CAST(SUM([Volume]) as INT) AS [Volume]
  FROM
    (
        SELECT [year]
             , [Destination]
             , [Volume]
          FROM [dbo].[dw_lc_full_aggregated_1]
         WHERE [entry_type] = 'Country upload' AND [year]='2016'
        UNION ALL
        SELECT  [year]
              , [Destination]
              , [Volume]
          FROM  [dbo].[dw_lc_full_aggregated_1]
         WHERE  [entry_type] = 'Country upload' AND [year]='2017'
    ) AS T
GROUP BY [year],[Destination]

Answer 2

实际上，你可以通过条件聚合(SUM()中的CASE语句(使生活变得更加简单

SELECT
    [Destination],
    [Volume_2016],
    [Volume_2017],
    [Volume_2017] - [Volume_2016]   AS [Development]
FROM
(
    SELECT
        [Destination],
        CAST(SUM(CASE WHEN [year] = '2016' THEN [Volume] END) as INT) [Volume_2016],
        CAST(SUM(CASE WHEN [year] = '2017' THEN [Volume] END) as INT) [Volume_2017]
    FROM
        [dbo].[dw_lc_full_aggregated_1]
    WHERE
            [entry_type] =  'Country upload'
        AND [year]       IN ('2016', '2017')
    GROUP BY
        [Destination]
)
    AS pivoted_agreggate

编辑：[Year]从GROUP BY中删除(复制和粘贴错误(