sql - 将唯一键、几列和子查询的总和插入表中

Question

我正在使用 postgres 数据库，在数据库迁移过程中，我有一个空表 tableB，我想用另一个 tableA 中的现有数据填充它。

tableB 有以下列

CREATE TABLE contributors (
    id bigint NOT NULL,
    pps double precision NOT NULL,
    contributor_user_id bigint,
    project_id bigint
);

而 tableA 有以下列

CREATE TABLE tableA (
    id bigint NOT NULL,
    assigned_ppoints double precision NOT NULL,
    state integer,
    contributor_id bigint,
    project_id bigint
);

所有*_id其实都是外键。

我需要为 tableA 的 contributor_id 和 project_id 的每个现有组合在 tableB 上添加一个新行如下

• 在project_id中，tableA的project_id
• 在contributor_user_id中，我需要tableA的contributor_id
• 在 pps 中，我需要 contributor_user_id 和 project_id 的 assigned_ppoints 的 sum> 对于 tableA 中的 state=2。

我的起点(也是很遥远的)是

INSERT INTO tableB (id, project_id, contributor_user_id, pps) 
SELECT MAX(id) FROM tableB, project_id, contributor_id, SUM(assigned_ppoints)
FROM tableA WHERE project_id=1 AND state=2 AND contributor_id=1;

这是错误的，只会添加与 project_id 和 contributor_id 的一种组合对应的一行。

我该怎么做？

Answer 1

Aggregate filter :

select
    max(id),
    contributor_id,
    project_id,
    sum(assigned_ppoints) filter (where state = 2)
from t
group by 2,3

对于 9.3 及之前的版本:

select
    max(id),
    contributor_id,
    project_id,
    sum(assigned_ppoints * (state = 2)::integer)
from t
group by 2,3