msaccess将最近匹配的记录从一个表连接到另一个表 [英] msaccess join most recent matching record from one table to another

问题描述

我想要的结果.

+--------+-------------+------------+--------+
|  Tag   | most_recent |  Comment   | Author |
+--------+-------------+------------+--------+
| TAG001 | 2015-07-23  | Something3 | AM     |
| TAG002 | 2015-07-25  | Something5 | BN     |
+--------+-------------+------------+--------+

我的桌子:

状态表

+--------+-------------+------------+
|  Tag   |   Status    | DateStatus |
+--------+-------------+------------+
| TAG001 | Not Started |            |
| TAG002 | Complete    | 2015-07-23 |
+--------+-------------+------------+

评论表

+----+--------+-------------+------------+--------+
| ID |  Tag   | DateCreated |  Comment   | Author |
+----+--------+-------------+------------+--------+
|  1 | TAG001 | 2015-07-22  | Something1 | JS     |
|  2 | TAG002 | 2015-07-23  | Something2 | JS     |
|  3 | TAG001 | 2015-07-23  | Something3 | AM     |
|  4 | TAG002 | 2015-07-23  | Something4 | AS     |
|  5 | TAG002 | 2015-07-25  | Something5 | BN     |
+----+--------+-------------+------------+--------+

我尝试了4个不同的查询，每个查询都变得越来越复杂，但仍然无法正常工作.

I've tried 4 different queries, each getting progressively more complicated, but still not working.

我尝试过的查询:

查询1)

SELECT Comments.[Tag], Max(Comments.[DateCreated]) AS most\_recent
FROM Comments
GROUP BY Comments.[Tag];

结果1)

+--------+-------------+
|  Tag   | most_recent |
+--------+-------------+
| TAG001 | 2015-07-23  |
| TAG002 | 2015-07-25  |
+--------+-------------+

请给我最近的日期，但没有值.

Just gives me the most recent date, but no values.

查询2)

SELECT Comments.[Tag], Max(Comments.[DateCreated]) AS most\_recent
FROM Comments
GROUP BY Comments.[Tag];

结果2)

+--------+-------------+------------+
|  Tag   | most_recent |  Comment   |
+--------+-------------+------------+
| TAG001 | 2015-07-22  | Something1 |
| TAG001 | 2015-07-23  | Something3 |
| TAG002 | 2015-07-23  | Something2 |
| TAG002 | 2015-07-23  | Something4 |
| TAG002 | 2015-07-25  | Something5 |
+--------+-------------+------------+

现在，我看到了我想要的所有信息，但是我无法筛选最新信息.

Now I see all the information I want, but I cannot filter for the most recent.

我尝试了DISTINCT，但是没有用.

I tried DISTINCT, but it didn't work.

查询3)

从此处修改: MYSQL-加入从一个表到另一个表的最新匹配记录

SELECT Status.\*,Comments.\*
FROM Status S
LEFT JOIN Comments C ON S.tag = C.tag
JOIN(SELECT x.tag, MAX(x.DateCreated) AS MaxCommentDate FROM Comments x
GROUP BY x.tag) y ON y.tag = x.tag AND y.MaxCommentDate = x.DateCreated

结果:查询表达式中的语法错误(缺少运算符)

Result: Syntax error (missing operator) in query expression

查询4)

从这里修改: 左加入到最新记录

SELECT
Status.\*,Comments.\*
FROM Status S
LEFT JOIN
(
Comments C
INNER JOIN
(
SELECT
x.tag, MAX(x.DateCreated) AS MaxCommentDate
FROM
Comments x
GROUP BY
x.tag
)
y
ON y.tag = x.tag
AND y.MaxCommentDate = x.DateCreated
)
ON S.tag = C.tag;

结果:JOIN上的语法错误

Result: Syntax Error on JOIN

运气不佳...感谢高级.

Not having much luck...thanks in advanced.

谢谢.

推荐答案

在Access 2010中，以下内容似乎对我有用:

The following seems to work for me in Access 2010:

SELECT c.Tag, c.DateCreated AS most_recent, c.Comment, c.Author
FROM
    (
        SELECT Tag, MAX(DateCreated) AS MaxDate 
        FROM Comments 
        GROUP BY Tag
    ) AS md
    INNER JOIN
    Comments AS c
        ON c.Tag = md.Tag AND c.DateCreated = md.MaxDate

这篇关于msaccess将最近匹配的记录从一个表连接到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！