不多说表:table1{id,app_id,create_user,create_time}app_id 和 create_user 相同的为重复数据 (即两条数据,app_id相等,并且cureate_user相等,那么就是有重复数据)要留 create_time 最大(最新)的数据,删除其他重复的。
上语句 满分, 带解释的,加分
上语句 满分, 带解释的,加分
解决方案 »
- orcale超过最大连接数的问题
- 如何使用游标值作为字段名称使用?具体如何写?
- in+Count某字段的时候如何把结果行补零?
- 【求助】oracle 和 MySQL哪个好学!
- 请问一个left join的问题
- Oracle不同登录方式的去别是什么?
- 请教关于bulk collect的问题?
- 学习 oracle (9i的)吗?这里有视频教学
- oracle数据库中执行操纵sqlserver的语句.( oracle 与sqlserver的同步问题)
- 表 t1 ,字段 c1 ,c1不是主键所以有重复。要选择c1(不重复),SQL语句如何写?拜托!
- oracle 优化方面的问题!!!
- Hibernate+Oracle 获取数据异常——只返回10条
不理解,再问我。呵呵declare
v_appid varchar2(100) := "";
cursor mycur is
select id,app_id,create_user,create_time from table1
order by app_id,create_time desc;
r_mycur table1%rowtype;
begin open mycur;
loop
fetch mycur into r_mycur;
exit when mycur%notfound;
if(v_appid = r_mycur.app_id) then
delete table1
where app_id = r_mycur.app_id
and create_time = r_mycur.app_id;
end if;
v_appid := r_mycur.app_id;
end loop;
close mycur;
commit;end;
(
id int,
app_id int,
create_user varchar2(10),
created_time date
);BEGIN
FOR i IN 1..20 LOOP
INSERT INTO table1 VALUES(i, mod(i,2), TO_CHAR(mod(i,4)), SYSDATE + i);
END LOOP;
COMMIT;
END;
/DELETE FROM table1
WHERE rowid IN (
SELECT rowid
FROM (
SELECT rowid, app_id, create_user, rank() over(partition by app_id, create_user order by created_time desc) rn, created_time
FROM table1
)
WHERE rn > 1
);
delete from table1
where id in (select id
from (select t.id,
t.create_time,
max(create_time) over(partition by app_id, create_user) maxtime,--按app_id, create_user分组取最大时间
count(1) over(partition by app_id, create_user) grpcnt --计算按app_id, create_user分组时每组中的记录条数
from table1 t
order by id) t
where t.grpcnt > 1
and t.create_time = t.maxtime)
with
t1 as (select id,app_id,count(*) cnt from table1 group by id,app_id having count(*)>1), --查找重复记录
t2 as (select id,app_id,max(create_time) c_time from table1 group by id,app_id), --查找最大时间
t3 as (select t1.id,t1.app_id,t2.c_time from t1,t2 where t1.id=t2.id and t1.app_id=t2.app_id), --关联重复记录的最大时间
delete from table1 a where exists (select 1 from t3 where a.id=t3.id and a.app_id=t3.app_id and a.create_time<>t3.c_time) --删除不是最大时间的记录
INSERT INTO test VALUES ('1','1','jack','20120601 12:01:00');
INSERT INTO test VALUES ('2','1','jack','20120601 12:50:00');
INSERT INTO test VALUES ('3','1','jack','20120601 23:01:00');
INSERT INTO test VALUES ('4','1','rose','20120615 12:01:00');
INSERT INTO test VALUES ('5','1','rose','20120616 12:01:00');DELETE FROM test t WHERE NOT EXISTS
(SELECT 1 FROM
(SELECT DISTINCT app_id,create_user, Last_Value(create_time) over (PARTITION BY app_id,create_user ORDER BY create_time rows between unbounded preceding and unbounded following)create_time
FROM test
)
WHERE app_id=t.app_id AND create_user=t.create_user AND create_time=t.create_time
)SELECT * FROM test;ID, APP_ID, CREATE_USER, CREATE_TIME
3 1 jack 20120601 23:01:00
5 1 rose 20120616 12:01:00
借用一下数据
DELETE FROM TEST
WHERE (CREATE_TIME, ROWID) NOT IN
(SELECT MAX(CREATE_TIME), MAX(ROWID)
FROM TEST
GROUP BY APP_ID, CREATE_USER);