这可以通过 笛卡尔 查询来完成:
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