基于对3列的SELECT DISTINCT创建临时表，但增加了1列

Question

我需要制作一个临时文件，其中包含：

零件代码，变动日期，移动类型，数量

每个零件代码的每个移动类型都有多个变化日期(最多可以有9个移动类型(我需要获得每个零件代码的每个移动类型的最后一个变化日期以及相应的数量。

部件代码为003307 的示例

003307 2018-05-31 1 -100
003307 2018-06-11 2 -33
003307 2018-04-25 3 +25

依此类推，用于所有9种移动类型。

到目前为止我得到了什么：

create table #LMUT(
MutationDate T_Date
,PartCode T_Code_Part
,CumInvQty T_Quantum_Qty10_3
,MovementType T_Type_PMOverInvt
)
insert #LMUT(
MutationDate,
Partcode,
CumInvQty,
MovementType)
SELECT
cast (max(MOV.MutationDate) as date)
,MOV.PartCode
,INV.MutationQty
,INV.PMOverInvtType
FROM dbo.T_PartMovementMain as MOV
inner join dbo.T_PartMovementOverInvt as INV on
INV.PMMainCode=MOV.PMMainCode
WHERE
MOV.PartMovementType = 1
group by MOV.PartCode,INV.PMOverInvtType,INV.MutationQty,MOV.MutationDate
SELECT * FROM #LMUT where partcode='003007'
drop table #LMUT

结果在：

2016-12-06 00:00:00.000 003007 -24.000 2
2016-09-29 00:00:00.000 003007 -24.000 2
2016-11-09 00:00:00.000 003007 -24.000 2
2016-11-22 00:00:00.000 003007 -24.000 2
2016-10-26 00:00:00.000 003007 -24.000 2
2016-09-12 00:00:00.000 003007 -42.000 2
2016-10-13 00:00:00.000 003007 -24.000 2
2016-12-03 00:00:00.000 003007 100.000 5
2017-01-12 00:00:00.000 003007 -48.000 2
2016-10-04 00:00:00.000 003007 306.000 7

不是我需要的，仍然有8次类型2

我还试过什么：

SELECT distinct MOV.Partcode,INV.PMOverInvtType,mov.MutationDate
FROM dbo.T_PartMovementMain as MOV
inner join dbo.T_PartMovementOverInvt as INV on
INV.PMMainCode=MOV.PMMainCode
WHERE
mov.MutationDate = (SELECT MAX (c.MutationDate) FROM
dbo.T_PartMovementMain as c
inner join dbo.T_PartMovementOverInvt as d on D.PMMainCode=c.PMMainCode
WHERE
C.PartMovementType = 1 AND
C.PartCode=mov.PartCode AND
D.PMMainCode = C.PMMainCode AND
D.PMOverInvtType=inv.PMOverInvtType                                     
)        
and MOV.PartMovementType = 1 and mov.partcode='003007'
order by MOV.Partcode,INV.PMOverInvtType

结果：

3007    2   2017-01-12 00:00:00.000
3007    5   2016-12-03 00:00:00.000
3007    7   2016-10-04 00:00:00.000

这是我想要的，但我也需要数量。

Answer 1

使用row_number()窗口函数

with cte as
(         SELECT  MOV.*,INV.*,
row_number() over(partition by INV.PMOverInvtType order by MOV.MutationDate desc)rn
FROM dbo.T_PartMovementMain as MOV
inner join dbo.T_PartMovementOverInvt as INV on
INV.PMMainCode=MOV.PMMainCode
) select cte.* from cte where rn=1

Answer 2

这样解决：

create table #LMUT(
PartCode     T_Code_Part
,MovementType T_Type_PMOverInvt
,MutationDate T_Date 
,CumInvQty    T_Quantum_Qty10_3
)
insert #LMUT(Partcode,MovementType,MutationDate,CumInvQty)
select Artikel,Type,Datum,Aant
from (
SELECT  MOV.Partcode as Artikel,INV.PMOverInvtType as Type,mov.MutationDate as Datum,INV.MutationQty as Aant,
row_number() over(partition by MOV.Partcode,INV.PMOverInvtType order by MOV.Partcode,INV.PMOverInvtType,MOV.MutationDate desc) rn
FROM dbo.T_PartMovementMain as MOV
inner join dbo.T_PartMovementOverInvt as INV on INV.PMMainCode=MOV.PMMainCode) cse
where rn=1
select * from #LMUT  order by Partcode  
drop table #LMUT