如何在SQL中获取另一行的开始和结束时间之间的开始和结束时间的行？

I'm using aspect-oriented programming techniques to create very fine-grained logging for a class library. Each method call will be logged.

我正在使用面向方面的编程技术为类库创建非常精细的日志记录。将记录每个方法调用。

I'm trying to develop a stacked bar chart with each call that comes from an instance of a class, with each sub-call occupying space in between the start and finish of the outer call to visually represent the time taken and aid in performance tuning. In order to do this, I need to identify which calls came from the same instance as a given call and lie between its start and end date, provided the CallGUID (primary key for the table). I know I need to double-reference the table, but I've been trying and the result set has the same Ticks for all the calls.

我正在尝试使用来自类实例的每个调用来开发堆叠条形图,每个子调用占用外部调用的开始和结束之间的空间,以直观地表示所花费的时间并帮助进行性能调整。为了做到这一点,我需要确定哪个来自同一个实例的调用作为给定的调用,并且在它的开始日期和结束日期之间,如果是CallGUID(表的主键)。我知道我需要对表进行双重引用,但我一直在尝试,结果集对所有调用都有相同的Ticks。

Schema:

USE [Skunkworks]
GO
/****** Object:  Table [dbo].[ClassMemberCall]    Script Date: 06/09/2009 15:38:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ClassMemberCall](
    [CallGUID] [uniqueidentifier] NOT NULL,
    [InstanceGUID] [uniqueidentifier] NOT NULL,
    [MemberGUID] [uniqueidentifier] NOT NULL,
    [StartedOn] [datetime] NOT NULL,
    [FinishedOn] [datetime] NULL,
    [SerializedResult] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ExceptionGUID] [uniqueidentifier] NULL,
    [SerializedException] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [StartedOnTick] [bigint] NOT NULL,
    [FinishedOnTick] [bigint] NULL

Data:

INSERT INTO [ClassMemberCall] ([CallGUID],[InstanceGUID],[MemberGUID],[StartedOn],[FinishedOn],[SerializedResult],
[ExceptionGUID],[SerializedException],
[StartedOnTick],[FinishedOnTick])
VALUES('1F45C171-910B-4F17-9286-3D8CDEB7A0C4','B1AE1340-1ED7-4C69-A964-49429D3F81B8','AF1249C9-7DE7-46BD-8042-9AFBD2DFAC15',
'Jun  9 2009  3:23:00:790PM','Jun  9 2009  3:23:00:837PM',NULL,'A4252B69-99FF-43F9-AA33-E8704478CD3E',
'Logging.Entities.ExceptionResult',633801577807903192,633801577808371951)
INSERT INTO [ClassMemberCall] ([CallGUID],[InstanceGUID],[MemberGUID],[StartedOn],
[FinishedOn],[SerializedResult],[ExceptionGUID],[SerializedException],
[StartedOnTick],[FinishedOnTick])VALUES('82989F3F-56F9-4F0A-9CBF-752FC07BA110','EBDF2013-6858-4DBB-AA8A-D1931FAF0BF8','55FE8E6C-9694-4DD1-9152-DD492102C00C','Jun  9 2009  3:23:00:713PM','Jun  9 2009  3:23:00:713PM',NULL,NULL,NULL,633801577807121927,633801577807121927)
INSERT INTO [ClassMemberCall] ([CallGUID],[InstanceGUID],[MemberGUID],[StartedOn],[FinishedOn],[SerializedResult],[ExceptionGUID],[SerializedException],[StartedOnTick],[FinishedOnTick])VALUES('B9779849-4815-4C92-8F2E-A29CB307B925',
'17D4C08C-1DCD-4CBA-97A4-BE7A2DFF1DAB','8AC8D865-DEB2-4858-ACE5-A9252F484350','Jun  9 2009  3:23:00:790PM','Jun  9 2009  3:23:00:790PM',NULL,NULL,NULL,633801577807903192,633801577807903192)
INSERT INTO [ClassMemberCall] ([CallGUID],[InstanceGUID],[MemberGUID],[StartedOn],[FinishedOn],[SerializedResult],
[ExceptionGUID],[SerializedException],
[StartedOnTick],[FinishedOnTick])VALUES('CAE8210C-617A-49F4-A739-E442F39B55B0',
'D46CFBF8-5DC0-437B-AE58-A9237BF8E1A0','108BC581-A5BA-402C-BA15-B8D1EB0C03F1','Jun  9 2009  3:23:00:650PM','Jun  9 2009  3:23:00:837PM',NULL,NULL,NULL,633801577806496915,633801577808371951)

1 个解决方案

#1

I'm not sure I get you right, but if I do, something like that seems to do the trick:

我不确定我是否正确,但如果我这样做,那样的东西似乎可以解决问题:

select RESULT.* from classmembercall as RESULT 
inner join classmembercall as INPUT 
    ON RESULT.startedontick BETWEEN INPUT.startedontick and INPUT.finishedontick 
    AND RESULT.finishedontick BETWEEN INPUT.startedontick and INPUT.finishedontick
where INPUT.CallGUID = 'CAE8210C-617A-49F4-A739-E442F39B55B0'

it should give you all calls between start and end tick of given log entry

它应该给你给定日志条目的开始和结束标记之间的所有调用

if you need to filter it additionally by the same InstanceGUID, then:

如果您需要通过相同的InstanceGUID进行额外过滤,则:

select RESULT.* from classmembercall as RESULT 
inner join classmembercall as INPUT 
    ON RESULT.startedontick BETWEEN INPUT.startedontick and INPUT.finishedontick 
    AND RESULT.finishedontick BETWEEN INPUT.startedontick and INPUT.finishedontick
    AND RESULT.InstanceGUID = INPUT.InstanceGUID
where INPUT.CallGUID = 'CAE8210C-617A-49F4-A739-E442F39B55B0'

note that given your sample data, you'll always get only the item with provided CallGuild - because all of the rows have different InstanceGUIDs...

请注意,根据您的示例数据,您将始终只获得提供CallGuild的项目 - 因为所有行都有不同的InstanceGUID ...

Good luck :)

祝好运 :)

#1