我想加入多对一关系,但根据条件(在本例中为最大日期)选择要加入的记录之一。 这是一个简化的架构;
parent
---------
id | name
---------
1 | Bob
2 | Mary
3 | Pat
child
----------------------------------
id | parent_id | name | birthdate
----------------------------------
11 | 1 | Anne | 2014-01-01
12 | 2 | Jane | 2014-01-02
13 | 3 | John | 2016-06-01
14 | 1 | Mark | 2016-08-01
15 | 2 | Jack | 2016-01-01
16 | 2 | Jill | 2016-01-01
父母可以有零个或多个孩子。 孩子的出生日期可以是将来(怀孕)。 来自同一父母的两个孩子可以具有相同的出生日期(双胞胎)。
我想得到父母记录和他们最小的孩子。 对于最小的双胞胎,我不在乎选择哪个孩子。 所以在这种情况下,鲍勃有两个孩子,他们最小的是马克,他将于 8 月 8 日出生。 玛丽有3个孩子。 她的两个最小的孩子是双胞胎,杰克和吉尔。 帕特没有孩子。 所以我想得到;
id | name | child | birthdate
-----------------------------
1 | Bob | Mark | 2016-08-01
2 | Mary | Jack | 2016-01-01
3 | Pat | null | null
是否可以在单个查询中执行此操作?
SELECT p.id, p.name, c.name, birthdate FROM parent p
LEFT JOIN child c ON p.id = c.parent_id AND c.birthdate <= NOW()
WHERE c.birthdate IS NULL OR birthdate = (SELECT MIN(birthdate) FROM child c2 WHERE c2.parent_id = p.id)
GROUP BY p.id
SQL 小提琴在这里。 http://sqlfiddle.com/#!9/a5fec2/2
注意:输入数据已稍作修改,以获得所需的输出。
您可以使用以下查询:
SELECT p.id, p.name, c.name, c.birthdate
FROM parent AS p
LEFT JOIN (
SELECT parent_id, name, birthdate,
@rn := IF(@pid = parent_id, @rn + 1,
IF(@pid := parent_id, 1, 1)) AS rn
FROM child
CROSS JOIN (SELECT @rn := 0, @pid := 0) AS vars
ORDER BY parent_id, birthdate DESC
) AS c ON p.id = c.parent_id AND c.rn = 1
派生表中使用变量,以便获取每个父级的最新子项。
在这里演示
SELECT
`p`.`id`,
`p`.`name`,
`c`.`name` AS `child`,
`c`.`birthday`
FROM `parent` AS `p`
LEFT JOIN `child` AS `c`
ON `p`.`id` = `c`.`parent_id`
GROUP BY `c`.`parent_id`
ORDER BY `p`.`id`;
http://sqlfiddle.com/#!9/b70acf/2