1.通过distinct 查出重复数据
select distict orderNum from OrderBill
2.通过分组统计查出大于1此以上orderNum
select * from OrderBill
where orderNum in (select orderNum from OrderBill group by orderNum having count(orderNum)>1)
3.通过 id,或者时间等其他字段区分自己要使用的字段
select * from OrderBill
where orderNum in (select orderNum from OrderBill group by orderNum having count(orderNum)>1)
and id in (select max(id) from OrderBill group by orderNum having count(orderNum )>1)
4.此处可用多条件关联筛选
select * from OrderBill
where (o.orderNum,deputyOrderNum) in (select orderNum,deputyOrderNum from OrderBill group by orderNum,deputyOrderNum having count(orderNum)>1)
and id in (select max(id) from OrderBill group by orderNum having count(orderNum )>1)
以上就是去重的思路,删除直接将 “select * ” 替换为delete 执行即可。
:+add
统计日出现次数
select substr(CREATED_TIME,0,10)as time, COUNT(*) as 合计 from INF_BILL_OSH_UPLOAD where CREATED_TIME > ‘2020-05-01’ and CREATED_TIME < ‘2020-05-31’ and business_type =2 and zt = ‘N’
GROUP BY substr(CREATED_TIME,0,10)
ORDER BY substr(CREATED_TIME,0,10) desc
参见: Oracle
–按日统计
SELECT TO_CHAR(T.MODIFIEDTIME,‘YYYY-MM-DD’) TIME,COUNT(*) COUNT
FROM TEST T --这里可加查询条件
WHERE TO_CHAR(T.MODIFIEDTIME,‘YYYY’) = TO_CHAR(SYSDATE,‘YYYY’)
GROUP BY TO_CHAR(T.MODIFIEDTIME,‘YYYY-MM-DD’) --根据日期来分组
ORDER BY TO_CHAR(T.MODIFIEDTIME,‘YYYY-MM-DD’) ASC NULLS LAST–根据日期排序