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屋!