主页 / dba / 问题 / 35988
Accepted
Nate
Asked: 2013-03-06 07:49:43 +0800 CST

需要一种查询表的方法，并将其与另一个表中的 TOP 1 相关记录连接起来

  • 772

作为对这个问题的跟进，我想知道是否有更好和/或更有效的方法来收集相关数据。

如前所述，这个查询确实返回了我需要的 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
sql-server sql-server-2008

2 个回答

  1. Best Answer

    你可以很容易地做到这一点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()功能。

    • 6

  2. 您可以尝试使用这种类型的“自加入”：

    select dat.*
from 
(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') grp 
    inner join 
(SELECT dv.Name, hb.*
FROM [Devices] as dv
    INNER JOIN [Heartbeats] as hb ON hb.DeviceID = dv.ID) dat 
        on grp.name = dat.name and grp.DE = dat.[DateEntered]
    • 0

相关问题