Oracle 中去除重复列数据的sql？

Question

根据你的描述，大致意思是根据那三个字段分组，取相同记录的任意一条：select *
  from (select row_number() over(partition by batch.batch_id, batch.created_by, dt.DELIVERY_NO order by batch.created_date) rn batch.batch_id as b_id,
               batch.batch_name,
               batch.created_by,
               batch.created_date,
               dt.po_no,
               dt.pr_form_no as pr_no,
               pr.applicant_id,
               pr.REQUEST_ORG_ID,
               pr.SUBMIT_ON,
               dt.material_id,
               dt.material_name,
               dt.material_code,
               dt.DELIVERY_NO,
               dt.WAREHOUSE_ID,
               pr.REQUEST_ORG_NAME
          from pur_f_delivery_batch batch
         inner join pur_f_delivery_dt dt on dt.BATCH_ID = batch.batch_id
         inner join pur_pr_item_work item on item.id = dt.PR_WORK_ID
         inner join pur_pr pr on pr.id = item.pr_id)
 where rn = 1

Answer 1

用这个吧，上一个粘贴错了select *
  from (select row_number() over(partition by batch.batch_id, batch.created_by, dt.DELIVERY_NO order by batch.created_date) rn,
               batch.batch_id as b_id,
               batch.batch_name,
               batch.created_by,
               batch.created_date,
               dt.po_no,
               dt.pr_form_no as pr_no,
               pr.applicant_id,
               pr.REQUEST_ORG_ID,
               pr.SUBMIT_ON,
               dt.material_id,
               dt.material_name,
               dt.material_code,
               dt.DELIVERY_NO,
               dt.WAREHOUSE_ID,
               pr.REQUEST_ORG_NAME
          from pur_f_delivery_batch batch
         inner join pur_f_delivery_dt dt on dt.BATCH_ID = batch.batch_id
         inner join pur_pr_item_work item on item.id = dt.PR_WORK_ID
         inner join pur_pr pr on pr.id = item.pr_id)
 where rn = 1

Answer 2

上面语句字段挺多的，有没有测试数据，就给你提点建议吧。
将上面的结果用row_number() over(partition by 字段1,字段2,字段3 order by 字段1 )进行分组排序，
再取每组的第一条记录试试。

Answer 3

select *
  from (  select
               batch.batch_id as b_id,
               batch.batch_name,
               batch.created_by,
               batch.created_date,
               dt.po_no,
               dt.pr_form_no as pr_no,
               pr.applicant_id,
               pr.REQUEST_ORG_ID,
               pr.SUBMIT_ON,
               dt.material_id,
               dt.material_name,
               dt.material_code,
               dt.DELIVERY_NO,
               dt.WAREHOUSE_ID,
               pr.REQUEST_ORG_NAME
          from pur_f_delivery_batch batch
         inner join pur_f_delivery_dt dt on dt.BATCH_ID = batch.batch_id
         inner join pur_pr_item_work item on item.id = dt.PR_WORK_ID
         inner join pur_pr pr on pr.id = item.pr_id) tt
 where b_id in (select b_id from tt group by b_id,batch.created_by,dt.DELIVERY_NO having count(*)>1)
 and rowid not in(select min(rowid) from tt group by b_id,batch.created_by,dt.DELIVERY_NO having count(*)>1)