如何从表中选择唯一记录 [英] How to select unique records from a table

问题描述

i有一个表名uniq_select_records，列

（sno number，
 item_name varchar2（ 50） ），
 start_date日期，
 end_date日期，
 action_flag  char （ 1 ），
 last_modified_date日期，
 creation_date日期）

sno ia我的列用于标识item_name的行，它不是唯一键或主键，但它不是null。 sno将出现在每个项目中。没有项目等于没有sno。样本

 sno item_name start_date end_date Action_flag last_modified creation_date 
 --- ----------- -------- ---------- ----------- ------------- -------- ----- 
  1  sample1 02-MAY-15 27-MAY-14 A-MAY-15 02-MAY-15 
  1  sample1 02-MAY-15 27-MAY-14 D 07-MAY-15 02-MAY-15 
  1  sample1 27-APR-15 06-JUN-14 C 07-MAY-15 02-MAY-15 
  1  sample1 27- APR-15 06-JUN-14 C 07-MAY-15 03-MAY-15 
  2  sample2 07-MAY-15 11-FEB-15 C 07-MAY-15 22-JAN-15 
  2  sample2 07-MAY-15 11-FEB-15 A-MAY-15 22-JAN- 15 
  2  sample2 07-MAY-15 01-FEB-15 C 12-MAY-15 12-MAY-15 

操作标志值C =更改，D =已删除且A =插入

样本数据如上所示。像这样我们有数百万条记录。现在我们必须选择记录每个项目的最新更改。每个项目可能没有相同的start_date和end_date。所有项目都没有在同一天修改。

任何人都可以建议如何获取每个项目的记录，这是项目的最新更改。



预期输出是

 sno item_name start_date end_date Action_flag last_modified creation_date 
 --- ---------- --------- ---------- ----------- ---------- --- ------------- 
 1 sample1 27-APR-15 06-JUN-14 C 07-MAY-15 03-MAY-15 
 2 sample2 07 -MAY-15 01-FEB-15 C 12-MAY-15 12-MAY-15 

提前感谢....

解决方案

  WITH  last_mod  AS （
  SELECT  
 sno 
，Max（last_modified_date）last_modified_date 
  FROM  uniq_select_records 
  GROUP   BY  sno 
）
  SELECT  
 u.sno 
，u.item_name 
，u.start_date 
，u.end_date 
，u.Action_flag 
，u。 last_modified_date 
，u.creation_date 
  FROM  uniq_select_records u 
  JOIN  last_mod l 
  ON  u.sno = l.sno 
  AND  u.last_modified_date = l.last_modified_date）

Hi,
i had a table name uniq_select_records with columns

(sno number, 
item_name varchar2(50),
start_date date, 
end_date date, 
action_flag char(1),
last_modified_date date, 
creation_date date)

sno i a column which i used to identify a row for a item_name and it is not unique or primary key but it was not null. sno will be present for each item. no of items is equal to no of sno. sample

sno   item_name start_date      end_date   Action_flag last_modified creation_date
---  ---------- ---------       ---------- ----------- ------------- -------------
1	sample1	02-MAY-15	27-MAY-14	A	07-MAY-15	02-MAY-15
1	sample1	02-MAY-15	27-MAY-14	D	07-MAY-15	02-MAY-15
1	sample1	27-APR-15	06-JUN-14	C	07-MAY-15	02-MAY-15
1	sample1	27-APR-15	06-JUN-14	C	07-MAY-15	03-MAY-15
2	sample2	07-MAY-15	11-FEB-15	C	07-MAY-15	22-JAN-15
2	sample2	07-MAY-15	11-FEB-15	A	07-MAY-15	22-JAN-15
2	sample2	07-MAY-15	01-FEB-15	C	12-MAY-15	12-MAY-15

Action flag values C=change, D=Deleted and A=insert

sample data is shown above. like this we have millions of records. now we have to select the to record which had the latest change for each item. each item may not have the same start_date and end_date.All the items weren't modified on the same date.
can anyone suggest idea how to fetch the records for each item which are latest change for an item.

expected output is

sno   item_name start_date      end_date   Action_flag last_modified creation_date
---  ---------- ---------       ---------- ----------- ------------- -------------
1   sample1 27-APR-15   06-JUN-14   C   07-MAY-15   03-MAY-15
2   sample2 07-MAY-15   01-FEB-15   C   12-MAY-15   12-MAY-15

thanks in advance....

解决方案

WITH last_mod AS (
    SELECT
            sno
           ,Max(last_modified_date) last_modified_date
    FROM    uniq_select_records
    GROUP BY sno
    )
SELECT
        u.sno
       ,u.item_name
       ,u.start_date
       ,u.end_date
       ,u.Action_flag
       ,u.last_modified_date
       ,u.creation_date
FROM    uniq_select_records u
JOIN    last_mod l
    ON  u.sno = l.sno
    AND u.last_modified_date = l.last_modified_date)

这篇关于如何从表中选择唯一记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！