sql - SQL - 基于最大值加入查询
问题描述
我正在尝试将具有成本数据的 CTE 加入到具有现有库存数据的另一个 CTE。我试图从成本数据 CTE (MAUCPvt) 中提取主要的“现有”CTE，这将是每个 WVG 和项目代码的最新日期和最新序列。我附上了我得到的结果的图像，以及所需的输出。我相信这是子查询中的内容，因为我非常接近获得所需的输出，但在这种情况下我想忽略该项目。当我删除 select max(project) 时，我得到“子查询返回超过 1 个值”。这是我的查询：
With [MAUCPvt] as
(Select [Item], [Proj], [WVG], [Trans Date], [Warehouse], [Seq],
isnull([MC00.010],0) as 'MC00.010', isnull([OC00.000],0) as 'OC00.000', isnull([GC00.025],0) as 'GC00.025',
isnull([MC00.010],0) + isnull([OC00.000],0) + isnull([GC00.025],0) as 'TotalCost'
from
(
Select ltrim(w136.t_item) as 'Item', w136.t_cprj as 'Proj', w136.t_wvgr as 'WVG', w136.t_trdt as 'Trans Date',
w136.t_cwar as 'Warehouse', w136.t_seqn as 'Seq', w137.t_mauc_1 as 'MAUC', w137.t_cpcp as 'CostC' from twhina136100 w136
join twhina137100 w137 on w137.t_cprj = w136.t_cprj
and w137.t_item = w136.t_item
and w137.t_trdt = w136.t_trdt
and w137.t_seqn = w136.t_seqn ) S
Pivot (SUM([MAUC]) for [CostC] in ([MC00.010],[OC00.000],[GC00.025]) ) PVT
)
, [OnHand] as
(Select ltrim(w260.t_item) as 'Part#', ITM.t_dsca as 'Part_Desc', w260.t_cwar as 'Warehouse', w200.t_wvgr as 'WVG',
w260.t_cprj as 'ProjPeg', w260.t_cact as 'ProjAct',
(w260.t_qhnd - w260.t_qnhd) as 'On Hand' ,
STD.t_ecpr_1 as 'STD_COST'
from twhwmd260100 w260
join twhwmd200100 w200 on w200.t_cwar = w260.t_cwar
join tticpr007100 STD on STD.t_item = w260.t_item
join ttcibd001100 ITM on ITM.t_item = w260.t_item
--left join twhina135100 a135 on a135.t_wvgr = w200.t_wvgr and a135.t_cprj = w260.t_cprj and a135.t_item = w260.t_item
where w260.t_qhnd > 0 )
Select O.*,
(Select m.TotalCost
from MAUCPvt m
where m.Item = O.Part# and
m.WVG = O.WVG and
m.[Trans Date] = (Select MAX(mp.[Trans Date]) from MAUCPvt mp
where mp.Item = O.Part# and
mp.Proj = O.ProjPeg and
mp.WVG = O.WVG)
and
m.Seq = (Select MIN(mp.[Seq]) from MAUCPvt mp
where mp.Item = O.Part# and
mp.Proj = O.ProjPeg and
mp.WVG = O.WVG)
and
m.Proj = (Select max(mp.Proj) from MAUCPvt mp
where mp.Item = O.Part# and
mp.Proj = O.ProjPeg and
mp.WVG = O.WVG
)
) as 'MAUC_WVG'
from OnHand O
Where Part# = '140B00103'
Order by Part#, Warehouse
--CTE 1
Item Proj WVG Trans Date Warehouse Seq MC00.010 OC00.000 GC00.025 TotalCost 140B00103 WVG002 12/24/2017 ZLOG-B 1 42.63 0 0 42.63 140B00103 WVG002 2/14/2018 BSHIP 1 40.25 0 0 40.25 140B00103 WVG002 6/1/2018 BCSHIP 1 40.25 0 0 40.25 140B00103 WVG002 10/17/2018 SCRAPD 1 40.25 0 0 40.25 140B00103 WVG002 10/22/2018 SCRAPD 1 40.25 0 0 40.25 140B00103 Z160029 WVG002 12/27/2017 ZLOG-B 1 42.63 0 0 42.63
--CTE2
Part# Part_Desc Warehouse WVG ProjPeg ProjAct On Hand STD_COST
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, B WVG001 48 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, BC WVG001 1 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, D WVG001 52 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, DC WVG001 1 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, FSR01 WVG001 1 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, SCRAPD WVG002 2 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, ZLOG-B WVG002 Z160029 1001 2 40.25
--我的输出
Part# Part_Desc Warehouse WVG ProjPeg ProjAct On Hand STD_COST MAUC_WVG
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, B WVG001 48 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, BC WVG001 1 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, D WVG001 52 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, DC WVG001 1 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, FSR01 WVG001 1 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, SCRAPD WVG002 2 40.25 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, ZLOG-B WVG002 Z160029 1001 2 40.25 42.63
--期望输出
Part# Part_Desc Warehouse WVG ProjPeg ProjAct On Hand STD_COST MAUC_WVG
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, B WVG001 48 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, BC WVG001 1 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, D WVG001 52 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, DC WVG001 1 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, FSR01 WVG001 1 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, SCRAPD WVG002 2 40.25 40.25
140B00103 POT,10000 OHM,5%,2W,WIREWOUND, ZLOG-B WVG002 Z160029 1001 2 40.25 40.25
编辑:: 还尝试使用 ROW_NUMBER()，这会产生预期的结果，但由于 ROWNUMBER 索引扫描，总共需要超过 12 分钟才能运行。必须有一种更简单的方法来实现这一点。
With [MAUCPvt] as
(Select [Item], [Proj], [WVG], [Trans Date], [Warehouse], [Seq],
ROW_NUMBER() OVER (Partition by [Item] Order by [Item], [Trans Date]) as 'RN',
isnull([MC00.010],0) as 'MC00.010', isnull([OC00.000],0) as 'OC00.000', isnull([GC00.025],0) as 'GC00.025',
isnull([MC00.010],0) + isnull([OC00.000],0) + isnull([GC00.025],0) as 'TotalCost'
from
(
Select ltrim(w136.t_item) as 'Item', w136.t_cprj as 'Proj', w136.t_wvgr as 'WVG', w136.t_trdt as 'Trans Date',
w136.t_cwar as 'Warehouse', w136.t_seqn as 'Seq', w137.t_mauc_1 as 'MAUC', w137.t_cpcp as 'CostC'
from twhina136100 w136
join twhina137100 w137 on w137.t_cprj = w136.t_cprj
and w137.t_item = w136.t_item
and w137.t_trdt = w136.t_trdt
and w137.t_seqn = w136.t_seqn ) S
Pivot (SUM([MAUC]) for [CostC] in ([MC00.010],[OC00.000],[GC00.025])
) PVT
)
,
[OnHand] as
(Select ltrim(w260.t_item) as 'Part#', ITM.t_dsca as 'Part_Desc', w260.t_cwar as 'Warehouse', w200.t_wvgr as 'WVG',
w260.t_cprj as 'ProjPeg', w260.t_cact as 'ProjAct',
(w260.t_qhnd - w260.t_qnhd) as 'On Hand' ,
STD.t_ecpr_1 as 'STD_COST'
from twhwmd260100 w260
join twhwmd200100 w200 on w200.t_cwar = w260.t_cwar
join tticpr007100 STD on STD.t_item = w260.t_item
join ttcibd001100 ITM on ITM.t_item = w260.t_item
--left join twhina135100 a135 on a135.t_wvgr = w200.t_wvgr and a135.t_cprj = w260.t_cprj and a135.t_item = w260.t_item
where w260.t_qhnd > 0 )
Select top 1000 O.*,
(Select m.TotalCost
from MAUCPvt m
where m.Item = O.Part# and
m.WVG = O.WVG and
--m.Proj = O.ProjPeg and
m.[RN] = (Select MAX(mp.RN) from MAUCPvt mp
where mp.Item = O.Part# and
--mp.Proj = O.ProjPeg and
mp.WVG = O.WVG)
) as 'MAUC_WVG'
from [OnHand] O
--Where Part# = '40980-15'
Order by Part#, Warehouse
解决方案
弄清楚了。通过在子查询中添加前 1 个能够获得所需的结果，并且只需 15 秒左右，而使用 ROW_NUMBER() 几乎需要 15 分钟。
With [MAUCPvt] as
(Select [Item], [WVG], [Trans Date],
isnull([MC00.010],0) as 'MC00.010', isnull([OC00.000],0) as 'OC00.000', isnull([GC00.025],0) as 'GC00.025',
isnull([MC00.010],0) + isnull([OC00.000],0) + isnull([GC00.025],0) as 'TotalCost'
from
(
Select ltrim(w136.t_item) as 'Item', w136.t_cprj as 'Proj', w136.t_wvgr as 'WVG', w136.t_trdt as 'Trans Date',
w136.t_cwar as 'Warehouse', w136.t_seqn as 'Seq', w137.t_mauc_1 as 'MAUC', w137.t_cpcp as 'CostC'
from twhina136100 w136
join twhina137100 w137 on w137.t_cprj = w136.t_cprj
and w137.t_item = w136.t_item
and w137.t_trdt = w136.t_trdt
and w137.t_seqn = w136.t_seqn ) S
Pivot (SUM([MAUC]) for [CostC] in ([MC00.010],[OC00.000],[GC00.025])
) PVT )
,
[OnHand] as
(Select ltrim(w260.t_item) as 'Part#', ITM.t_dsca as 'Part_Desc', w260.t_cwar as 'Warehouse', w200.t_wvgr as 'WVG',
w260.t_cprj as 'ProjPeg', w260.t_cact as 'ProjAct',
(w260.t_qhnd - w260.t_qnhd) as 'On Hand' ,
STD.t_ecpr_1 as 'STD_COST'
from twhwmd260100 w260
join twhwmd200100 w200 on w200.t_cwar = w260.t_cwar
join tticpr007100 STD on STD.t_item = w260.t_item
join ttcibd001100 ITM on ITM.t_item = w260.t_item
--left join twhina135100 a135 on a135.t_wvgr = w200.t_wvgr and a135.t_cprj = w260.t_cprj and a135.t_item = w260.t_item
where w260.t_qhnd > 0 )
Select O.*,
(Select TOP 1 m.TotalCost
from MAUCPvt m
where m.Item = O.Part# and
m.WVG = O.WVG
-- and m.[Seq] = (Select MIN(mp.Seq) from MAUCPvt mp
-- where mp.Item = O.Part# and
-- mp.WVG = O.WVG)
and m.[Trans Date] = (Select MAX(mp.[Trans Date]) from MAUCPvt mp
where mp.Item = O.Part# and
mp.WVG = O.WVG
) ) as 'MAUC_WVG'
from [OnHand] O
--Where Part# = '019612-2074'
Order by Part# asc , Warehouse asc
推荐阅读
- html - 使用输入调整内容大小
- android - 从 NFC 标签启动 Android Instant App
- python - 熊猫中的e +数字含义
- c# - 无法将 ValueObject 与另一个表列一起设置为数据库索引
- r - 从果蝇基因符号到人类基因符号的 ID 转换
- react-native - 无法在 React Native 中导航
- powershell - 如何从 Powershell 获取 SharePoint 中的元数据（列值）
- java - 在 Spring Boot 中使用一个 CacheManager 设置 TTL 缓存不同的数据
- django - 如何在 POST 中返回 DRF 表单数据
- c# - Gridview 事件订阅