尝试编写一个查询，显示连续两年内发表论文的所有作者的姓名

Question

维护作者和出版物信息的数据库具有以下架构：

CREATE TABLE Author (aid integer NOT NULL,
name varchar(50) NOT NULL,
affiliation varchar(50), primary key(aid));

CREATE TABLE Paper (pid  integer NOT NULL,
title varchar(50) NOT NULL,
year integer NOT NULL, primary key(pid));

CREATE TABLE Authored (aid integer references Author,
pid integer references Paper,
primary key(aid, pid), foreign key(aid) references Author(aid), foreign key(pid) references Paper(pid));

insert into Author(aid, name, affiliation) values (1, "A", "DS");
insert into Author(aid, name, affiliation) values (2, "B", "PS");
insert into Author(aid, name, affiliation) values (3, "C", "CS");

insert into Paper(pid, title, year) values (100, "DS1", 2019);
insert into Paper(pid, title, year) values (101, "PS1", 2019);
insert into Paper(pid, title, year) values (102, "CS1", 2019);
insert into Paper(pid, title, year) values (103, "DS2", 2020);
insert into Paper(pid, title, year) values (104, "PS2", 2020);
insert into Paper(pid, title, year) values (105, "CS2", 2019);

Authored.aid是Author的外键，Authored.pid是Paper的外键。

我正在尝试编写一个查询，打印连续两年发表论文的所有作者的姓名。这是我到目前为止所拥有的，但它似乎过于复杂。

select au1.aid as id, a1.name as name
from authored au1 
inner join authored au2 on au1.aid = au2.aid
inner join author a1 on au1.aid = a1.aid
inner join paper p1 on au1.pid = p1.pid
inner join paper p2 on au2.pid = p2.pid
where p1.year = p2.year + 1
order by au1.aid;

Answer 1

我会使用lag()。要获取作者ids，请执行以下操作：

select p.aid
from (select a.aid, p.year,
             lag(p.year) over (partition by a.aid order by p.year) as prev_year
      from papers p join
           authored a
           on a.pid = p.pid
      group by a.aid, p.year
     ) p
where prev_year = year - 1

然后，您可以使用in或join或其他任何工具来获取完整的作者信息：

select a.*
from authors a
where a.aid in (select a.aid
                from (select a.aid, p.year, 
                             lag(p.year) over (partition by a.aid order by p.year) as prev_year
                      from papers p join
                           authored a
                           on p.pid = a.pid
                      group by a.aid, p.year
                     ) p
                where prev_year = year - 1
               );

你实际上不需要滞后，但它可能会更有效率。另一种选择是：

with pa as (
      select p.*, a.aid
      from papers p join
           authors a
           on p.pid = p.pid
     )
select a.*
from authors a
where a.aid in (select p.aid
                from pa p join
                     pa p_prev
                     on p_prev.aid = p.aid and
                        p_prev.year = p.year - 1
               );

Answer 2

尝试下面的方法，它看起来像是鸿沟和孤岛问题。这是一个小的示例demo，它将让您了解如何解决您的问题。

select
    id,
    name
from
(
    select
        aid,
        name,
        count(*) over (partition by rnk) as total
    from
    (
        select 
            aid as id, 
            name,
            year - row_number() over (partition by aid, name order by year) as rnk
        from authored au            

        inner join author a
        on au.aid = a.aid

        inner join paper p
        on au.pid = p.pid
    ) val
) fin
where total = 2

Answer 3

看起来没那么复杂

SELECT au1.aid as id, au1.name AS name
FROM author au1 INNER join authored authored1 ON au1.aid=authored1.aid
INNER join paper p1 ON authored1.pid=p1.pid
WHERE au1.aid IN (SELECT au2.aid FROM author au2 
                  INNER join authored authored2 ON au1.aid=authored2.aid
                  INNER join paper p2 ON au2.pid=p1.pid AND  p2.year = p1.year+1)
ORDER by au1.aid;

您也可以使用SQL EXISTS而不是IN