时间戳

首页 > 解决方案 > 当一列具有唯一值时如何删除重复记录

sql - 当一列具有唯一值时如何删除重复记录

问题描述

使用表中的以下查询：

select * 
from hourly_report_table 
where API_HOUR = 9 
  and API_DATE = date '2020-09-30' 
  and total_trans = 72506;

下表中有重复记录，我们如何删除它：

ID  APPLICATION API_DATE    API_HOUR    SO  APP API ACTUAL_API  AVG_RUN TOTAL_TRANS GOOD_TRANS  FAIL_TRANS  FAIL_PERC   COUNTS_TO1  PERC_TO1    COUNTS_TO15 PERC_TO15   COUNTS_OVER15   PERC_OVER15 COUNTS_1TO5 PERC_1TO5   COUNTS_5TO10    PERC_5TO10  COUNTS_10TO15   PERC_10TO15 COUNTS_15TO30   PERC_15TO30 COUNTS_30TO60   PERC_30TO60 COUNTS_OVER60   PERC_OVER60 CREATED_USER_ID CREATED_TIME_STAMP  METRIC  AVG_RUN_GOOD    AVG_RUN_FAIL
225344087   LS  30-Sep-20   9       G2  GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506   72505   1   1.3792E-05  72007   0.993117811 72477   0.999600033 29  0.000399967 403 0.005558161 52  0.000717182 15  0.000206879 12  0.000165504 13  0.000179296 4   5.51678E-05 UFOSODRPT   4-Oct-20    A   0.176561258 20.256
225278469   LS  30-Sep-20   9       G2  GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506   72505   1   1.3792E-05  72007   0.993117811 72477   0.999600033 29  0.000399967 403 0.005558161 52  0.000717182 15  0.000206879 12  0.000165504 13  0.000179296 4   5.51678E-05 UFOSODRPT   4-Oct-20    A   0.176561258 20.256
224980737   LS  30-Sep-20   9       G2  GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506   72505   1   1.3792E-05  72007   0.993117811 72477   0.999600033 29  0.000399967 403 0.005558161 52  0.000717182 15  0.000206879 12  0.000165504 13  0.000179296 4   5.51678E-05 UFOSODRPT   4-Oct-20    A   0.176561258 20.256
225548611   LS  30-Sep-20   9       G2  GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506   72505   1   1.3792E-05  72007   0.993117811 72477   0.999600033 29  0.000399967 403 0.005558161 52  0.000717182 15  0.000206879 12  0.000165504 13  0.000179296 4   5.51678E-05 UFOSODRPT   4-Oct-20    A   0.176561258 20.256
225452770   LS  30-Sep-20   9       G2  GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506   72505   1   1.3792E-05  72007   0.993117811 72477   0.999600033 29  0.000399967 403 0.005558161 52  0.000717182 15  0.000206879 12  0.000165504 13  0.000179296 4   5.51678E-05 UFOSODRPT   4-Oct-20    A   0.176561258 20.256

感谢尝试进行要求的更改，但它以某种方式从表中删除了所有 5 条记录：-

delete  from hourly_report_table
    where id not in (select id
                     from (select max(id) id, application, api_date, api_hour, so
                           from test
                           group by application, api_date, api_hour, so
                          )
                     ) and API_HOUR=9 and API_DATE=date '2020-09-30' and total_trans=72506;
                     
           5 rows deleted.

谢谢，再次尝试使用请求的命令，但它再次在 2017 年 3 月 23 日之后的输出中提供数据。

   select *  from hourly_report_table 
Where Id Not in
          (Select max(Id)
            from hourly_report_table  where API_HOUR=9 and API_DATE=date '2020-09-30' and total_trans=72506
            group by APPLICATION, API_DATE, API_HOUR, SO, APP, API, ACTUAL_API, AVG_RUN, AVG_RUN_GOOD, AVG_RUN_FAIL, 
TOTAL_TRANS, GOOD_TRANS, FAIL_TRANS, FAIL_PERC, COUNTS_TO1, PERC_TO1, COUNTS_TO15, PERC_TO15, COUNTS_OVER15, PERC_OVER15, 
COUNTS_1TO5, PERC_1TO5, COUNTS_5TO10, PERC_5TO10, COUNTS_10TO15, PERC_10TO15, COUNTS_15TO30, PERC_15TO30, COUNTS_30TO60, 
PERC_30TO60, COUNTS_OVER60, PERC_OVER60, CREATED_USER_ID, CREATED_TIME_STAMP, METRIC, AVG_RUN_GOOD, AVG_RUN_FAIL);
        
        
        
24134557    TSNR    23-MAR-17   3       CSI InquireWirelineServiceMaintenanceDetails_POTSWtn    InquireWirelineServiceMaintenanceDetails_POTSWtn    1.344
24134558    TSNR    23-MAR-17   3       RTTP    STB_SEND_MESSAGE    RTTPSendMessageToSTB    1.099
24134559    TSNR    23-MAR-17   3       CSI InquireFiberServiceOrderDetail_Detail   InquireFiberServiceOrderDetail_Detail   0.976820512820513
24134560    TSNR    23-MAR-17   3       CMS GetLiveData_5031NV-030  GetLiveData_5031NV-030  20.828

以下是表 hourly_report_table 中的记录：

ID APPLICATION API_DATE API_HOUR SO APP API ACTUAL_API AVG_RUN TOTAL_TRANS GOOD_TRANS FAIL_TRANS FAIL_PERC COUNTS_TO1 PERC_TO1 COUNTS_TO15 PERC_TO15 COUNTS_OVER15 PERC_OVER15 COUNTS_1TO5 PERC_1TO5 COUNTS_5TO10 PERC_5TO10 COUNTS_10TO15 PERC_10TO15 COUNTS_15TO30 PERC_15TO30 COUNTS_30TO60 PERC_30TO60 COUNTS_OVER60 PERC_OVER60 CREATED_USER_ID CREATED_TIME_STAMP METRIC AVG_RUN_GOOD AVG_RUN_FAIL



SAMPLE DATA IN TABLE, ABOVE IS COLUMN NAME AND BELOW CORRESPONDING VALUES, BELOW VALUES ARE HAVING IDENTICAL, SO WE NEED TO KEEP ONE AND REMOVE ALL OTHER DUPLICATES VALUES FROM RECORDS.

225344087 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

225278469 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

224980737 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

225548611 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

225452770 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

在以下列中找到十进制值：

FAIL_PERC：0.0000137919620445205 PERC_T01：0.993117810939784 PERC_T015：0.999600033100 PERC_OVER15=0.0003999 AVG_RUN_GOOD：0.17656 AVG_RUN_FAIL：2

表名：hourly_report_table

标签： sqloracleplsqloracle-sqldeveloper

解决方案

假设唯一列是“Id”，重复列是 col1, col2 & col3 (Table : myTable) ；你可以简单地做 -

Delete from myTable
Where Id Not in
          (Select max(Id)
            from myTable
            group by col1, col2, col3);

编辑：这也适用于大量记录。

更新：您还应该在外面指定您的 where 条件，否则它会考虑所有不来自子查询的 id。这是为了确保将 Id not in condition 应用于满足另一组标准的记录子集，而不是在全局范围内应用。请参考下文。

 select *  from hourly_report_table 
 Where Id Not in
      (Select max(Id)
        from hourly_report_table  where API_HOUR=9 and API_DATE=date '2020-09-30' and total_trans=72506
        group by APPLICATION, API_DATE, API_HOUR, SO, APP, API, ACTUAL_API, AVG_RUN, AVG_RUN_GOOD, AVG_RUN_FAIL, 
TOTAL_TRANS, GOOD_TRANS, FAIL_TRANS, FAIL_PERC, COUNTS_TO1, PERC_TO1, COUNTS_TO15, PERC_TO15, COUNTS_OVER15, PERC_OVER15, 
COUNTS_1TO5, PERC_1TO5, COUNTS_5TO10, PERC_5TO10, COUNTS_10TO15, PERC_10TO15, COUNTS_15TO30, PERC_15TO30, COUNTS_30TO60, 
PERC_30TO60, COUNTS_OVER60, PERC_OVER60, CREATED_USER_ID, CREATED_TIME_STAMP, METRIC, AVG_RUN_GOOD, AVG_RUN_FAIL)
and API_HOUR=9 and API_DATE=date '2020-09-30' and total_trans=72506;

推荐阅读