SQL Server-仅选择最新日期 [英] SQL Server - Only Select Latest Date

问题描述

RDBMS = Microsoft SQL Server

RDBMS = Microsoft SQL Server

我在一家制冷公司工作，我们希望做得更好，以跟踪每个库存地点购买的制冷剂瓶的成本.我正在尝试创建一个提取此信息的SQL查询，但是遇到了一些问题.对于每个库存地点，我想显示该库存地点最后一次购买制冷剂的成本.我想查看该地点购买特定制冷剂的最新记录.我尝试使用Max函数失败，而Row_Number函数却无法正常工作.任何帮助将不胜感激.

I work for a refrigeration company and we want to do a better job of tracking the cost bottles of refrigerant were bought at for each inventory location. I am trying to create a SQL Query that pulls this information but I am running into some issues. For each inventory location I want to display the last cost refrigerant was bought at for that inventory location.I want to see the latest date we have record of for this location purchasing a specific refrigerant. I have tried using the Max function unsuccessfully and the Row_Number function I have not been able to get work. Any help would be much appreciated.

请参阅下面的代码示例，我只想显示每个库存地点购买的R-22 30磅水罐的最新日期.

See below the code sample I am trying to only get to display the Latest Date each inventory location purchased R-22 30 pound jug.

select 
    lctn_id as Location,
    invntryitm_id as InventoryItemID,
    invntryitm_nme as InventoryItemName,
    prchseordrlst_dte_rqstd as DateRequested,
    prchseordrlst_unt_cst as UnitCost
from 
    invntryitm
join
    prchseordrlst on prchseordrlst.invntryitm_rn = invntryitm.invntryitm_rn
join 
    prchseordr on prchseordr.prchseordr_rn = prchseordrlst.prchseordr_rn
join
    lctn on lctn.lctn_rn = prchseordr.lctn_rn
where   
    invntryitm.invntryitm_nme ='REFRIGERANT R-22 30#'
    and lctn_obslte = 'N'
group by 
    lctn.lctn_id, invntryitm.invntryitm_id, invntryitm.invntryitm_nme, 
    prchseordrlst.prchseordrlst_unt_cst
order by 
    lctn_id

推荐答案

我认为分析/窗口化功能可以满足您的需求:

I think an analytic/windowing function would give you what you need:

with location_data as (
  select 
    lctn_id as Location,
    invntryitm_id as InventoryItemID,
    invntryitm_nme as InventoryItemName,
    prchseordrlst_dte_rqstd as DateRequested,
    prchseordrlst_unt_cst as UnitCost,
    max (prchseordrlst_dte_rqstd) over (partition by lctn_id) as max_date
  from
    invntryitm
    JOIN prchseordrlst on prchseordrlst.invntryitm_rn = invntryitm.invntryitm_rn
    JOIN prchseordr on prchseordr.prchseordr_rn = prchseordrlst.prchseordr_rn
    JOIN lctn on lctn.lctn_rn = prchseordr.lctn_rn
  where
    invntryitm.invntryitm_nme ='REFRIGERANT R-22 30#' and
    lctn_obslte = 'N'
)
select *
from location_data
where max_date = DateRequested
order by Location

请记住，如果有平局，则两个location_id记录具有相同的日期，那么您将同时取回它们.如果这是一个问题，那么您可能希望使用row_number()而不是max():

Bear in mind that if there is a tie, two location_id records with the same date, then you will get both of them back. If this is an issue, then you probably want row_number() instead of max():

row_number() over (partition by lctn_id order by prchseordrlst_dte_rqstd desc) as rn

然后您会

where rn = 1

获得第一行

我之所以没有列出row_number()的原因是max是O(n)，并且如果您的数据中包含日期和时间，则可能足以满足您的需求.

The reason I didn't list row_number() first is that max is O(n), and if your data has dates and times, it may be sufficient for what you need.

这篇关于SQL Server-仅选择最新日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！