为所有给定的 ID 选择一个月内的最后一个值 [英] Select last value in a month for all given IDs
本文介绍了为所有给定的 ID 选择一个月内的最后一个值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有 2 个表,一个包含仪表 ID,另一个包含第一个表中某些仪表的测量值.这是表结构:
I have 2 tables, one containing meter IDs, and another containing measurements for some of the meters in the first table. This is the table structure:
仪表配置:
- MeterID (int)
- MeterNumber (char[16])
- 类型 (char[25])
阅读:
- MeterID (int)
- 日期(日期时间)
- 值(数字(18,6))
我需要获取每个仪表的给定时间段的最后读数(及其日期)以及仪表编号.我设法在 T-SQL 中做到了这一点,尽管我对使用此查询的方式并不特别满意:
I need to get the last reading (and its date) from a given period for each meter, as well as the meter number. I managed to do this in T-SQL, although I'm not particularly pleased with the way I did it using this query:
select distinct
cfg.MeterNumber,
(select top 1 r.Date from Readings as r where r.Date between @startdate and @endDate and r.MeterID = cfg.MeterID order by r.Date desc) as Date,
(select top 1 r.Value from Readings as r where r.Date between @startdate and @endDate and r.MeterID = cfg.MeterID order by r.Date desc) as Value
from
MeterConfig cfg, Readings r1
where cfg.MeterID = r1.MeterID and r1.Date between @startdate and @endDate;
我怎样才能更有效地做到这一点?
How can I do this more efficiently?
推荐答案
WITH CTE AS (
SELECT mc.MeterID, Date, Value, ROW_NUMBER() OVER (PARTITION BY mc.MeterID ORDER BY Date DESC) as Rank
FROM MeterConfig mc
INNER JOIN Readings rd
ON mc.MeterID = rd.MeterID
WHERE rd.Date BETWEEN @startdate AND @endDate)
SELECT * FROM CTE WHERE Rank = 1
这篇关于为所有给定的 ID 选择一个月内的最后一个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文