sql - 在另一个表中为外键创建新行

Question

我有一个类似于

CREATE TABLE b (b_id SERIAL PRIMARY KEY,
                data INTEGER NULL);

CREATE TABLE a (a_id INTEGER PRIMARY KEY,
                b_id INTEGER NULL REFERENCES b(b_id),
                attrib INTEGER);                 
                
INSERT INTO b (data) values(2);
INSERT INTO b (data) values(3);
INSERT INTO b (data) values(4);
INSERT INTO b (data) values(5);

INSERT INTO a VALUES(1, 1, 13);
INSERT INTO a VALUES(2, 2, 12);
INSERT INTO a VALUES(3, 3, 13);
INSERT INTO a VALUES(4, NULL, 14);
INSERT INTO a VALUES(5, NULL, 15);
INSERT INTO a VALUES(6, NULL, 16);

我想在其中创建新的不同行并在b其中链接这些行，以便a链接到a. 中的几行a 可以链接到中的同一行，b但每一行都NULL b_id应该导致并链接到中的新行b。

表之间没有链接，除了b_id. b_id可以假定为任何自动类型（唯一的附加要求是它应该适合作为主键）。

如果可以在单个语句中完成，那就太好了，但这不是必需的。

我可以轻松地创建b与缺少的行相对应的b_id新行a：

INSERT INTO b (data) 
SELECT NULL 
FROM a WHERE a.b_id IS NULL;

但到目前为止还没有设法构建一个我想要的更新（我倾向于最终将所有条目a链接到同一个新创建的行，例如

UPDATE a set b_id = bx.b_id 
FROM b bx 
WHERE NOT EXISTS (SELECT 1 
                  FROM a ax 
                  WHERE ax.b_id = bx.b_id) 
  AND a.b_id IS NULL;

我也做过没有子选择的版本，但结果相同。

在正确的解决方案之后有很多可能的结果（对于这个例子），一个是

援助	出价	属性
1	1	13
2	2	12
3	3	13
4	4	14
5	5	15
6	6	16

但同样有效的是

援助	出价	属性
1	1	13
2	2	12
3	3	13
4	6	14
5	4	15
6	5	16

或者

援助	出价	属性
1	1	13
2	2	12
3	3	13
4	100	14
5	200	15
6	300	16

我认为应该适用于这个例子的测试是

WITH c AS (
  SELECT b_id, COUNT(*) OVER (PARTITION BY b_id) 
  FROM a
) 
SELECT MAX(count) FROM c;

应该返回1并且

SELECT COUNT(*) FROM a WHERE b_id is NULL;

应该返回0。

主要环境是 Postgres，但我想尽可能地保持它的可移植性。如果可能的话，我也想避免使用WHILE。

游乐场设置：http ://sqlfiddle.com/#!17/2262c/1

标签： sqlpostgresql