msaccess使用多个条件将最近的匹配记录从一个表连接到另一个表-Java 学习之路

扩大回答msaccess join most recent matching record from one table to another

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

如何使用多个条件/ n 1条件来获得结果，例如：我想使用tag，critera1和criteria2作为单个复合键来查询状态和注释表 .

EDIT ：我会问另一个问题，将数据分成不同的表格 . 此问题已被修改，以匹配@DonJewett给出的答案

表 - 结果

+----+--------+-----------+-----------+----------------+-------------+--------+
| ID |  Tag   | Criteria1 | Criteria2 |  DateCreated   |   Comment   | Author |
+----+--------+-----------+-----------+----------------+-------------+--------+
|  6 | TAG001 | ghi       | jkl       | 25-July-2015   | Something6  | AQ     |
|  8 | TAG001 | mno       | pqr       | 23-July-2015   | Something8  | BV     |
| 13 | TAG002 | abc       | abc       | 22-June-2015   | Something13 | BV     |
| 14 | TAG001 | abc       | def       | 06-August-2015 | Something14 | AB     |
+----+--------+-----------+-----------+----------------+-------------+--------+

我有的表格：

状态表

+--------+-------------+-----------+-----------+----------------+
|  Tag   |   Status    | Criteria1 | Criteria2 |   DateStatus   |
+--------+-------------+-----------+-----------+----------------+
| TAG001 | Not Started | abc       | def       | 04-August-2015 |
| TAG001 | Complete    | ghi       | jkl       | 04-August-2015 |
| TAG001 | Complete    | mno       | pqr       | 02-August-2015 |
| TAG002 | Not Started | abc       | abc       | 02-August-2015 |
+--------+-------------+-----------+-----------+----------------+

评论表：

+----+--------+-----------+-----------+----------------+-------------+--------+
| ID |  Tag   | Criteria1 | Criteria2 |  DateCreated   |   Comment   | Author |
+----+--------+-----------+-----------+----------------+-------------+--------+
|  1 | TAG001 | abc       | def       | 22-July-2015   | Something1  | JS     |
|  3 | TAG001 | abc       | def       | 23-July-2015   | Something3  | AM     |
|  6 | TAG001 | ghi       | jkl       | 25-July-2015   | Something6  | AQ     |
|  8 | TAG001 | mno       | pqr       | 23-July-2015   | Something8  | BV     |
| 12 | TAG002 | abc       | abc       | 20-June-2015   | Something12 | AZ     |
| 13 | TAG002 | abc       | abc       | 22-June-2015   | Something13 | BV     |
| 14 | TAG001 | abc       | def       | 06-August-2015 | Something14 | AB     |
+----+--------+-----------+-----------+----------------+-------------+--------+

我尝试使用AND，但它没有用 .

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 AND status.critera1 = c.criteria1 AND status.criteria2 = c.criteria2

COMPLETE ANSWER:

MaxCommentDateQuery

SELECT Tag, MAX(DateCreated) AS MaxDate, Criteria1, Criteria2
FROM Comments
GROUP BY Tag, Criteria1, Criteria2;

ResultQuery

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

ResultQuery（现在带状态）

SELECT DISTINCT c.*
FROM Status
INNER JOIN (MaxCommentDateQuery AS md
INNER JOIN Comments AS c
ON (md.Tag = c.Tag) AND (md.MaxDate = c.DateCreated) AND (c.criteria1 = md.criteria1) AND (c.criteria2 = md.criteria2))
ON (Status.Tag = c.Tag) AND (Status.Criteria1 = c.Criteria1) AND (Status.Criteria2 = c.Criteria2);

2 回答

试试这个：

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
    INNER JOIN
    status AS s
        ON s.critera1 = c.criteria1 AND s.criteria2 = c.criteria2

回复于 2024-04-29T06:13:02+08:00

您的查询中没有“状态”表，因此您无法在查询中使用它 . 我建议将子查询分解为自己的单独查询，然后您就可以使用UI进行编辑 .

MaxCommentDateQuery

SELECT Tag, MAX(DateCreated) AS MaxDate 
    FROM Comments 
    GROUP BY Tag

ResultQuery

SELECT c.Tag, c.DateCreated AS most_recent, c.Comment, c.Author
FROM MaxCommentDateQuery AS md
INNER JOIN Comments AS c
    ON c.Tag = md.Tag AND c.DateCreated = md.MaxDate

一旦发现这些问题，您就可以使用UI向ResultQuery添加Status . 加入标签，然后您应该能够在您的标准中使用状态字段 .

Version 1

这是Tag，Criteria1，Criteria2加入评论和状态的复合键 . 结果将限制为最大评论日期 . 您可能会发现这会过多地限制您的结果 .

ResultQuery（现在带状态）

SELECT c.*
FROM Status INNER JOIN (MaxCommentDateQuery AS md INNER JOIN Comments AS c 
ON (md.MaxDate = c.DateCreated) AND (md.Tag = c.Tag)) 
ON (Status.Criteria2 = c.Criteria2) 
    AND (Status.Criteria1 = c.Criteria1) 
    AND (Status.Tag = c.Tag);

Version 2

如果您需要与最长评论日期相关的条件，请使用此版本：

MaxCommentDateQuery（现状态）

SELECT Tag, MAX(DateCreated) AS MaxDate, Criteria1, Criteria2
FROM Comments
GROUP BY Tag, Criteria1, Criteria2;

ResultQuery（现在带状态）

SELECT DISTINCT c.*
FROM Status
INNER JOIN (MaxCommentDateQuery AS md
INNER JOIN Comments AS c
ON (md.Tag = c.Tag) AND (md.MaxDate = c.DateCreated) 
     AND (c.criteria1 = md.criteria1) AND (c.criteria2 = md.criteria2))
ON (Status.Tag = c.Tag) AND (Status.Criteria1 = c.Criteria1) 
     AND (Status.Criteria2 = c.Criteria2);

回复于 2024-04-29T06:13:02+08:00

msaccess使用多个条件将最近的匹配记录从一个表连接到另一个表

2 回答

相关问题