REMOVE DUPLICATE RECORD(2)

繼上次會出現重複的資料
先用group by 把重複的資料拉出來
後來用輸出Insert 指令
最後delete掉後再把剛剛的insert指令丟進來


後來又找相關資料
http://scottelkin.com/programming/find-and-delete-duplicates-in-sql/
這個改天再來玩

但後來發現可以先create view
然後把重複資料塞進來
delete原本db裡面重複的資料
再把view的資料塞進來

就是把上次的動作，用view做掉


CREATE VIEW TEMP1
AS
select * from fbeu.cas_p_trvrat where ratnam = 'R050' and trvltype = 'G' and channel = 20 AND DAYS = 2 and INS_MED = 1500000 AND ROWNUM = 1

DROP VIEW TEMP

另外又無意間發現新的東西 PARTITION BY

SELECT RATNAM, RANK() OVER (PARTITION BY RATNAM ORDER BY NUM) RNK 

FROM (SELECT ROWNUM NUM, CAS.* FROM FBEU.CAS_P_TRVRAT CAS WHERE RATNAM = 'R050' AND TRVLTYPE = 'G' AND CHANNEL = 20 AND DAYS = 2) X

http://my-plsql.blogspot.tw/2010/02/over-partition.html

把資料分割，先這樣改天再來玩