时间戳

首页 > 解决方案 > 将每位客户的销售额与去年的销售额进行比较

sql-server - 将每位客户的销售额与去年的销售额进行比较

问题描述

我有 2 个查询，我使用 UNION 来比较 2 年内每位客户的销售价值。下面返回 2 列，一列用于组织，一列用于销售价值/客户。但是 2 年的拆分行不通。似乎将所有值都拉到一列中，而不是 2018 年的列和 2019 年的列。也许我应该使用连接或子查询？

返回结果类似于：

Organization    Last Year      Previous year
CUS                 12000        160000
etc

SELECT Top 25
    FORMAT(SUM(dbo.ARInvoices.arpFullInvoiceSubtotalBase), 'C2') AS "previousyear ", dbo.Organizations.cmoOrganizationID AS "Organization"
FROM (dbo.ARInvoices
LEFT OUTER JOIN dbo.Organizations ON dbo.ARInvoices.arpCustomerOrganizationID = dbo.Organizations.cmoOrganizationID)  
WHERE  YEAR(arpInvoiceDate) = year(DATEADD(year, -2, getdate()))AND arpInvoiceType = 1
GROUP BY dbo.Organizations.cmoOrganizationID

UNION

SELECT Top 25
    FORMAT(SUM(dbo.ARInvoices.arpFullInvoiceSubtotalBase), 'C2') AS "Lastyear", dbo.Organizations.cmoOrganizationID AS "Organization"
FROM (dbo.ARInvoices
LEFT OUTER JOIN dbo.Organizations ON dbo.ARInvoices.arpCustomerOrganizationID = dbo.Organizations.cmoOrganizationID)   
WHERE  YEAR(arpInvoiceDate) = year(DATEADD(year, -1, getdate()))AND arpInvoiceType = 1
GROUP BY dbo.Organizations.cmoOrganizationID'

标签： sql-servertsqlgroup-bysumunion

解决方案

你可以做条件聚合

select
    sum(case 
        when arpInvoiceDate >= datefromparts(year(getdate()) - 2, 1, 1) 
             and arpInvoiceDate < datefromparts(year(getdate()) - 1, 1, 1) 
        then i.arpFullInvoiceSubtotalBase
    end) as previousYear,
    sum(case 
        when arpInvoiceDate >= datefromparts(year(getdate()) - 1, 1, 1)
        then i.arpFullInvoiceSubtotalBase
    end) as lastYear
from dbo.ARInvoices i 
inner join dbo.Organizations o ON i.arpCustomerOrganizationID = o.cmoOrganizationID
where arpInvoiceType = 1 and arpInvoiceDate >= datefromparts(year(getdate()) - 2, 1, 1)
group by o.cmoOrganizationID

笔记：

  • 我使用INNER JOIN而不是LEFT JOIN因为左表的列之一在GROUP BY子句中使用

  • 表别名确实使查询更短且更易于阅读

  • 我更改了日期过滤的实现，因此表列上没有使用日期函数（这使查询更有效）

推荐阅读