access sql从有条件的组中选择最近的记录

mzmfm0qo 于 2021-07-24 发布在 Java

关注(0)|答案(3)|浏览(376)

我在ms access 2016 x64中有两个表。我想用表注解的查询结果更新表事务中的字段status\u id（字段status\u id），它必须是不是0或6的最新status\u id。无论我尝试什么，即使使用联接，结果仍然不能提供预期的结果（应该是9条记录）。
如何创建只选择正确记录的查询？
更新sql非常简单，为了清晰起见，省略了它。如果我有一个有效的select查询，我可以将其更改为updatesql。
谢谢你的帮助！

Table transactions      
id      trans_no      status_id
12      9483241273    
13      9483241274    
14      9483241275    
15      9483241276    
16      9483241277    
17      9483241343    
18      9483241344    
19      9483241345    
20      9483241346    
21      9483390967   

Table comments
id      trans_id   com_date               status_id
29      12         23-06-2020 16:40:21    1
30      13         23-06-2020 16:40:26    1
31      14         23-06-2020 16:40:32    1
32      15         23-06-2020 16:40:38    1
33      16         23-06-2020 16:40:44    1
34      17         23-06-2020 16:40:50    1
35      18         23-06-2020 16:40:56    1
36      19         23-06-2020 16:41:01    1
37      20         23-06-2020 16:41:07    1
38      15         23-06-2020 16:40:44    6
39      18         25-06-2020 16:40:44    6
40      15         22-06-2020 16:40:38    11

sql语句

SELECT comments.trans_id, comments.status_id, MAX(comments.com_date) AS com_date
FROM comments
GROUP BY comments.trans_id, comments.status_id, MAX(comments.com_date)
HAVING comments.status_id<>0 AND comments.status_id)<>6))

结果：10条记录（预期9条）

trans_id    status_id     com_date
12          1             23-06-2020 16:40:21
13          1             23-06-2020 16:40:26
14          1             23-06-2020 16:40:32
15          1             23-06-2020 16:40:38
15          11            22-06-2020 16:40:38
16          1             23-06-2020 16:40:44
17          1             23-06-2020 16:40:50
18          1             23-06-2020 16:40:56
19          1             23-06-2020 16:41:01
20          1             23-06-2020 16:41:07

sql语句

SELECT a.trans_id, a.status_id  
FROM comments a
INNER JOIN
(SELECT b.trans_id, Max(b.com_date) AS com_date
FROM comments b
GROUP BY b.trans_id) c
ON a.trans_id=c.trans_id 
AND a.com_date = c.com_date
WHERE a.status_id<>0 AND a.status_id<>6

结果：7条记录（预期9条）

trans_id    status_id
12          1
13          1
14          1
16          1
17          1
19          1
20          1

sql ms-access

来源：https://stackoverflow.com/questions/62585084/access-sql-select-most-recent-records-out-of-a-group-with-conditions

3条答案

按热度按时间

hkmswyz61#

涉及聚合查询的更新操作将阻止更新操作。必须使用其他方法来编辑字段。保存计算的数据，尤其是聚合的数据通常是不明智的。如果可以计算更新，则可以在需要时进行计算，如下所示：

SELECT comments.*
FROM comments 
WHERE (((comments.id) In (SELECT TOP 1 ID FROM comments AS Dupe WHERE Dupe.trans_id=comments.trans_id 
    AND Dupe.status_id NOT IN(0,6) ORDER BY Dupe.com_date DESC)));

可以将该查询左键联接到事务，以显示具有派生状态代码的所有事务记录。

赞(0）回复(0）举报 2021-07-24

km0tfn4u2#

任何启用了总计的查询都将成为不可编辑的查询，因此这将在您的端上引起一些问题。这也解释了为什么你看到了你所看到的结果。
第一个查询有10个结果，结果是10，因为状态id上有一个group by，而事务id 15有一个1和11的条目，导致这两个记录都存在。
第二个与内部连接有关，因为唯一省略的记录是具有多个条目的记录。
我执行此更新的方法是编写一个小vba模块来执行更新，而不是尝试使用查询。

赞(0）回复(0）举报 2021-07-24

xmd2e60i3#

最终的sql现在是：

UPDATE transactions AS upd LEFT JOIN 
(SELECT a.trans_id, a.status_id
FROM comments AS a 
WHERE (((a.id) IN (
SELECT TOP 1 ID FROM comments AS dupe 
WHERE dupe.trans_id=a.trans_id
AND dupe.status_id NOT IN (0,6) ORDER BY dupe.com_date DESC)))) AS b
ON upd.id = b.trans_id SET upd.status_id = b.status_id
WHERE (((b.trans_id) Is Not Null));

赞(0）回复(0）举报 2021-07-24

我来回答

access sql从有条件的组中选择最近的记录

3条答案

相关问题

热门标签

最新问答