sql - postgreSQL 依赖于连接的重复行

Question

我有一个复杂的问题。我将尝试用示例来解释它:

有一个表有主键，我想加入另一个表那里第一个表的主键是外键，我想如果第二个表中有重复的外键来选择重复性的数量。例如:

第一个表:

  id    name 
  ---  -----
  1     Greg
  2     Alan 
  3    George 
  4     John 
  5     Peter   

第二张 table

 id       aid        data   
 ---     -----      -------
 1        2          CCCV   
 2        2          VVVV 
 3        3          DDDDD 
 4        3          SSSS 
 5        4          PPPPP 

我希望连接的结果是:

 id(1st table)  aid   name    Data   Number
 -----------    ----  -----   -----  -----
 1               null  Greg    null   1
 2                1    Alan    CCCV   1
 2                2    Alan    VVVV   2
 3                3    George  DDDDD  1
 3                4    George  SSSS   2
 4                5    John    PPPPP  1
 5               null  Peter   null   1

我找了很多，我找不到任何东西。也许我不知道怎么搜索，或者没有我想做的事情。

Answer 1

SELECT Table1.id, Table2.id as aid, Table1.name, Table2.data,
GREATEST(1, (SELECT COUNT(*)
             FROM Table2 t2
             WHERE t2.aid = Table1.id
             AND t2.id <= Table2.id))
AS number
FROM Table1
LEFT JOIN Table2
ON Table2.aid = Table1.id
ORDER BY id, aid;

适用于 MySQL 和 PostgreSQL。