如果您确定这些运行是连续的并且不重叠，则应该可以使用Id字段来拆分组。查找仅相距1的ID字段以及大于相差某个阈值的日期创建的字段。从您的数据来看，一次运行中的记录看起来最多只能在1分钟内输入一次，因此安全阈值可能是1分钟或更长。
这将为您提供开始时间
SELECt mrtB.Id, mrtB.DateCreatedFROM MyReportTable AS mrtAINNER JOIN MyReportTable AS mrtB ON (mrtA.Id + 1) = mrtB.IdWHERe DateDiff(mi, mrtA.DateCreated, mrtB.DateCreated) >= 1
我称它为DataRunStarts
现在，您可以使用它来获取有关组的开始和结束位置的信息
SELECt drsA.Id AS StartID, drsA.DateCreated, Min(drsB.Id) AS ExcludedEndIdFROM DataRunStarts AS drsA, DataRunStarts AS drsBWHERe (((drsB.Id)>[drsA].[id]))GROUP BY drsA.Id, drsA.DateCreated
我将其称为DataRunGroups。我将最后一个字段称为“已排除”，因为它所持有的ID仅用于定义将被拉出的ID集的结束边界。
现在我们可以使用DataRunGroups和MyReportTable来获取计数
SELECt DataRunGroups.StartID, Count(MyReportTable.Id) AS CountOfRecordsFROM DataRunGroups, MyReportTableWHERe (((MyReportTable.Id)>=[StartId] And (MyReportTable.Id)<[ExcludedEndId]))GROUP BY DataRunGroups.StartID;
我称它为DataRunCounts
现在，我们可以将DataRunGroups和DataRunCounts放在一起以获取开始时间和计数。
SELECt DataRunGroups.DateCreated, DataRunCounts.CountOfRecordsFROM DataRunGroupsINNER JOIN DataRunCounts ON DataRunGroups.StartID = DataRunCounts.StartID;
Depending on your setup, you may need to do all of this on one query, but you
get the idea. Also, the very first and very last runs wouldn’t be included in
this, because there’d be no start id to go by for the very first run, and no
end id to go by for the very last run. To include those, you would make
queries for just those two ranges, and union them together along with the old
DataRunGroups query to create a new DataRunGroups. The other queries that use
DataRunGroups would work just as described above.
