比较“连续的"和“连续的". Access中的行 [英] Comparing "Consecutive" Rows in Ms Access
问题描述
我有一个看起来像这样的表:
ID Subid Time Value Value2
1 1 3 100 2
1 1 4 100 2
2 2 3 200 1
2 2 4 200 2
4 4 4 10 4
5 6 3 10 2
5 6 4 12 2
我现在想做的只是显示从时间3到时间4有差异的条目.因此结果应如下所示:
ID Subid Time Value Value2
2 2 3 200 1
2 2 4 200 2
4 4 4 10 4
5 6 3 10 2
5 6 4 12 2
第二部分中条目的条件如下: 如果有两个条目具有相同的ID和Subid，但月份不同，则检查value和value2是否相等.如果两者相等，则删除条目，否则显示条目. 如果在其他时间找不到匹配项，则还显示该条目. 通常，此表中会有很多匹配的条目，所以我想通过整理这些条目来使我的生活更轻松.
我必须使用MS Access(2010)来完成此操作.
谢谢您的帮助.
在Remou回答后进行
我现在遇到的问题是我确实没有上面显示的表，但是我使用另一个查询来创建它.因此，现在我只是将查询放在Remou使用它的四个位置中创建该表...我敢肯定有更好的方法来执行此操作，但我无法弄清楚...
SELECT t2.*
FROM (SELECT t.ID, t.SubID, t.Time, t.Value, t.value2, t.value FROM
( HUGE SELECT HERE with 3 joins) t
( HUGE SELECT HERE with 3 joins) t
UNION ALL
( HUGE SELECT HERE with 3 joins) t
( HUGE SELECT HERE with 3 joins) t
ORDER BY Id, EntryNo;
如何:
SELECT t2.*
FROM (SELECT t.ID, t.Subid, t.Time, t.Value, t.Value2
FROM Test2 t
WHERE t.Time=4) AS t2 LEFT JOIN (SELECT t.ID, t.Subid, t.Time, t.Value, t.Value2
FROM Test2 t
WHERE t.Time=3) AS t1 ON t2.ID = t1.ID
WHERE t2.Value<>t1.Value
OR t2.Value2<>t1.Value2
OR t1.ID Is Null
UNION ALL
SELECT t1.* FROM
(SELECT t.ID, t.Subid, t.Time, t.Value, t.Value2
FROM Test2 t
WHERE t.Time=3) t1
LEFT JOIN
(SELECT t.ID, t.Subid, t.Time, t.Value, t.Value2
FROM Test2 t
WHERE t.Time=4) t2
ON t1.ID=t2.ID
WHERE t1.Value <> t2.Value
OR t1.Value2 <> t2.Value2
OR t2.ID Is Null
ORDER BY ID
I have a table that looks like this:
ID Subid Time Value Value2
1 1 3 100 2
1 1 4 100 2
2 2 3 200 1
2 2 4 200 2
4 4 4 10 4
5 6 3 10 2
5 6 4 12 2
What i want to do now is just show the entries where there is a difference from time 3 to time 4. So the result should look like this:
ID Subid Time Value Value2
2 2 3 200 1
2 2 4 200 2
4 4 4 10 4
5 6 3 10 2
5 6 4 12 2
Conditions for an entry being in the second part are as follows: If there are two entries with the same ID and Subid, but a different month then check if value and value2 are equal. If these are BOTH equal, then remove the entry, else show the entry. If no match can be found in the other time then also show the entry. Typically this table would have a lot of entries that match, so I want to make my life easier by sorting those out.
I have to use MS Access (2010) to do this.
Thank you for your help.
EDIT after Remou's answer:
I now have the problem that I do not really have the table shown above, but that I create it with another query. So for now I just put the query to create that table in the four places Remou uses it... I'm sure there is a better way to do this, but I can't figure it out...
SELECT t2.*
FROM (SELECT t.ID, t.SubID, t.Time, t.Value, t.value2, t.value FROM
( HUGE SELECT HERE with 3 joins) t
WHERE t.Time=4) AS t2
LEFT JOIN
(SELECT t.ID, t.SubID, t.Time, t.Value, t.value2, t.value
FROM
( HUGE SELECT HERE with 3 joins) t
WHERE t.Time=3) AS t1
ON t2.Id = t1.Id
WHERE t2.value<>t1.value
OR t2.value2<>t1.value2
OR t1.Id Is Null
UNION ALL
SELECT t1.* FROM
(SELECT t.ID, t.SubID, t.Time, t.Value, t.value2, t.value
FROM
( HUGE SELECT HERE with 3 joins) t
WHERE t.Time=3) AS t1
LEFT JOIN
(SELECT t.ID, t.SubID, t.Time, t.Value, t.value2, t.value
FROM
( HUGE SELECT HERE with 3 joins) t
WHERE t.Time=4) AS t2
ON t1.Id=t2.Id
WHERE t1.value<>t2.value
OR t1.value2<>t2.value2
OR t2.Id Is Null
ORDER BY Id, EntryNo;
How about:
SELECT t2.*
FROM (SELECT t.ID, t.Subid, t.Time, t.Value, t.Value2
FROM Test2 t
WHERE t.Time=4) AS t2 LEFT JOIN (SELECT t.ID, t.Subid, t.Time, t.Value, t.Value2
FROM Test2 t
WHERE t.Time=3) AS t1 ON t2.ID = t1.ID
WHERE t2.Value<>t1.Value
OR t2.Value2<>t1.Value2
OR t1.ID Is Null
UNION ALL
SELECT t1.* FROM
(SELECT t.ID, t.Subid, t.Time, t.Value, t.Value2
FROM Test2 t
WHERE t.Time=3) t1
LEFT JOIN
(SELECT t.ID, t.Subid, t.Time, t.Value, t.Value2
FROM Test2 t
WHERE t.Time=4) t2
ON t1.ID=t2.ID
WHERE t1.Value <> t2.Value
OR t1.Value2 <> t2.Value2
OR t2.ID Is Null
ORDER BY ID
这篇关于比较“连续的"和“连续的". Access中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
