sql 查重与去重

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–根据日期排序