SQL 内连接与前 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16262312/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL inner join with top 1
提问by kosnkov
Hi I need to get table of latest known value one for each input.
嗨，我需要为每个输入获取最新的已知值表。
I started with this
我从这个开始
SELECT [MeterReadingId]
,[TimeStampUtc]
,[Val]
,[MeterReading].[InverterInputId]
,[Status]
FROM [Project].[dbo].[MeterReading]
inner join InverterInput on [MeterReading].InverterInputId = [InverterInput].InverterInputId
inner join Inverter on [InverterInput].InverterId = [Inverter].InverterId
where [InverterInput].InputName = 'DayYield' and [Inverter].PlantId = 1
off course now i got all values which belong to inputs of name 'DayYield and plantId =1 My question is how can got table only of those values which have latest [TimeStampUtc]
当然，现在我得到了属于名称“DayYield 和 plantId =1”的输入的所有值我的问题是如何只得到那些具有最新 [TimeStampUtc] 值的表
Other words: get all those [Val] which belong to input of name 'DayYield and plantId =1 and are last inserted into table.
换句话说：获取所有属于名称'DayYield 和plantId =1 的输入并最后插入表中的所有[Val]。
回答by Andomar
One option is a cross apply, like:
一种选择是 a cross apply，例如：
select *
from Devices d
cross apply
(
select top 1 *
from Measurements m
where m.device_id = d.id
order by
m.timestamp desc
) m
回答by Mahmoud Gamal
For this, you can use the ranking functions, ROW_NUMBER()for example. Something like this:
为此，您可以使用排名函数，ROW_NUMBER()例如。像这样的东西：
WITH CTE
AS
(
SELECT [MeterReadingId]
,[TimeStampUtc]
,[Val]
,m.[InverterInputId]
,[Status]
,ROW_NUMBER() OVER(PARTITION BY m.InverterInputId
ORDER BY m.[TimeStampUtc] DESC) AS RN
FROM [Project].[dbo].[MeterReading] AS m
inner join InverterInput AS ii on m.InverterInputId = ii.InverterInputId
inner join Inverter AS i on ii.InverterId = i.InverterId
where ii.InputName = 'DayYield' and i.PlantId = 1
)
SELECT *
FROM CTE
WHERE RN = 1;
Using ORDER BY m.[TimeStampUtc] DESCwill give you the latest [TimeStampUtc].
使用ORDER BY m.[TimeStampUtc] DESC将为您提供最新的[TimeStampUtc].
Note that:I don't know about the tables' structures you are using, but you might need to use another column to group by in the PARTITION BYclause instead of MeterReadingId.
请注意：我不知道您正在使用的表的结构，但您可能需要在PARTITION BY子句中使用另一列而不是MeterReadingId.
回答by Sven Havgen
I found the cross apply function far easier to read. It also means you maintain the basic structure of a normal query.
我发现交叉应用功能更容易阅读。这也意味着您维护普通查询的基本结构。
update cc
set cc.OutCC = oocd.CC, cc.Outcel = oocd.cel,
cc.Outcd = oocd.cd, cc.Outrn = oocd.rn,
cc.outidate = oocd.idate, cc.outtdate = oocd.tdate
from @CbC cc
cross apply (
select top 1 * from @OutC ocd
where substring(ocd.rn,LEN(ocd.rn) - 9,9) = substring(cc.rn,LEN(cc.rn) - 9,9) and (ocd.cdate between cc.idate and DATEADD(hh,1,cc.idate)) and ocd.cdate < cc.tdate and ocd.cel like '%' + cc.line + '%' collate database_default
order by ocd.idate
) oocd
