sql - 如何在使用子查询时消除带有连接的笛卡尔积？

Question

我有以下数据库:

 paperid | authorid | name
---------+----------+---------------
 1889374 |   897449 | D. N. Page
 1889374 |  1795881 | C. N. Pope
 1889374 |  1952069 | S. W. Hawking

我想创建一个包含以下列的表:

• 论文编号
• 作者姓名 - 这篇论文的每位作者id
• 共同作者 - 该论文的每位共同作者

结果应该是这样的:

 paperid |    author     |          coauthors          
---------+---------------+---------------------------
 1889374 | D. N. Page    |  C. N. Pope S. W. Hawking
 1889374 | C. N. Pope    | D. N. Page  S. W. Hawking
 1889374 | S. W. Hawking | D. N. Page C. N. Pope 

这是通过以下查询实现的:

SELECT  foo.paperid, npa.name as author, foo.coauthors
INTO npatest
FROM newpaperauthor npa
CROSS JOIN (
   SELECT paperid, string_agg(name, ' ') as coauthors
   FROM newpaperauthor
   GROUP BY paperid
   ORDER BY paperid) foo;
UPDATE npatest SET coauthors = regexp_replace(coauthors, author, '');
SELECT * FROM npatest;

当数据库中有更多 paperid 时会出现问题，例如:

 paperid | authorid |       name       |      affiliation       
---------+----------+------------------+------------------------
 1889373 |   122817 | Kazuhiro Hongo   | 
 1889373 |  1091191 | Hiroshi NAKAGAWA | 
 1889373 |  1874415 | Hiroshi Nakagawa | University of Oklahoma
 1889373 |  2149773 | Han Soo Chang    | 
 1889374 |   897449 | D. N. Page       | 
 1889374 |  1795881 | C. N. Pope       | 
 1889374 |  1952069 | S. W. Hawking    | 

然后我将得到它们的笛卡尔积，例如:

 paperid |      author      |                           coauthors                            
---------+------------------+----------------------------------------------------------------
 1889373 | Kazuhiro Hongo   |  Hiroshi NAKAGAWA Hiroshi Nakagawa Han Soo Chang
 1889374 | Kazuhiro Hongo   | D. N. Page C. N. Pope S. W. Hawking
 1889373 | Hiroshi NAKAGAWA | Kazuhiro Hongo  Hiroshi Nakagawa Han Soo Chang
 1889374 | Hiroshi NAKAGAWA | D. N. Page C. N. Pope S. W. Hawking
 1889373 | Hiroshi Nakagawa | Kazuhiro Hongo Hiroshi NAKAGAWA  Han Soo Chang
 1889374 | Hiroshi Nakagawa | D. N. Page C. N. Pope S. W. Hawking
 1889373 | Han Soo Chang    | Kazuhiro Hongo Hiroshi NAKAGAWA Hiroshi Nakagawa 
 1889374 | Han Soo Chang    | D. N. Page C. N. Pope S. W. Hawking
 1889373 | D. N. Page       | Kazuhiro Hongo Hiroshi NAKAGAWA Hiroshi Nakagawa Han Soo Chang
 1889374 | D. N. Page       |  C. N. Pope S. W. Hawking
 1889373 | C. N. Pope       | Kazuhiro Hongo Hiroshi NAKAGAWA Hiroshi Nakagawa Han Soo Chang
 1889374 | C. N. Pope       | D. N. Page  S. W. Hawking
 1889373 | S. W. Hawking    | Kazuhiro Hongo Hiroshi NAKAGAWA Hiroshi Nakagawa Han Soo Chang
 1889374 | S. W. Hawking    | D. N. Page C. N. Pope 

如何去掉那里的笛卡尔积？

Answer 1

解决这个问题的方法是:

将所有共同作者的列表生成为子查询。生成所有作者的列表。然后将它们连接在一起并进行字符串操作以获得您想要的内容。

---

作者很简单:

select paperid, npa.name as author
from newpaperauthor npa;

共同作者很简单:

select paperid, string_agg(npa.name, ' ') as coauthors
from newpaperauthor npa
group by paperid;

组合需要一些列表替换:

select a.paperid, a.author,
       replace(replace(coauthors, author, ''), '  ', ' ') as coauthors
from (select paperid, npa.name as author
      from newpaperauthor npa
     ) a join
     (select paperid, string_agg(npa.name, ' ') as coauthors
      from newpaperauthor npa
      group by paperid
     ) ca
     on a.paperid = ca.paperid;