Postgresql left join左连接后数据变多去重记录（当左表1对多右表时）

程序媛一枚~

已于 2023-11-15 10:07:31 修改

阅读量3.5k

点赞数 1

分类专栏： JAVA 数据库 Postgresql 文章标签： postgresql 数据库 java Postgres left join记录变多去重

于 2023-03-10 12:53:37 首次发布

本文链接：https://blog.csdn.net/qq_40985985/article/details/129440432

版权

JAVA 同时被 3 个专栏收录

126 篇文章 7 订阅

订阅专栏

数据库

27 篇文章 1 订阅

订阅专栏

Postgresql

22 篇文章 0 订阅

订阅专栏

Postgresql left join左连接后数据变多去重记录（当左表1对多右表时）

1. 构造表数据

drop table if exists t_cover;
create table if not exists t_cover(
    pname text NOT NULL,
    upload_date varchar(12),
    city_code varchar(20) default '',
    total_dis varchar(20),
    create_time timestamp with time zone NOT NULL DEFAULT now(),
    constraint t_cover_unique_key unique (pname,upload_date,city_code)
);
COMMENT ON TABLE t_cover IS '统计中间表';
COMMENT ON COLUMN t_cover.pname IS '名称';
COMMENT ON COLUMN t_cover.upload_date IS '日期';
COMMENT ON COLUMN t_cover.city_code IS '城市行政编码';
COMMENT ON COLUMN t_cover.total_dis IS '里程';
COMMENT ON COLUMN t_cover.create_time IS '创建时间';

create index if not exists t_cover_citycode on t_cover(city_code);
create index if not exists t_cover_pname on t_cover(pname);
create index if not exists t_cover_uploaddate on t_cover(upload_date);

INSERT INTO public.t_cover(pname, upload_date, city_code, total_dis) VALUES ('aa', '202201', '110099', 99.88);
INSERT INTO public.t_cover(pname, upload_date, city_code, total_dis) VALUES ('aa', '202201', '110000', 64.96);
INSERT INTO public.t_cover(pname, upload_date, city_code, total_dis) VALUES ('aa', '202201', '120000', 82.77);
INSERT INTO public.t_cover(pname, upload_date, city_code, total_dis) VALUES ('aa', '202202', '110108', 23.63);
INSERT INTO public.t_cover(pname, upload_date, city_code, total_dis) VALUES ('aa', '202202', '120000', 17.97);

DROP TABLE if exists t_county;
CREATE TABLE if not exists t_county
(
    province_code integer,
    province_name character varying(32),
    city_code integer,
    city_name character varying(32),
    county_code integer,
    county_name character varying(32)
);

INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (110000,'北京市',110099,'北京市',110114,'昌平区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (110000,'北京市',110099,'北京市',110115,'大兴区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (110000,'北京市',110099,'北京市',110101,'东城区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (110000,'北京市',110099,'北京市',110111,'房山区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (110000,'北京市',110099,'北京市',110106,'丰台区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (110000,'北京市',110099,'北京市',110108,'海淀区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (110000,'北京市',110099,'北京市',110116,'怀柔区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (110000,'北京市',110099,'北京市',110109,'门头沟区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (110000,'北京市',110099,'北京市',110118,'密云区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (110000,'北京市',110099,'北京市',110117,'平谷区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (110000,'北京市',110099,'北京市',110107,'石景山区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (110000,'北京市',110099,'北京市',110113,'顺义区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (110000,'北京市',110099,'北京市',110112,'通州区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (110000,'北京市',110099,'北京市',110102,'西城区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (110000,'北京市',110099,'北京市',110119,'延庆区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (110000,'北京市',110099,'北京市',110105,'朝阳区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (120000,'天津市',120099,'天津市',120115,'宝坻区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (120000,'天津市',120099,'天津市',120113,'北辰区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (120000,'天津市',120099,'天津市',120116,'滨海新区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (120000,'天津市',120099,'天津市',120110,'东丽区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (120000,'天津市',120099,'天津市',120101,'和平区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (120000,'天津市',120099,'天津市',120105,'河北区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (120000,'天津市',120099,'天津市',120102,'河东区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (120000,'天津市',120099,'天津市',120103,'河西区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (120000,'天津市',120099,'天津市',120106,'红桥区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (120000,'天津市',120099,'天津市',120119,'蓟州区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (120000,'天津市',120099,'天津市',120112,'津南区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (120000,'天津市',120099,'天津市',120118,'静海区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (120000,'天津市',120099,'天津市',120104,'南开区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (120000,'天津市',120099,'天津市',120117,'宁河区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (120000,'天津市',120099,'天津市',120114,'武清区');
INSERT INTO t_county(province_code, province_name, city_code, city_name, county_code, county_name) VALUES (120000,'天津市',120099,'天津市',120111,'西青区');