sql - 基于 SELECT DISTINCT ON 3 列但有 1 个额外列创建 TEMP 表
问题描述
我需要在其中制作一个临时文件:
Partcode, MutationDate, MovementType, 数量
每个零件代码的每个运动类型都有多个突变日期(最多可能有 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
这就是我想要的,但我也需要获得数量。
解决方案
use row_number()
window function
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
推荐阅读
- amazon-ecs - Amazon ECS fargate:静态 IP 地址
- loops - Ansible 结合“复杂”字典
- html - HTML等于边框宽度并强制换行
- laravel - Laravel 应用程序版本控制
- angular - 角度清除间隔不停止间隔
- wordpress - 根据自定义字段的值动态更改自定义帖子类型和表单的 URL
- ngrx - 如何在赛普拉斯 e2e 测试之前设置 NGRX 状态?
- c++ - transform_reduce & 摆脱 for 循环
- tensorflow - TensorFlow 内部 Python 错误:找不到模块
- c# - 销毁预制件后如何实例化它们?