oracle怎麼進行資料去重

去重方法：1.利用distinct關鍵字去重，語法「SELECT DISTINCT 欄位名稱FROM 表名;」；2、利用視窗函數row_number () over()去重；3、利用「group by」子句去重，語法「select 欄位名稱from 表名group by 欄位名稱;」；4、利用rowid進行偽列去重。

本教學操作環境：Windows7系統、Oracle 11g版、Dell G3電腦。

業務場景

需要查詢某數據，由於需要三張表關聯查詢，查詢結果如下：

原始SQL語句

SELECT 
  D.ORDER_NUM AS "申请单号" ,
  D.CREATE_TIME ,
  D.EMP_NAME AS "申请人",
  (SELECT extractvalue(t1.row_data,'/root/row/FI13_wasteName')
  FROM dat_table_row t1
  WHERE d.document_id = t1.document_id
  AND t1.table_id     = 'dynamicRowsIdPTFLXX'
  ) AS "废料名称",
  (SELECT extractvalue(t1.row_data,'/root/row/FI13_units')
  FROM dat_table_row t1
  WHERE d.document_id = t1.document_id
  AND t1.table_id     = 'dynamicRowsIdPTFLXX'
  ) AS "单位",
  (SELECT extractvalue(t1.row_data,'/root/row/FI13_estimate')
  FROM dat_table_row t1
  WHERE d.document_id = t1.document_id
  AND t1.table_id     = 'dynamicRowsIdPTFLXX'
  ) AS "预估数量",
  (SELECT extractvalue(t1.row_data,'/root/row/FI13_stockRemoval')
  FROM dat_table_row t1
  WHERE d.document_id = t1.document_id
  AND t1.table_id     = 'dynamicRowsIdPTFLXX'
  ) AS "累计出库数量",
  (SELECT extractvalue(t1.row_data,'/root/row/FI13_receivingTime')
  FROM dat_table_row t1
  WHERE d.document_id = t1.document_id
  AND t1.table_id     = 'dynamicRowsIdCGYTX'
  ) AS "收购方收货时间",
  (SELECT extractvalue(t2.row_data,'/root/row/FI13_collectionTime')
  FROM dat_table_row t2
  WHERE d.document_id = t2.document_id
  AND t2.table_id     = 'dynamicRowsIdPTSJSKSJ'
  ) AS "实际收款时间"
FROM dat_document d,
  dat_table_row dtr
WHERE d.form_name       ='FI14'
AND d.document_id       =dtr.document_id
AND (D.DOCUMENT_STATUS != 'deleted'
OR D.DOCUMENT_STATUS   IS NULL )
  --AND TO_CHAR(d.create_time,'yyyy-MM-dd') BETWEEN '2020-01-01' AND '2021-03-26'
AND d.order_num = 'FI1420210708002' --FI1420210708002
ORDER BY d.CREATE_TIME DESC;

方法1：distinct去重

SELECT DISTINCT可以用來篩選結果集中的重複行，確保SELECT子句中傳回指定的一列或多列的值是唯一的。

DISTINCT語句的語法如下：

SELECT DISTINCT column_1,
    column_2,
        ...
        FROM
    table_name;

範例：

SELECT 
  D.ORDER_NUM AS "申请单号" ,
  D.CREATE_TIME ,
  D.EMP_NAME AS "申请人",
  (SELECT extractvalue(t1.row_data,'/root/row/FI13_wasteName')
  FROM dat_table_row t1
  WHERE d.document_id = t1.document_id
  AND t1.table_id     = 'dynamicRowsIdPTFLXX'
  ) AS "废料名称",
  (SELECT extractvalue(t1.row_data,'/root/row/FI13_units')
  FROM dat_table_row t1
  WHERE d.document_id = t1.document_id
  AND t1.table_id     = 'dynamicRowsIdPTFLXX'
  ) AS "单位",
  (SELECT extractvalue(t1.row_data,'/root/row/FI13_estimate')
  FROM dat_table_row t1
  WHERE d.document_id = t1.document_id
  AND t1.table_id     = 'dynamicRowsIdPTFLXX'
  ) AS "预估数量",
  (SELECT extractvalue(t1.row_data,'/root/row/FI13_stockRemoval')
  FROM dat_table_row t1
  WHERE d.document_id = t1.document_id
  AND t1.table_id     = 'dynamicRowsIdPTFLXX'
  ) AS "累计出库数量",
  (SELECT extractvalue(t1.row_data,'/root/row/FI13_receivingTime')
  FROM dat_table_row t1
  WHERE d.document_id = t1.document_id
  AND t1.table_id     = 'dynamicRowsIdCGYTX'
  ) AS "收购方收货时间",
  (SELECT extractvalue(t2.row_data,'/root/row/FI13_collectionTime')
  FROM dat_table_row t2
  WHERE d.document_id = t2.document_id
  AND t2.table_id     = 'dynamicRowsIdPTSJSKSJ'
  ) AS "实际收款时间"
FROM dat_document d,
  dat_table_row dtr
WHERE d.form_name       ='FI14'
AND d.document_id       =dtr.document_id
AND (D.DOCUMENT_STATUS != 'deleted'
OR D.DOCUMENT_STATUS   IS NULL )
  --AND TO_CHAR(d.create_time,'yyyy-MM-dd') BETWEEN '2020-01-01' AND '2021-03-26'
AND d.order_num = 'FI1420210708002' --FI1420210708002
ORDER BY d.CREATE_TIME DESC;

注意：DISTINCT後面要跟ORDER BY的字段，Oracle先執行DISTINCT去重後，再使用ORDER BY進行排序的。所以如果在ORDER BY需要排序的字段，沒有在 distinct 後的字段中，自然會拋錯。

報錯資訊如下：

#方法2：row_number() over()

語法格式

select * from
(select A.*, row_number() over(partition by A.name1 order by A.name12 desc) rn from A)
where rn = 1

範例

select * from (
select 
  d.order_num as "申请单号" ,
  d.create_time ,
  d.emp_name as "申请人",
  (select extractvalue(t1.row_data,'/root/row/FI13_wasteName')
  from dat_table_row t1
  where d.document_id = t1.document_id
  and t1.table_id     = 'dynamicRowsIdPTFLXX'
  ) as "废料名称",
  (select extractvalue(t1.row_data,'/root/row/FI13_units')
  from dat_table_row t1
  where d.document_id = t1.document_id
  and t1.table_id     = 'dynamicRowsIdPTFLXX'
  ) as "单位",
  (select extractvalue(t1.row_data,'/root/row/FI13_estimate')
  from dat_table_row t1
  where d.document_id = t1.document_id
  and t1.table_id     = 'dynamicRowsIdPTFLXX'
  ) as "预估数量",
  (select extractvalue(t1.row_data,'/root/row/FI13_stockRemoval')
  from dat_table_row t1
  where d.document_id = t1.document_id
  and t1.table_id     = 'dynamicRowsIdPTFLXX'
  ) as "累计出库数量",
  (select extractvalue(t1.row_data,'/root/row/FI13_receivingTime')
  from dat_table_row t1
  where d.document_id = t1.document_id
  and t1.table_id     = 'dynamicRowsIdCGYTX'
  ) as "收购方收货时间",
  (select extractvalue(t2.row_data,'/root/row/FI13_collectionTime')
  from dat_table_row t2
  where d.document_id = t2.document_id
  and t2.table_id     = 'dynamicRowsIdPTSJSKSJ'
  ) as "实际收款时间",
  row_number() over(partition by d.order_num  order by d.create_time desc) rn 
from dat_document d,
  dat_table_row dtr
where d.form_name       ='FI14'
and d.document_id       =dtr.document_id
and (d.document_status != 'deleted'
or d.document_status   is null )
  --AND TO_CHAR(d.create_time,'yyyy-MM-dd') BETWEEN '2020-01-01' AND '2021-03-26'
and d.order_num = 'FI1420210708002' --FI1420210708002
) where rn = 1;

查詢結果

方法3：group by

select 字段名 from 表名
group by 字段名;

方法4：利用rowid（偽列去重）

select id,name,age from test t1
where t1.rowid in (select min(rowid) from test t2 where t1.name=t2.name and t1.age=t2.age);

推薦教學：《 Oracle教學》

以上是oracle怎麼進行資料去重的詳細內容。更多資訊請關注PHP中文網其他相關文章！