如何从Access中的表获取选择性记录 [英] How can I get selective records from a table in access

问题描述

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

Given below are the results of my query. But there are many redundant records, therefore, I want to filter out the results of this query. My goal is to extract only two records per angle, first and the last. For example when the angle is 195, I want to get its first record when the date=2/27/2017, time=2:00:00 AM and the second record when the date is 2/27/2017 and time=9:00:00 AM. Similarly when the angle is changed to 210 I want to get its first record when the date=2/27/2017 time=10:00:00 AM and the other record when the date and time is 2/27/2017 and 9:00:00 PM. And similarly for all the records. I tried to do it myself but it only returns one record per angle only the top one, dont know how do I get the last one. I am doing it using two queries, (Query1)

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

第二个查询是(fileredOUtput)

and second query is (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. Looking forward to hear from you. Thank you.

推荐答案

尝试一下

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

Please note that in testing I changed the name of the first two columns to DDate and DTime respectively, because Date and Time are reserved words.

编辑

这使难度变得更大，尤其是在没有LEAD/LAG功能的Access中.以下应该可行，但是有人可能会有更优雅的解决方案！

That makes it considerably harder, particularly in Access which has no LEAD/LAG functions. The following should work, but someone might have a more elegant solution!

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中没有诸如临时表之类的东西.真的，您只是创建一个普通表并在每次要使用它时将其内容删除.

Technically there is no such thing as a temporary table in MS Access. Really you just create a normal table and delete its contents every time you want to use it.

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

To create the table you need copy this into a Query Window (SQL View), and then click Run:

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);

然后在现有查询类型之前(您只需要第一行):

Then before your existing Query type (you just need the first line):

INSERT into final
SELECT etc.

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

Now you will be able to run my query exactly as is.

这篇关于如何从Access中的表获取选择性记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！