Try this
SELECT * FROM final INNER JOIN
(SELECT Min(DDate + DTime) AS DDateTime, Angle FROM final GROUP BY Angle
UNION SELECT Max(DDate + DTime) AS DDateTime, Angle FROM final GROUP BY Angle) mm
ON final.DDate + final.DTime = mm.DDateTime AND final.Angle = mm.Angle
请注意,在测试中我将前两列的名称分别更改为 DDate 和 DTime,因为 Date 和 Time 是保留字。
EDIT
这使得它变得相当困难,特别是在没有 LEAD/LAG 功能的 Access 中。以下应该可行,但有人可能有更优雅的解决方案!
SELECT final.* FROM final INNER JOIN
(SELECT MIN(DDateTime) AS MDateTime, Angle FROM
(SELECT (f.DDate+ f.DTime) AS DDateTime, f.Earth_Value, f.Mars_Value, f.Earth_Col, f.Mars_Col, f.Diff, f.Angle, f.Col_Apart,
IIF(ISNULL((SELECT MIN(m.DDate + m.DTime) FROM final m where f.angle <> m.angle and (f.DDate+f.DTime) < (m.DDate+m.DTime))),
(SELECT MAX(DDate+DTime) FROM final),(SELECT MIN(m.DDate + m.DTime) FROM final m where f.angle <> m.angle and (f.DDate+f.DTime) < (m.DDate+m.DTime))) AS NextChangeDateTime
FROM final f order by DDate, DTime) g
GROUP BY g.Angle,g.NextChangeDateTime
UNION
SELECT MAX(DDateTime) AS MDateTime, Angle FROM
(SELECT (f.DDate+ f.DTime) AS DDateTime, f.Earth_Value, f.Mars_Value, f.Earth_Col, f.Mars_Col, f.Diff, f.Angle, f.Col_Apart,
IIF(ISNULL((SELECT MIN(m.DDate + m.DTime) FROM final m where f.angle <> m.angle and (f.DDate+f.DTime) < (m.DDate+m.DTime))),
(SELECT MAX(DDate+DTime) FROM final),(SELECT MIN(m.DDate + m.DTime) FROM final m where f.angle <> m.angle and (f.DDate+f.DTime) < (m.DDate+m.DTime))) AS NextChangeDateTime
FROM final f order by DDate, DTime) g
GROUP BY g.Angle,g.NextChangeDateTime) FLDates
ON final.DDate + final.DTime = FLDates.MDateTime
EDIT 2
从技术上讲,MS Access 中不存在临时表之类的东西。实际上,您只需创建一个普通表并在每次要使用它时删除其内容即可。
要创建表,您需要将其复制到查询窗口(SQL 视图)中,然后单击运行:
CREATE TABLE final (
DDate DATETIME NOT NULL,
DTime DATETIME NOT NULL,
Earth_Value DOUBLE NOT NULL,
Mars_Value DOUBLE NOT NULL,
Earth_Col INTEGER NOT NULL,
Mars_Col INTEGER NOT NULL,
Diff INTEGER NOT NULL);
然后在现有的查询类型之前(您只需要第一行):
INSERT into final
SELECT etc.
现在您将能够完全按原样运行我的查询。