sql - 如何从 SQLite 中的聚合函数返回关系

Question

我有一个作业问题，要求我返回一个人的最大 child 的名字，如果有关系，则返回所有关系。

数据库的架构是:

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),
  f_fname   char(12),
  f_lname   char(12),
  m_fname   char(12),
  m_lname   char(12),
  primary key (regno),
  foreign key (fname,lname) references persons,
  foreign key (f_fname,f_lname) references persons,
  foreign key (m_fname,m_lname) references persons
);

我目前的查询是

SELECT fname, lname, min(bdate)
from (SELECT *
    from persons
    JOIN births using (fname, lname)
    WHERE f_fname='Michael' and f_lname='Fox');

迈克尔福克斯是有问题的人。预期的输出是

Q4|MFOld
Q4|MFOld2

但是我只能得到第一个最大的 child 。我尝试使用 With声明，但我们不允许使用 View 或临时表来回答这个问题。我也研究过使用 Rank()，但据我所知，这是在 insqlite v3.25 中引入的，但这个问题将使用 v3.11 进行测试。关于如何退回领带的任何见解？

Answer 1

您可以使用 RANK() :

SELECT fname, lname, bdate
FROM (
    SELECT 
        b.fname, 
        b.lname,
        p.bdate, 
        RANK() OVER(ORDER BY bdate) rnk
    from persons p
    JOIN births b using (fname, lname)
    WHERE b.f_fname='Michael' and b.f_lname='Fox'
) x
WHERE rnk = 1;

如果您需要删除 where 子句以便为每个人获取最年长的 child ，那么您需要添加一个使用 PARTITION :

SELECT fname, lname, bdate
FROM (
    SELECT 
        b.fname, 
        b.lname,
        p.bdate, 
        RANK() OVER(PARTITION BY b.f_fname, b.f_lname ORDER BY bdate) rnk
    from persons p
    JOIN births b using (fname, lname)
) x
WHERE rnk = 1;

在 SQLite < 3.25 中，其中 RANK() 等窗口函数不可用，您可以使用公用表表达式(从版本 3.8 开始提供)拉出 Michael Fox 的所有子级并使用 NOT EXISTS过滤最旧的:

WITH cte AS (
    SELECT b.fname, b.lname, p.bdate
    FROM persons p
    JOIN births b using (fname, lname)
    WHERE b.f_fname = 'Michael' AND b.f_lname = 'Fox'
)
SELECT *
FROM cte c
WHERE NOT EXISTS (
    SELECT 1 FROM cte c1 WHERE c1.bdate < c.bdate
)