在开发或者维护数据的过程中,经常需要对一些垃圾数据进行清理:
下面提供一种查询是否存在重复id 的方式:
SELECT id,count(1) FROM TABLE_NAME GROUP BY id HAVING count(1) > 1;
SELECT id,count(1) FROM TABLE_NAME GROUP BY id HAVING count(1) > 1;
select *
from (
select id,
FINDTIME,
substr(FINDTIME, 1, 4) as yyyy,
substr(FINDTIME, 6, 2) as mm,
substr(FINDTIME, 9, 2) as dd,
substr(FINDTIME, 12, 2) as HH24,
substr(FINDTIME, 15, 2) as MI,
substr(FINDTIME, 18, 2) as SS,
substr(FINDTIME, 20) as timea
from TABLE_NAME
) t
where length(FINDTIME) <> 19
or t.SS > 59
or t.SS < 0;