Início / dba / Perguntas / 35988
Accepted
Nate
Asked: 2013-03-06 07:49:43 +0800 CST

Precisa de uma maneira de consultar uma tabela e JOIN com o registro TOP 1 relacionado de outra tabela

  • 772

Na sequência desta pergunta , gostaria de saber se existe uma maneira melhor e/ou mais eficiente de coletar os dados em questão.

Conforme declarado, esta consulta me retorna 95% dos dados de que preciso -

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';

Existe uma maneira de obter o mesmo resultado (para cada dispositivo, selecione o TOP Heartbeat ordenado DESC por DateEntered), mas também selecione a linha inteira da [Heartbeats]tabela? No momento, só recebo o DateTimepara essa linha.

Se eu incluir as colunas adicionais na GROUP BYcláusula, posso adicioná-las ao select; mas então recebo várias linhas por [Devices]linha que não quero. Parece estranho, mas o que eu basicamente quero fazer é fazer uma consulta [Devices]e, em seguida, fazer uma for...eachsobre esse conjunto e adicionar a [Heartbeats]linha superior para essa [Devices]linha. Isso é possível?

atualização Esta é a estrutura da tabela Heartbeats:

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 respostas

  1. Best Answer

    Você pode fazer isso facilmente com OUTER APPLY(se estiver em 2005 ou mais recente). Observe que pode haver maneiras de alcançar o resultado com melhor desempenho, como usar ROW_NUMBER()- verifique os planos de execução em caso de dúvida. Além disso, SELECT *é preguiçoso e desaconselhável; Estou fazendo isso aqui apenas para fins ilustrativos e porque não conheço a estrutura real da tabela 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'

    Consulte os livros on-line para obter os pontos mais sutis de OUTER APPLYvs. CROSS APPLY(é muito parecido com OUTER JOINvs. INNER JOIN). Sempre foi uma dor de cabeça fazer consultas como essa no SQL Server 2000, onde você não tinha OUTER/CROSS APPLY ou a ROW_NUMBER()função.

    • 6

  2. Você pode tentar usar este tipo de "autojunção":

    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

relate perguntas