需求：删除station_id、ab_data_time、item_code_id、data_cycle、ab_value 字段重复的记录
#查询重复的数据
select
b.id,b.station_id,b.ab_data_time,b.item_code_id,b.data_cycle,b.ab_lable,b.ab_value
from
d_abnormal_data_yyyymms b
where
(b.station_id,b.ab_data_time,b.item_code_id,b.data_cycle,b.ab_value) in (
select *
from
(
select
station_id,ab_data_time,item_code_id,data_cycle,ab_value
from
d_abnormal_data_yyyymms
group by
station_id,ab_data_time,item_code_id,data_cycle,ab_value
having
count(*) >1
) b
)
and ab_data_time <= '2019-01-22 12:00:00';
#删除多个字段重复数据(去掉ab_lable字段)
delete
from
d_abnormal_data_yyyymms
where
(station_id,ab_data_time,item_code_id,data_cycle,ab_value) in (
select *
from
(
select
station_id,ab_data_time,item_code_id,data_cycle,ab_value
from
d_abnormal_data_yyyymms
group by
station_id,ab_data_time,item_code_id,data_cycle,ab_value
having
count(*) >1
) b
)
and ab_data_time <= '2019-01-22 12:00:00' and id not in (select * from (select
min(id)
from
d_abnormal_data_yyyymms
where ab_data_time <= '2019-01-22 12:00:00' group by
station_id,ab_data_time,item_code_id,data_cycle,ab_value
having count(*)>1)c
);
#查看数据是否还保留一条
select
b.id,b.station_id,b.ab_data_time,b.item_code_id,b.data_cycle,b.ab_lable,b.ab_value
from
d_abnormal_data_yyyymms b
where
ab_data_time = '2018-12-17 11:50:00';
#添加唯一索引
create unique index station_id_time_code_cycle_lable_value on d_abnormal_data_yyyymms(station_id,ab_data_time,item_code_id,data_cycle,ab_lable,ab_value);