需要一種查詢表的方法，並將其與另一個表中的 TOP 1 相關記錄連接起來

作為對這個問題的跟進，我想知道是否有更好和/或更有效的方法來收集相關數據。

如前所述，這個查詢確實返回了我需要的 95% 的數據——

SELECT dv.Name
     ,MAX(hb.[DateEntered]) as DE
FROM 
   [Devices] as dv
   INNER JOIN 
   [Heartbeats] as hb ON hb.DeviceID = dv.ID
GROUP BY dv.Name
HAVING MAX(hb.[DateEntered]) < '20130304';

有沒有辦法實現相同的結果（對於每個設備，選擇按 DateEntered 排序的 TOP Heartbeat 排序 DESC）但也從表中選擇整行[Heartbeats]？現在，我只得到那DateTime一行。

如果我在子句中包含其他列，則GROUP BY可以將它們添加到選擇中；但後來我每[Devices]行得到多行，這是我不想要的。這聽起來很奇怪，但我基本上想做的是做一個查詢[Devices]，然後for...each在那個集合上做一個，並為該行添加第一[Heartbeats]行[Devices]。那可能嗎？

更新 這是心跳表的結構：

CREATE TABLE [dbo].[Heartbeats](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [DeviceID] [int] NOT NULL,
   [IsFMSFMPUp] [bit] NOT NULL,
   [IsFMSWebUp] [bit] NOT NULL,
   [IsPingUp] [bit] NOT NULL,
   [DateEntered] [datetime] NOT NULL,
CONSTRAINT [PK_Heartbeats] PRIMARY KEY CLUSTERED 
(
   [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats] 
(
   [DateEntered] ASC,
   [DeviceID] ASC
)
INCLUDE ( [ID],
[IsFMSFMPUp],
[IsFMSWebUp],
[IsPingUp]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HeartbeatDeviceIndex] ON [dbo].[Heartbeats] 
(
   [DeviceID] ASC
)
INCLUDE ( [ID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  Default [DF_Heartbeats_DateEntered]    Script Date: 03/05/2013 10:45:45 ******/
ALTER TABLE [dbo].[Heartbeats] ADD  CONSTRAINT [DF_Heartbeats_DateEntered]  DEFAULT (getdate()) FOR [DateEntered]
GO
/****** Object:  ForeignKey [FK_Heartbeats_Devices]    Script Date: 03/05/2013 10:45:45 ******/
ALTER TABLE [dbo].[Heartbeats]  WITH CHECK ADD  CONSTRAINT [FK_Heartbeats_Devices] FOREIGN KEY([DeviceID])
REFERENCES [dbo].[Devices] ([ID])
GO
ALTER TABLE [dbo].[Heartbeats] CHECK CONSTRAINT [FK_Heartbeats_Devices]
GO

你可以很容易地做到這一點OUTER APPLY（如果你在 2005 年或更高版本）。請注意，可能有更好的執行方式來實現結果，例如使用ROW_NUMBER()- 如果有疑問，請檢查執行計劃。此外，SELECT *是懶惰和不可取的；我在這裡只是為了說明的目的，因為我不知道 Heartbeats 表的真實結構。

SELECT
   dv.Name,
   hb.*
FROM [Devices] as dv
   OUTER APPLY (
       SELECT TOP 1 *
       FROM Heartbeats
       WHERE Heartbeats.DeviceID = dv.ID
       ORDER BY DateEntered DESC
   ) hb
WHERE ISNULL(hb.DateEntered, '1900-01-01T00:00') < '2013-03-04T00:00'

OUTER APPLY有關vs.的詳細資訊，請參閱聯機叢書CROSS APPLY（它很像OUTER JOINvs. INNER JOIN）。在 SQL Server 2000 中進行這樣的查詢總是很痛苦，因為您沒有OUTER/CROSS APPLY 或沒有該ROW_NUMBER()功能。

引用自：https://dba.stackexchange.com/questions/35988