sql - 如何从 Access 表中获取选择性记录

Question

下面是我查询的结果。但是有很多冗余记录，因此，我想过滤掉这个查询的结果。我的目标是每个角度只提取两个记录，第一个和最后一个。例如当角度为 195 时，我想在 date=2/27/2017, time=2:00:00 AM 和日期为 2/27/2017 且时间 = 9:00:00 AM 时的第二条记录。同样，当角度更改为 210 我想在 date=2/27/2017 time=10:00:00 AM 和另一条记录时获取其第一条记录当日期和时间为 2/27/2017 和 9:00:00 PM 时。所有记录也是如此。我试着自己做，但它只返回每个角度的一个记录，只有最上面的一个，不知道我怎么得到最后一个。我正在使用两个查询 (Query1)

SELECT final.Date, final.Angle
FROM final
GROUP BY final.Date, final.Angle

第二个查询是 (fileredOUtput)

SELECT Query1.Date, Query1.Angle, (SELECT TOP 1 final.Date FROM final WHERE  Query1.Date=final.Date AND Query1.Angle= final.Angle)
 AS NewDate,
 (SELECT TOP 1 final.Angle FROM final WHERE  Query1.Date=final.Date AND Query1.Angle= final.Angle) AS NewAngle,
 (SELECT TOP 1 final.earthCol.Value FROM final WHERE  Query1.Date=final.Date AND Query1.Angle= final.Angle) AS NewE_CV, 
(SELECT TOP 1 final.earthCol.ColNu FROM final WHERE  Query1.Date=final.Date AND Query1.Angle= final.Angle) AS New_E_CN,
 (SELECT TOP 1 final.mars_Col.Value FROM final WHERE  Query1.Date=final.Date AND Query1.Angle= final.Angle) AS NewM_CV,
 (SELECT TOP 1 final.Col_apart FROM final WHERE  Query1.Date=final.Date AND Query1.Angle= final.Angle) AS New_CApart,
 (SELECT TOP 1 final.mars_Col.ColNu FROM final WHERE  Query1.Date=final.Date AND Query1.Angle= final.Angle) AS NewM_CN, 
(SELECT TOP 1 final.Time FROM final WHERE  Query1.Date=final.Date AND Query1.Angle= final.Angle) AS NewTime
FROM Query1, final
WHERE (((Query1.Date) Between [Forms]![Query Form]![txtStartDate] And [Forms]![Query Form]![txtEndDate]));

Query results and results I need are marked with red .期待着听到您的意见。谢谢。

Answer 1

试试这个

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 是保留字。

编辑

这使得它变得相当困难，特别是在没有 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

编辑 2

从技术上讲，MS Access 中没有临时表这样的东西。实际上，您只需创建一个普通表并在每次要使用它时删除其内容。

要创建表，您需要将其复制到查询窗口(SQL View )中，然后单击运行:

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.

现在您将能够完全按原样运行我的查询。