我想要的结果。
+--------+-------------+------------+--------+ | 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个不同的查询，每个渐趋复杂，但仍然没有工作。
我曾尝试查询：
查询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 | +--------+-------------+
只是给了我最近的日期，但没有价值。
查询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 | +--------+-------------+------------+
现在我看到所有我想要的信息，但我不能筛选最近的。
我试过DISTINCT，但没有奏效。
查询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
结果：在查询表达式语法错误（缺少运算符）
查询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语法错误
运气不好......感谢先进。
谢谢。
