Row_number 应用

           

检索数据插入到表中，要求插入的数据无重复

  INSERT INTO TEMWT (
                        COP_CD,
                        LGIID_CD,
                        TOK_CD,
                        TOKKAI_KB,
                        HIN_CD
                        )  

             SELECT     '001' ,
                                '0023' ,
                                TOKHNY_CD,
                                TOKKAI_KB,
                                HIN_CD
                  FROM(                       
                         SELECT '001' ,
                                '0023' ,
                                TOKHNY_CD,
                                TOKKAI_KB,
                                HIN_CD ,
                                ROW_NUMBER() OVER(PARTITION BY TOKHNY_CD,TOKKAI_KB,HIN_CD ORDER BY TOKHNY_CD,TOKKAI_KB,HIN_CD) AS RN
                           FROM TOKKYKTNKMT TKM
                          WHERE TKM.COP_CD = '001'
                            AND TKM.TOKHNY_CD = '000002'
                            AND TKM.DELRNR_FL = 0

                         ) WHERE RN = 1

内层select 中Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。

外层select 条件where rn=1，重复的数据中取一条即可，从而保证了插入TEMWT表中的数据没有重复的。