mark一下
on conflict 用法真的很方便,不存在就插入,存在可以更新 可以do nothing,就是用起来要注意几点:
- 字段必须完全同名。试了半天不行,改了同名就行。
- 拿不到原表的内容。退而求其次用原表做一个内连接就行,取原值然后想怎么玩都行。下面这个例子就是取原值并加上新统计值更新回原表。
- 建临时表可以加入很多自己需要的内容,操作空间更大
- excluded代表新选择出来的内容。
insert into plate_no_info
select plate_no,total_orders ,first_parking_time,last_parking_time
from (
select t1.plate_no,(plate_no_info.total_orders + cnt ) as total_orders,plate_no_info.first_parking_time,t1.last_parking_time
from (
select plate_no,count(id) cnt,min(parking_time) first_parking_time,max(parking_time) last_parking_time
from order_list
WHERE created_at > CURRENT_DATE - 1
and plate_no ~'^[\u2e80-\ua4cf]|[\uf900-\ufaff]|[\ufe30-\ufe4f]'
GROUP BY plate_no
) t1,
plate_no_info
WHERE 1=1
AND t1.plate_no = plate_no_info.plate_no
)
as t_temp
on conflict(plate_no)
-- do NOTHING
do update
set
total_orders = excluded.total_orders,
first_parking_time = excluded.first_parking_time,
last_parking_time = excluded.last_parking_time;