如何查询MS Access中只有最后一个明细行的主明细结果

Question

我有以下主表

.------------------.
| id | parent_name | 
.------------------.
| 1  | Mike        |
| 2  | Sarah       |
| 3  | Danial      |
| 4  | Alex        |
.------------------.

并具有以下子表详细信息：

.------------------------------------------.
| id | parent_id | child_name | birth year |
.------------------------------------------.
| 1  |   1       | mandy      |  2000      |
| 2  |   1       | mark       |  2003      |
| 3  |   1       | mathew     |  2005      |
| 4  |   2       | sandy      |  1998      |
| 5  |   2       | sharon     |  2006      |
| 6  |   3       | david      |  2001      |
.------------------------------------------.

在上面的例子中，我故意选择第一个字母与父母名字匹配的孩子的名字，只是为了更容易理解这种关系，即使每个孩子都使用parent_id与他/她的父母相连。

我想要的是一个所有父母的列表(4行(，并从孩子表中有一个匹配的4行，只选择每个尊敬的父母最后一个出生的孩子。

.-------------------------------.
| id | parent_name | last_child |
.-------------------------------.
| 1  | Mike        | mathew     |
| 2  | Sarah       | sharon     |
| 3  | Danial      | david      |
| 4  | Alex        | (null)     |
.-------------------------------.

在oracle中，这很容易：

SELECT
p.id,
p.parent_name,
c.child_name last_child
FROM
parents_table p,
children_table c
WHERE
p.id = c.parent_id
AND  c.birth_year = (SELECT MAX(birth_year) FROM children_table where parent_id = p.id)

但我正在努力在MS Access中生成相同的结果。。MS Access不接受子查询(用于为同一父母选择最长出生年份的孩子(。

有没有更好的方法在MS Access中获得结果？

Answer 1

Access当然支持子查询，但您使用的是交叉联接，因此永远不会得到null。

相反，在FROM子句中保留join并执行子查询。顺便说一句，您的查询在Oracle中也会同样失败。Access和Oracle之间没有相关差异。

SELECT
p.id,
p.parent_name,
c.child_name last_child
FROM
parents_table p
LEFT JOIN
(
SELECT *
FROM children_table c
WHERE c.birth_year = (SELECT MAX(c2.birth_year) FROM children_table c2 WHERE c2.parent_id = c.parent_id)
) c
ON p.id = c.parent_id

使用EXISTS时，Access有时会表现得更好，因此重写为：

SELECT
p.id,
p.parent_name,
c.child_name last_child
FROM
parents_table p
LEFT JOIN
(
SELECT *
FROM children_table c
WHERE EXISTS(SELECT 1 FROM children_table c2 WHERE c2.parent_id = c.parent_id HAVING c.birth_year = MAX(c2.birth_year))
) c
ON p.id = c.parent_id

Answer 2

如果你只想要孩子的名字，你可以使用一个相关的子查询：

select p.*,
(select top 1 child_name
from children_table as ct
where ct.parent_id = p.id
order by ct.birth_year desc, ct.child_name
) as youngest_child_name
from parents_table as p;

这可以利用children_table(parent_id, birth_year desc, child_name)上的索引。有了这个指数，我预计这会很快。