分析函数Row_Number()解决重复问题

一、示例1：

Select *
  From (Select a.Product_Num,
               a.Order_Id,
               a.Apply_Date,
               Row_Number() Over(Partition By a.Product_Num Order By a.Order_Id Desc) Rn
          From t_Od_Order a
         Where Rownum < 999) t
 Where t.Rn = 1;

update temp_zx_030602_checktf aa
   set aa.v_nedate =
       (Select send_time
          From (Select a.send_time,
                       a.so_nbr,
                       Row_Number() Over(Partition By a.so_nbr Order By a.spjk_id Desc) Rn
                  From spjk_table a
                 Where Rownum < 999) t
         Where t.Rn = 1
           and aa.trade_id = to_number(t.so_nbr))
 where aa.v_nedate is null;

　　

二、示例2

--001+002 
---- 001. 
Select Yy.Error_Reason_Code,
       Yy.Error_Reason,
       Part,
       Yyt_Cnt,
       To_Char(Yyt_Cnt * 100 / Round(Sum(Yyt_Cnt) Over(Partition By Part)),
               'fm999990.00') || '%' Yyt_Percent,
       Dx_Cnt,
       To_Char(Dx_Cnt * 100 / Round(Sum(Dx_Cnt) Over(Partition By Part)),
               'fm999990.00') || '%' Dx_Percent,
       Dq_Cnt,
       To_Char(Dq_Cnt * 100 / Round(Sum(Dq_Cnt) Over(Partition By Part)),
               'fm99990.00') || '%' Dq_Percent,
       Cnt,
       To_Char(Cnt * 100 / Round(Sum(Cnt) Over(Partition By Part)),
               'fm999990.00') || '%' Sum_Percent
  From (Select Tt.Error_Reason_Code,
               Tt.Error_Reason,
               Cnt,
               0                    Part,
               Yyt                  Yyt_Cnt,
               Dq                   Dq_Cnt,
               Dx                   Dx_Cnt
          From (Select (Select Er.Error_Reason_Code
                          From t_Od_Error_Reason Er
                         Where Er.Error_Reason = Aa.Error_Reason
                           And Rownum = 1) Error_Reason_Code,
                       Aa.Error_Reason Error_Reason,
                       Count(1) Cnt,
                       Sum(Case
                             When Aa.Apply_Dept_Name Like '%营业厅%' Then
                              1
                             Else
                              0
                           End) Yyt,
                       Sum(Case
                             When Aa.Apply_Dept_Name = '电销自营店' Then
                              1
                             Else
                              0
                           End) Dx,
                       Sum(Case
                             When Aa.Apply_Dept_Name = '电渠自营店' Then
                              1
                             Else
                              0
                           End) Dq
                  From (Select *
                          From (Select a.*,
                                       Row_Number() Over(Partition By a.Order_Id Order By a.Send_Date Desc) Rn
                                  From t_Stat_Td_Iptv_Detail_01@Iom107 a) t
                         Where t.Rn = 1) Aa
                 Group By Aa.Error_Reason
                 Order By 1) Tt) Yy
Union All

---- 002. 合计
Select Null,
       '合计',
       1,
       Sum(u.Yyt_Cnt) Yyt_Cnt,
       Sum(To_Number(Replace(u.Yyt_Percent, '%'))) || '%' Yyt_Percent,
       Sum(u.Dx_Cnt) Dx_Percent,
       Sum(To_Number(Replace(u.Dx_Percent, '%'))) || '%' Dx_Percent,
       Sum(u.Dq_Cnt) Dq_Percent,
       Sum(To_Number(Replace(u.Dq_Percent, '%'))) || '%' Dq_Percent,
       Sum(u.cnt),
       Sum(To_Number(Replace(u.sum_percent, '%'))) || '%' sum_Percent
  From (Select Yy.Error_Reason_Code,
               Yy.Error_Reason,
               Part,
               Yyt_Cnt,
               To_Char(Yyt_Cnt * 100 /
                       Round(Sum(Yyt_Cnt) Over(Partition By Part)),
                       'fm999990.00') || '%' Yyt_Percent,
               Dx_Cnt,
               To_Char(Dx_Cnt * 100 /
                       Round(Sum(Dx_Cnt) Over(Partition By Part)),
                       'fm999990.00') || '%' Dx_Percent,
               Dq_Cnt,
               To_Char(Dq_Cnt * 100 /
                       Round(Sum(Dq_Cnt) Over(Partition By Part)),
                       'fm99990.00') || '%' Dq_Percent,
               Cnt,
               To_Char(Cnt * 100 / Round(Sum(Cnt) Over(Partition By Part)),
                       'fm999990.00') || '%' Sum_Percent
        
          From (Select Tt.Error_Reason_Code,
                       Tt.Error_Reason,
                       Cnt,
                       0                    Part,
                       Yyt                  Yyt_Cnt,
                       Dq                   Dq_Cnt,
                       Dx                   Dx_Cnt
                
                  From (Select (Select Er.Error_Reason_Code
                                  From t_Od_Error_Reason Er
                                 Where Er.Error_Reason = Aa.Error_Reason
                                   And Rownum = 1) Error_Reason_Code,
                               Aa.Error_Reason Error_Reason,
                               Count(1) Cnt,
                               Sum(Case
                                     When Aa.Apply_Dept_Name Like '%营业厅%' Then
                                      1
                                     Else
                                      0
                                   End) Yyt,
                               Sum(Case
                                     When Aa.Apply_Dept_Name = '电销自营店' Then
                                      1
                                     Else
                                      0
                                   End) Dx,
                               Sum(Case
                                     When Aa.Apply_Dept_Name = '电渠自营店' Then
                                      1
                                     Else
                                      0
                                   End) Dq
                          From (Select *
                                  From (Select a.*,
                                               Row_Number() Over(Partition By a.Order_Id Order By a.Send_Date Desc) Rn
                                          From t_Stat_Td_Iptv_Detail_01@Iom107 a) t
                                 Where t.Rn = 1) Aa
                         Group By Aa.Error_Reason
                         Order By 1) Tt) Yy) u;