如何选择最旧的日期与关系? [英] How to select the oldest date with ties?

问题描述

我有这些表:

    create table persons (
      fname     char(12),
      lname     char(12),
      bdate     date,
      bplace    char(20),
      address   char(30),
      phone     char(12),
      primary key (fname, lname));

    create table births (
      regno     int,
      fname     char(12),
      lname     char(12),
      regdate   date,
      regplace  char(20),
      gender    char(1),
      fthr_fname    char(12),
      fthr_lname    char(12),
      mthr_fname    char(12),
      mthr_lname    char(12),
      primary key (regno),
      foreign key (fname,lname) references persons,
      foreign key (fthr_fname,fthr_lname) references persons,
      foreign key (mthr_fname,mthr_lname) references persons); 

而且我只需要找到给定 fname 和 lname 的最大孩子的名字和姓氏(在我的例子中:分别是Michael"和Fox").

And I need to find just the first name and last name of the oldest child of a given fname and lname (In my case: 'Michael' and 'Fox' respectively).

只有在有一个大孩子的情况下，我才在这方面取得了成功.

I've only had success at this only if there is ONE oldest child.

但是，如果最大的孩子是双胞胎/三胞胎/等，我需要显示所有孩子的名字和姓氏.我将如何处理这个问题?

However, if the oldest child is a TWIN/TRIPLETS/ETC, I would need to show the first names and last names of all the children. How would I be able to handle this?

一些示例数据:

insert into persons values ('Michael', 'Fox', '1961-06-09', 'Edmonton, AB', 'Manhattan, New York, US', '212-111-1111');
insert into persons values ('Tracy', 'Pollan', '1960-06-22', 'Long Island, New York, US', 'Manhattan, New York, US', '212-222-1112');
insert into persons values ('TwinOne', 'Fox', '1995-11-20', 'Los Angeles, CA, US', 'Manhattan, New York, US', '212-222-1113');
insert into persons values ('TwinTwo', 'Fox', '1995-11-20', 'Los Angeles, CA, US', 'Manhattan, New York, US', '212-222-1114');
insert into persons values ('Young', 'Fox', '1997-07-06', 'Manhattan, New York, US', 'Manhattan, New York, US', '212-222-1115');
insert into births values (310, 'TwinOne', 'Fox', '1995-11-20', 'Los Angeles, CA, US', 'M', 'Michael', 'Fox', 'Tracy', 'Pollan');
insert into births values (312, 'TwinTwo', 'Fox', '1995-11-20', 'Los Angeles, CA, US', 'F', 'Michael', 'Fox', 'Tracy', 'Pollan');
insert into births values (314, 'Young', 'Fox', '1997-07-06', 'Manhattan, New York, US', 'M', 'Michael', 'Fox', 'Tracy', 'Pollan');

我刚刚得到一个最大的孩子所得到的:

What I had for just getting one oldest child:

SELECT p.fname, p.lname
FROM births b1, births b2, persons p
WHERE b1.fname = 'Michael' AND b1.lname = 'Fox'
AND b2.f_fname = b1.fname AND b2.f_lname = b1.lname
AND p.fname = b2.fname AND p.lname = b2.lname
ORDER BY julianday(p.bdate) ASC
LIMIT 1

我用上面的代码得到的结果:

The result I'm getting with the code above:

fname       lname
----------  ----------
TwinOne     Fox

我想要的结果:

fname       lname
----------  ----------
TwinOne     Fox
TwinTwo     Fox

推荐答案

使用 CTE 返回此人 ('Michael Fox') 的所有孩子，然后不存在:

Use a CTE to return all the children of the person ('Michael Fox') and then NOT EXISTS:

with children as (
  select p.fname, p.lname, p.bdate
  from persons p inner join births b
  on p.fname = b.fname and p.lname =b.lname
  where (b.fthr_fname = 'Michael' and b.fthr_lname = 'Fox') 
  or (b.mthr_fname = 'Michael' and b.mthr_lname = 'Fox')
)
select c.* from children c
where not exists (
  select 1 from children
  where bdate < c.bdate
) 

查看演示.
结果:

See the demo.
Results:

| fname   | lname | bdate      |
| ------- | ----- | ---------- |
| TwinOne | Fox   | 1995-11-20 |
| TwinTwo | Fox   | 1995-11-20 |

这篇关于如何选择最旧的日期与关系?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！