要使用
on conflict
,您需要在
summary_songs
表上强制执行
unique(userid, songd)
:
SQL Fiddle
PostgreSQL 9.6 Schema Setup:
CREATE TABLE summary_songs (
date_time date NOT NULL,
userid integer NOT NULL,
songd integer NOT NULL,
countid integer NOT NULL,
unique(userid, songd)
);
CREATE TABLE daily_report(
date_time date NOT NULL,
userid integer NOT NULL,
songd integer NOT NULL,
countid integer NOT NULL
);
insert into daily_report (date_time, userid, songd, countid) values
(to_date('2017-12-31','YYYY-MM-DD'), 1 , 1 , 5),
(to_date('2017-12-31','YYYY-MM-DD'), 2 , 1 , 10),
(to_date('2017-12-31','YYYY-MM-DD'), 4 , 1 , 7);
insert into summary_songs (date_time, userid, songd, countid) values
(to_date('2017-12-30', 'YYYY-MM-DD'),1, 1, 80),
(to_date('2017-12-30', 'YYYY-MM-DD'),2, 1, 51),
(to_date('2017-12-30', 'YYYY-MM-DD'),3, 1, 66);
查询 1:
select * from daily_report
结果:
| date_time | userid | songd | countid |
|------------|--------|-------|---------|
| 2017-12-31 | 1 | 1 | 5 |
| 2017-12-31 | 2 | 1 | 10 |
| 2017-12-31 | 4 | 1 | 7 |
查询2:
select * from summary_songs
Results:
| date_time | userid | songd | countid |
|------------|--------|-------|---------|
| 2017-12-30 | 1 | 1 | 80 |
| 2017-12-30 | 2 | 1 | 51 |
| 2017-12-30 | 3 | 1 | 66 |
查询 3:
insert into summary_songs (date_time, userid, songd, countid)
select date_time, userid, songd, countid from daily_report
on conflict (userid, songd)
do update set
countid = summary_songs.countid + excluded.countid ,
date_time = excluded.date_time
查询4:
select * from summary_songs
Results:
| date_time | userid | songd | countid |
|------------|--------|-------|---------|
| 2017-12-30 | 3 | 1 | 66 |
| 2017-12-31 | 1 | 1 | 85 |
| 2017-12-31 | 2 | 1 | 61 |
| 2017-12-31 | 4 | 1 | 7 |