1. 执行语句,创建3张表
CREATE TABLE table_dev1 (
id serial NOT NULL,
devicecode text NULL, -- 设备id
openflag int4 NULL -- 是否启动 1:启动, 0:未启动
);
CREATE TABLE table_dev2 (
id serial NOT NULL,
devicecode text NULL, -- 设备id
devicetype int4 NULL, -- 设备类型
vendor text NULL -- 厂家名称
);
CREATE TABLE table_dev3 (
id serial NOT NULL,
devicecode text NULL, -- 设备id
openflag int4 NULL, -- 是否启动 1:启动, 0:未启动
devicetype int4 NULL, -- 设备类型
vendor text NULL -- 厂家名称
);
2. 插入数据到 table_dev1 & table_dev2
insert into table_dev1 (devicecode, openflag)values ('123', 1);
insert into table_dev1 (devicecode, openflag)values ('abc', 0);
insert into table_dev2 (devicecode, devicetype, vendor)values ('123', 100, '路人');
insert into table_dev2 (devicecode, devicetype, vendor)values ('123', 100, '路人');
insert into table_dev2 (devicecode, devicetype, vendor)values ('abc', 101, '未知');
得到表数据
3. 使用 left join 关联表 table_dev1 & table_dev1,并把数据插入到表 table_dev3
1)未去重
insert into table_dev3 (devicecode, openflag, devicetype, vendor)
select t1.devicecode, t1.openflag, t2.devicetype, t2.vendor
from table_dev1 t1
left join
table_dev2 t2
on t1.devicecode = t2.devicecode
得到结果 >>
2)去重
insert into table_dev3 (devicecode, openflag, devicetype, vendor)
select t1.devicecode, t1.openflag, t2.devicetype, t2.vendor
from table_dev1 t1
left join
(select t3.* from
(select devicecode,devicetype,vendor,row_number() over (partition by devicecode order by id desc) as rownum from table_dev2) t3 where t3.rownum = 1 ) t2
on t1.devicecode = t2.devicecode
得到结果 >>