我有一个数据集：
tbldataid TS EndTS > HX32.3401 10/2/2017 11:49:34 PM 10/3/2017 12:01:57 AM > HX32.3403 10/3/2017 12:02:48 AM 10/3/2017 12:08:34 AM > HX32.3425 10/3/2017 2:50:57 AM 10/3/2017 2:50:58 AM > HX32.3428 10/3/2017 4:06:15 AM 10/3/2017 6:09:19 AM
我想基本上在一个小时内“拆分”这些时间间隔，例如：
tbldataid TS EndTS HX32.3401 10/2/2017 11:49:34 PM 10/2/2017 11:59:99 PM HX32.3401 10/2/2017 12:00:00 PM 10/3/2017 12:01:57 AM HX32.3403 10/3/2017 12:02:48 AM 10/3/2017 12:08:34 AM HX32.3425 10/3/2017 2:50:57 AM 10/3/2017 2:50:58 AM HX32.3428 10/3/2017 4:06:15 AM 10/3/2017 4:59:99 AM HX32.3428 10/3/2017 5:00:00 AM 10/3/2017 5:59:99 AM HX32.3428 10/3/2017 6:00:00 AM 10/3/2017 6:09:19 AM
到目前为止，这是我的代码（该想法的积分发给@DumitrescuBogdan，将通话数据分为15分钟间隔）：
SELECT [tbldataid],[TS], IIF([EndTS]<=dateadd("n",60*((datediff("n",0,[TS]/60))+.99),0), [EndTS], dateadd("n", 60*(datediff("n",0,[TS]/60)+.99),0)) as [End] FROM tbldata UNION ALL SELECT t2.[tbldataid], t2.[TS], IIF(t1.[EndTS]<=dateadd("n",60*((datediff("n",0, t2.[EndTS])/60)+1),0), t1.[EndTS], dateadd("n",60*((datediff("n",0,t2.[EndTS])/60)+1),0)) as [End] FROM tbldata t1 LEFT JOIN tbldata t2 ON t1.[tbldataid]=t2.[tbldataid] Where t2.[EndTS]<IIF(t1.[EndTS]<=dateadd("n",60*((datediff("n",0,t2.[EndTS])/60)+1),0), t1.[EndTS], dateadd("n",60*((datediff("n",0,t2.[EndTS])/60)+1),0));
后半部分（在“全部合并”之后）不产生任何结果，前半部分产生以下结果：
tbldataid TS End HX32.3401 10/2/2017 11:49:34 PM 10/2/2017 11:59:00 PM HX32.3403 10/3/2017 12:02:48 AM 10/3/2017 12:08:34 AM HX32.3425 10/3/2017 2:50:57 AM 10/3/2017 2:50:58 AM HX32.3428 10/3/2017 4:06:15 AM 10/3/2017 4:59:00 AM
谢谢，我是初学者。我了解第一部分；坦白地说，我不明白下半场
我愿意接受其他解决方案。
我正在使用MS-Access 2010
这可以通过 笛卡尔 查询来完成：
SELECT DISTINCT tblData.tblDataId, IIf([TimeStart] > DateAdd("h",[Factor],CDate(Fix([Timestart]*24)/24)), [TimeStart], DateAdd("h",[Factor],CDate(Fix([Timestart]*24)/24))) AS TSStart, IIf([TimeEnd] < DateAdd("s",3599,DateAdd("h",[Factor],CDate(Fix([Timestart]*24)/24))), [TimeEnd], DateAdd("s",3599,DateAdd("h",[Factor],CDate(Fix([Timestart]*24)/24)))) AS TSEnd FROM qdxFactor, tblData WHERE qdxFactor.Factor Between 0 And DateDiff("h",[TimeStart],[TimeEnd]);
使用其他已保存的笛卡尔查询（ qdxFactor ）：
SELECT DISTINCT [Tens]+[Ones] AS Factor, 10*Abs([Deca].[id] Mod 10) AS Tens, Abs([Uno].[id] Mod 10) AS Ones FROM MSysObjects AS Uno, MSysObjects AS Deca;
结果：
tblDataId TSStart TSEnd 3401 2017-10-02 23:49:34 2017-10-02 23:59:59 3401 2017-10-03 00:00:00 2017-10-03 00:01:57 3403 2017-10-03 00:02:48 2017-10-03 00:08:34 3425 2017-10-03 02:50:57 2017-10-03 02:50:58 3428 2017-10-03 04:06:15 2017-10-03 04:59:59 3428 2017-10-03 05:00:00 2017-10-03 05:59:59 3428 2017-10-03 06:00:00 2017-10-03 06:09:19
第二个示例：
tblDataId TimeStart TimeEnd 3430 2017-10-07 02:08:24 2017-10-07 14:09:30
tblDataId TSStart TSEnd 3430 2017-10-07 02:08:24 2017-10-07 02:59:59 3430 2017-10-07 03:00:00 2017-10-07 03:59:59 3430 2017-10-07 04:00:00 2017-10-07 04:59:59 3430 2017-10-07 05:00:00 2017-10-07 05:59:59 3430 2017-10-07 06:00:00 2017-10-07 06:59:59 3430 2017-10-07 07:00:00 2017-10-07 07:59:59 3430 2017-10-07 08:00:00 2017-10-07 08:59:59 3430 2017-10-07 09:00:00 2017-10-07 09:59:59 3430 2017-10-07 10:00:00 2017-10-07 10:59:59 3430 2017-10-07 11:00:00 2017-10-07 11:59:59 3430 2017-10-07 12:00:00 2017-10-07 12:59:59 3430 2017-10-07 13:00:00 2017-10-07 13:59:59 3430 2017-10-07 14:00:00 2017-10-07 14:09:30
