常用SQL指令

SQL去重复语句：select 改为Delete

 

select * from TRWTC01 where ITEM_NAME in (select ITEM_NAME from TRWTC01 group by

ITEM_NAME,ITEM_TYPE,ATTRIBUT_1,ATTRIBUT_2,ATTRIBUT_3,ATTRIBUT_4 having count(ITEM_NAME) > 1) and REC_ID not in

(select min(REC_ID) from TRWTC01 group by ITEM_NAME,ITEM_TYPE,ATTRIBUT_1,ATTRIBUT_2,ATTRIBUT_3,ATTRIBUT_4 having count(REC_ID )>1  )

--查重复

select * from trwtp10 where REVESE_IRON_END_TIME in (select REVESE_IRON_END_TIME from trwtp10 group by REVESE_IRON_END_TIME  having count(REVESE_IRON_END_TIME) > 1)

--分组排序只保留第一条

delete from trwtp10 where REC_ID not in (

SELECT REC_ID  from

(

SELECT  Rank() over(PARTITION BY REVESE_IRON_END_TIME ORDER BY REC_CREATE_TIME desc) rowno,  t.* FROM trwtp10 t

)

WHERE rowno=1

)