根据劳伦斯的回答,使用以下脚本:
创建一个临时表以从 CSV 文件插入:
CREATE TEMP TABLE std_temp (id int, student_name char(25), group_name char(25));
然后,导入 CSV 文件:
COPY std_temp FROM '/home/username/Documents/std.csv' CSV HEADER;
现在,创建std
and grp
学生和团体表格:
CREATE TABLE grp (id int, name char(25));
CREATE TABLE std (id int, name char(20), grp_id int);
It's grp
轮到表填充基于distinct
组名称的值。考虑如何row_number() is use to provide value for
id`:
INSERT INTO grp (id, name) select row_number() OVER (), * from (select distinct group_name from std_temp) as foo;
最后一步,根据join
然后将其插入std
table:
insert into std (id, name, grp_id) select std_temp.id, std_temp.student_name,grp.id from std_temp inner join grp on std_temp.group_name = grp.name;
最后,从final中检索数据std
table:
select * from std;