SQL Server:根据同一列的不同数据过滤出结果

Question

我在创建查询时遇到了一个问题。

表是这样的:

LineId|Product|orderedQty|PickedQty|Operator|TimeFinised
------+-------+----------+---------+--------+-----------
1|   1234|        60|       40|    Joe |     125546      
2|   1234|        60|       18|  Benny |     120025

我想创建一个查询，将其转换为一个结果，其中pickkedqty被总结。我还希望只显示根据列Timefinished挑选项目的最后一个操作符。

结果应该是这样的:

Product|orderedQty|PickedQty|Operator
1234|        60|       58|     Joe

查询结果:

select
product,
orderedQty,
cast(sum(m.PickedQty) as decimal(18,1)) as PickedQty,
from 
Lines
group by 
product, orderedQty

总结PickedQty很容易，但我不知道如何过滤掉第一个操作符。

有谁知道我怎么才能做到这一点吗?

Answer 1

条件聚合可能有帮助:

select
product,
orderedQty,
cast(sum(m.PickedQty) as decimal(18,1)) as PickedQty,
MAX (CASE WHEN Rn = 1 THEN Operator END) AS Operator
from (
SELECT 
*,
ROW_NUMBER() OVER (PARTITION BY product, orderedQty ORDER BY TimeFinised DESC) AS Rn
FROM (VALUES
(1, 1234, 60, 40, 'Joe',   125546),      
(2, 1234, 60, 18, 'Benny', 120025)
) v (LineId, Product, orderedQty, PickedQty, Operator, TimeFinised)   
) m
group by 
product, orderedQty

结果:

product orderedQty  PickedQty   Operator
1234    60          58.0        Joe