求和列A而不求和列B取决于列C-SQL Server

Question

我正试图设计一种方法，根据C列(版本(的值，始终对a列(单位(求和，有时对B列(价格(求和。我想在陈述时使用一个案例，但我遇到的问题围绕着对Price的依赖，Price有一种分层系统。如果可用，我必须取当前版本的价格值，然后取最近版本的价格，如果当前版本和最近版本都不可用，我需要取旧版本的价格。层列表显示为"当前">"最近">"旧"。现在数据在SQL Server中。

另一个问题是，如果同一产品的两个更高版本层的价格不同，则返回的价格应与最旧版本的价格不同。例如，如果同一产品的三行的Prices分别为50、50和10，Versions分别为Old、Recent和Recent，则返回的Price将为10。

因此，如果起始数据看起来像这样：

--------------------------------------------------
|  Units      |  Price  |  Version    | Product  |
--------------------------------------------------
|  105        |  50     |  Old        | Bear     |
--------------------------------------------------
|  100        |  100    |  Recent     | Bear     |
--------------------------------------------------
|  100        |  150    |  Current    | Bear     |
--------------------------------------------------
|  97         |  50     |  Old        | Bear     |
--------------------------------------------------
|  67         |  50     |  Old        | Goose    |
--------------------------------------------------
|  28         |  50     |  Recent     | Goose    |
--------------------------------------------------
|  10         |  10     |  Recent     | Goose    |
--------------------------------------------------

数据的汇总版本如下所示：

--------------------------------------------------
|  Units      |  Price  |  Version    | Product  |
--------------------------------------------------
|  402        |  150    |  Current    | Bear     |
--------------------------------------------------
|  105        |  10     |  Recent     | Goose    |
--------------------------------------------------

我是SQL的新手，所以如果这是一个新手问题，我很抱歉。非常感谢您能提供的任何帮助。

Answer 1

另一个选项是将WITH TIES子句与Row_Number((一起使用

示例

Select Top 1 with ties 
Units = sum(Units) over (Partition By Product)
,Price
,Version
,Product
From  YourTable
Order By Row_Number() over (Partition By Product Order by case when Version='Old' then 3 when Version='Recent' then 2 else 1 end)

退货

Units   Price   Version   Product
402     150     Current   Bear
95      10      Recent    Goose

编辑-请求更新

这里，我们在CTE中使用lag((函数来确定价格的变化

Declare @YourTable Table ([Units] int,[Price] int,[Version] varchar(50),[Product] varchar(50))
Insert Into @YourTable Values 
(105,50,'Old','Bear')
,(100,100,'Recent','Bear')
,(100,150,'Current','Bear')
,(97,50,'Old','Bear')
,(67,50,'Old','Goose')
,(28,50,'Recent','Goose')
,(10,10,'Recent','Goose')
;with cte as (
Select Units = sum(Units) over (Partition By Product)
,Price
,Version
,Product
,PrevPrice = abs(Price-lag(Price,1) over (Partition By Product Order by case when Version='Old' then 3 when Version='Recent' then 2 else 1 end desc) )
From  @YourTable
)
Select top 1 with ties
Units 
,Price
,Version
,Product
From  cte
Order By Row_Number() over (Partition By Product Order by case when Version='Old' then 3 when Version='Recent' then 2 else 1 end ,PrevPrice desc) 

退货

Units   Price   Version Product
402     150     Current Bear
105     10      Recent  Goose

Answer 2

您可以使用条件聚合。诀窍是按优先级排序。一种方法使用row_number()和case:

select sum(units) as units,
max(case when seqnum = 1 then price end) as price,
max(case when seqnum = 1 then version end) as version,
product
from (select t.*,
row_number() over (partition by product
order by (case version when 'old' then 3 when 'recent' then 2 when 'current' then 1 else 4 end)
) as seqnum
from t
) t
group by product;