下面是我的查询结果。但是有很多冗余记录,因此,我想过滤掉这个查询的结果。我的目标是每个角度只提取两条记录,第一条和最后一条。 例如,当角度为195时, 我想在日期=2/27/2017、时间=2:00:00 AM和 日期为2017 年 2 月 27 日且时间 = 9:00:00 AM时的第二条记录。 同样,当角度更改为 210 时,我想在日期=2/27/2017 时间=10:00:00 AM时获得它的第一条记录,当日期和时间为2/27/2017 和 9:00:00 PM时获得另一条记录。所有记录也是如此。 我尝试自己做,但它每个角度只返回一条记录,只返回顶部的记录,不知道我如何获得最后一条。 我正在使用两个查询(查询 1( 来执行此操作
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]));
查询结果和我需要的结果用红色标记。 期待您的来信。 谢谢。
试试这个
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,因为日期和时间是保留字。
编辑
这使得它更加困难,特别是在没有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 视图(中,然后单击"运行":
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.
现在,您将能够完全按原样运行我的查询。
它可能是:
SELECT
*
FROM
final,
(SELECT
final.Date, Min(final.Time) As MinTime, Max(final.Time) As MaxTime, final.Angle
FROM
final
GROUP BY
final.Date, final.Angle) As T
WHERE
final.Angle = T.Angle AND
final.Date = T.Date AND
((final.Time = T.MinTime) OR (final.Time = T.MaxTime))
ORDER BY
final.Date, final.Time, final.Angle
请记住,除非您也从表中选择时间,否则您无法知道要检索哪些值。所需输出中的日期和角度不是唯一的组合。
您可能需要稍微修改Access
语法。
SELECT
final.Date AS dt,
MIN(final.Time) AS tm,
final.Angle AS ag
FROM
final
GROUP BY
final.Date, final.Angle
UNION SELECT
final.Date,
MAX(final.Time),
final.Angle
FROM
final
GROUP BY
final.Date, final.Angle
ORDER BY
dt, ag, tm;
编辑:SQL小提琴示例...http://sqlfiddle.com/#!9/2f638c/14