英文:
Insert elements postgresql with maximum row
问题
我正在尝试创建一个与Postgres一起使用的插入脚本,
将最多插入每个人的一个元素,
例如,我有这个查询,但我想创建一个逻辑,如果我已经向一个人插入了一个文件,下一个文件应该插入到该国家的下一个人
我对这种情况感到困惑,以前从未见过这样的东西,一个只插入一行然后插入下一个“人”的脚本。
现在发生的情况是我向一个人插入3个文件,而同一国家的其他人没有收到文件。
WITH get_files AS (
SELECT
type,
country
FROM files
WHERE title IS NOT NULL
)
INSERT INTO history_person_file (
name,
document_type,
age
)
SELECT
p.name,
f.type,
p.age
FROM person p
LEFT JOIN get_files f
ON p.country = f.country
ON conflict (name) do nothing;
英文:
I am trying to create a insert script with postgres,
To insert into a table a maximum 1 element per person,
For example, I got this query down, but I would like to create a logic that if I already inserted a file to a person, the next file should go to the next person of that country
I'm so stucked with this situation, I've never seen something like this before, a script that insert just one row and then inserts to the next "person".
What is happening now is that I'm inserting 3 files to a person, and the other person from the same country doenst receive a file.
WITH get_files AS (
SELECT
type,
country
FROM files
WHERE title IS NOT NULL
)
INSERT INTO history_person_file (
name,
document_type,
age
)
SELECT
p.name,
f.type,
p.age
FROM person p
LEFT JOIN get_files f
ON p.country = f.country
ON conflict (name) do nothing;
答案1
得分: 2
在查询中执行限制逻辑。一旦你做对了,将结果插入到你的历史表中:
with num_docs as (
select type, country,
row_number() over (partition by country
order by title) as rn
from files
where title is not null
), num_person as (
select name, age, country,
row_number() over (partition by country
order by name) as rn
from person
)
select p.country, p.name, d.country, d.type, d.age
from num_docs d
full join num_person p
on (p.country, p.rn) = (d.country, d.rn);
我在这里使用了 full join
,这样你可以在加载到历史表之前查看结果。
英文:
Perform the restriction logic in your query. Once you have that right, insert the result into your history table:
with num_docs as (
select type, country,
row_number() over (partition by country
order by title) as rn
from files
where title is not null
), num_person as (
select name, age, country,
row_number() over (partition by country
order by name) as rn
from person
)
select p.country, p.name, d.country, d.type, d.age
from num_docs d
full join num_person p
on (p.country, p.rn) = (d.country, d.rn);
I used a full join
here so you can review the results before loading to your history table.
答案2
得分: 1
不确定但也许这会起作用
WITH RankedPersons AS (
SELECT
p.name,
p.age,
p.country,
ROW_NUMBER() OVER (PARTITION BY p.country ORDER BY f.title IS NULL) AS
row_num
FROM person p
LEFT JOIN files f ON p.country = f.country AND f.title IS NOT NULL
),
NextAvailableFile AS (
SELECT
f.type,
f.country
FROM files f
LEFT JOIN RankedPersons p ON f.country = p.country AND p.row_num = 1
WHERE p.row_num IS NULL
LIMIT 1
)
INSERT INTO person_file (name, document_type, age)
SELECT
p.name,
COALESCE(naf.type, 'default_document_type'),
p.age
FROM RankedPersons p
LEFT JOIN NextAvailableFile naf ON p.country = naf.country
WHERE p.row_num = 1
ON CONFLICT (name) DO NOTHING;
英文:
Not sure but maybe this will work
WITH RankedPersons AS (
SELECT
p.name,
p.age,
p.country,
ROW_NUMBER() OVER (PARTITION BY p.country ORDER BY f.title IS NULL) AS
row_num
FROM person p
LEFT JOIN files f ON p.country = f.country AND f.title IS NOT NULL
),
NextAvailableFile AS (
SELECT
f.type,
f.country
FROM files f
LEFT JOIN RankedPersons p ON f.country = p.country AND p.row_num = 1
WHERE p.row_num IS NULL
LIMIT 1
)
INSERT INTO person_file (name, document_type, age)
SELECT
p.name,
COALESCE(naf.type, 'default_document_type'),
p.age
FROM RankedPersons p
LEFT JOIN NextAvailableFile naf ON p.country = naf.country
WHERE p.row_num = 1
ON CONFLICT (name) DO NOTHING;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论