sql-server - SQL使用group by合并重复行

Question

我有一个包含这样值的表；

ACC     SALES LY   SALES TY      YEAR
-------------------------------------
B0022      0           15        2017
B0022      22          0         2016
AA000      0           12        2017
AA000      6           0         2016

我希望创建一个 View ，它将合并具有相同 ACC 的行。所以我要找的是这个；

ACC     SALES LY   SALES TY      YEAR
--------------------------------------
B0022      22          15        EMPTY
AA000       6          12        EMPTY

我查看了 GROUP BY 子句，over(Partition by) 但我就是无法让它工作。这仅显示了我当前表/ View 的一部分。

完整的 View 代码在这里:

SELECT DISTINCT TOP (100) PERCENT 
    dbo.[Sales By SKU Units].[Customer Account Number], 
    CASE 
       WHEN [Sales By SKU Units].[Customer Account Number] = 'S0040' THEN 'H'
       WHEN [Sales By SKU Units].[Customer Account Number] = 'Z0004' THEN 'E'
       WHEN [Sales By SKU Units].[Customer Account Number] = 'N0014' THEN 'G' 
       WHEN [Sales By SKU Units].[Customer Account Number] = 'B0022' THEN 'B'
       WHEN [Sales By SKU Units].[Customer Account Number] = 'A0097' THEN 'F' 
       WHEN [Sales By SKU Units].[Customer Account Number] = 'H0085' THEN 'F'
       WHEN [Sales By SKU Units].[Customer Account Number] = 'A0044' THEN 'A' 
       WHEN [Sales By SKU Units].[Customer Account Number] = 'S0482' THEN 'W'
    END AS CustomerName, 
    dbo.[Sales By SKU Units].SKU, 
    dbo.RangeLists.Column1 AS Status, 
    CASE 
       WHEN [Sales By SKU Value].[W/H Stock] IS NULL THEN '0' 
       WHEN [Sales By SKU Value].[W/H Stock] = '' THEN '0' 
       WHEN [Sales By SKU Value].[W/H Stock] IS NOT NULL THEN [Sales By SKU Value].[W/H Stock] 
    END AS [Warehouse Stock], 
    CASE 
       WHEN [Sales By SKU Value].[Store Stock] IS NULL THEN '0' 
       WHEN [Sales By SKU Value].[Store Stock] = '' THEN '0' 
       WHEN [Sales By SKU Value].[Store Stock] IS NOT NULL THEN [Sales By SKU Value].[Store Stock] 
    END AS [Store Stock], 
    CASE 
        WHEN [Sales By SKU Value].[On Order] IS NULL THEN '0' 
        WHEN [Sales By SKU Value].[On Order] = '' THEN '0' 
        WHEN [Sales By SKU Value].[On Order] IS NOT NULL THEN [Sales By SKU Value].[On Order] 
    END AS [On Order], 
    CASE 
        WHEN [Sales By SKU Value].[Total Stock] IS NULL THEN '0' 
        WHEN [Sales By SKU Value].[Total Stock] = '' THEN '0' 
        WHEN [Sales By SKU Value].[Total Stock] IS NOT NULL THEN [Sales By SKU Value].[Total Stock] 
    END AS [Total Stock], 
    CASE 
       WHEN [Sales By SKU Value].[No Of Stores] IS NULL THEN '0' 
       WHEN [Sales By SKU Value].[No Of Stores] = '' THEN '0' 
       WHEN [Sales By SKU Value].[No Of Stores] IS NOT NULL THEN [Sales By SKU Value].[No Of Stores] 
    END AS [Number of Stores], 
    CASE 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'jan' THEN [Sales By SKU Units].[Jan] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'feb' THEN [Sales By SKU Units].[Feb] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'mar' THEN [Sales By SKU Units].[March] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'apr' THEN [Sales By SKU Units].[April] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'may' THEN [Sales By SKU Units].[May] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'jun' THEN [Sales By SKU Units].[June] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'jul' THEN [Sales By SKU Units].[July] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'aug' THEN [Sales By SKU Units].[August] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'sep' THEN [Sales By SKU Units].[September] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'oct' THEN [Sales By SKU Units].[October] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'nov' THEN [Sales By SKU Units].[November] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'dec' THEN [Sales By SKU Units].[December] 
       ELSE '0' 
    END AS [Sales Last Month], 
    dbo.RangeLists.pnum AS Model, 
    CASE 
        WHEN [Sales By SKU Units].[Year] = '2016' 
           THEN ISNULL([Sales By SKU Units].[Jan], 0) + ISNULL([Sales By SKU Units].[Feb], 0) + 
                ISNULL([Sales By SKU Units].[March], 0) + ISNULL([Sales By SKU Units].[April], 0) + 
                ISNULL([Sales By SKU Units].[May], 0) + ISNULL([Sales By SKU Units].[June], 0) + 
                ISNULL([Sales By SKU Units].[July], 0) + ISNULL([Sales By SKU Units].[August], 0) + 
                ISNULL([Sales By SKU Units].[September], 0) + ISNULL([Sales By SKU Units].[October], 0) + 
                ISNULL([Sales By SKU Units].[November], 0) + ISNULL([Sales By SKU Units].[December], 0) 
           ELSE '0' 
    END AS [SALES LY], 
    CASE 
       WHEN [Sales By SKU Units].[Year] = '2017' 
          THEN ISNULL([Sales By SKU Units].[Jan], 0) + ISNULL([Sales By SKU Units].[Feb], 0) + 
               ISNULL([Sales By SKU Units].[March], 0) + ISNULL([Sales By SKU Units].[April], 0) + 
               ISNULL([Sales By SKU Units].[May], 0) + ISNULL([Sales By SKU Units].[June], 0) + 
               ISNULL([Sales By SKU Units].[July], 0) + ISNULL([Sales By SKU Units].[August], 0) + 
               ISNULL([Sales By SKU Units].[September], 0) + ISNULL([Sales By SKU Units].[October], 0) + 
               ISNULL([Sales By SKU Units].[November], 0) + ISNULL([Sales By SKU Units].[December], 0) 
          ELSE '0' 
    END AS [SALES TY], 
    dbo.[Sales By SKU Units].Year
FROM
    dbo.[Sales By SKU Units] 
INNER JOIN
    dbo.[Sales By SKU Value] ON dbo.[Sales By SKU Units].Year = dbo.[Sales By SKU Value].Year 
                             AND dbo.[Sales By SKU Units].SKU = dbo.[Sales By SKU Value].SKU 
                             AND dbo.[Sales By SKU Units].[Customer Account Number] = dbo.[Sales By SKU Value].[Customer Account Number] 
INNER JOIN
    dbo.RangeLists ON dbo.[Sales By SKU Value].SKU = dbo.RangeLists.CustProdRef

我知道我需要 GROUP BY 或 OVER(分区依据)，但我不知道如何将它们应用到我当前的查询中。

Answer 1

我假设您正在寻找 SALES 字段的总和，在这种情况下您需要 SUM() 进行分组，如下所示:

select 
    ACC
    , sum(SALES_LY) as SALES_LY
    , sum(SALES_TY) as SALES_TY
    , null as [YEAR]
from 
    ([insert your current query here])
    group by ACC

如果您想要最大值，您只需使用 MAX() 而不是 SUM()