现在oracle数据库有张表,数据如下。如何用一条SQL语句,删除重复数据,注册时间在1分钟之内的数据,都算重复数据。
姓名,学号,注册时间
张三,201111,2012-11-20 09:50:10
张三,201111,2012-11-20 09:50:15
张三,201111,2012-11-20 09:52:15
李四,201112,2012-11-20 10:30:50
李四,201112,2012-11-20 10:31:10
姓名,学号,注册时间
张三,201111,2012-11-20 09:50:10
张三,201111,2012-11-20 09:50:15
张三,201111,2012-11-20 09:52:15
李四,201112,2012-11-20 10:30:50
李四,201112,2012-11-20 10:31:10
SELECT '张三' AS XM,'201111' AS XH ,'2012-11-20 09:50:10' SJ FROM DUAL
UNION ALL
SELECT '张三' AS XM,'201111' AS XH ,'2012-11-20 09:50:15' SJ FROM DUAL
UNION ALL
SELECT '张三' AS XM,'201111' AS XH ,'2012-11-20 09:52:15' SJ FROM DUAL
UNION ALL
SELECT '李四' AS XM,'201112' AS XH ,'2012-11-20 10:30:50' SJ FROM DUAL
UNION ALL
SELECT '李四' AS XM,'201112' AS XH ,'2012-11-20 10:31:10' SJ FROM DUAL
)
SELECT XM,XH,SJ FROM(
SELECT NVL(TO_CHAR(TO_DATE(SJ, 'YYYY-MM-DD HH24:MI:SS'), 'MISS') -
TO_CHAR(TO_DATE(LAG(SJ, 1) over(partition by XH order by SJ),
'YYYY-MM-DD HH24:MI:SS'),
'MISS'),0) AS FLG,
XM,
XH,
SJ
FROM TEST)
WHERE FLG<60
SELECT '张三' AS XM,'201111' AS XH ,'2012-11-20 09:50:10' SJ FROM DUAL
UNION ALL
SELECT '张三' AS XM,'201111' AS XH ,'2012-11-20 09:50:15' SJ FROM DUAL
UNION ALL
SELECT '张三' AS XM,'201111' AS XH ,'2012-11-20 09:52:15' SJ FROM DUAL
UNION ALL
SELECT '李四' AS XM,'201112' AS XH ,'2012-11-20 10:30:50' SJ FROM DUAL
UNION ALL
SELECT '李四' AS XM,'201112' AS XH ,'2012-11-20 10:31:10' SJ FROM DUAL
)
SELECT XM,XH,SJ FROM(
SELECT
TO_CHAR(TO_DATE(SJ, 'YYYY-MM-DD HH24:MI:SS'), 'MISS') -
TO_CHAR(TO_DATE(min(SJ) keep (DENSE_RANK first ORDER BY SJ) over(partition by XH), 'YYYY-MM-DD HH24:MI:SS'), 'MISS') AS FLG,XM,XH,SJ FROM TEST)
WHERE FLG =0 OR FLG >60
with 是创建临时表用的,一模一样的数据也能删除吧
SELECT
TO_CHAR(TO_DATE(SJ, 'YYYY-MM-DD HH24:MI:SS'), 'MISS') -
TO_CHAR(TO_DATE(min(SJ) keep (DENSE_RANK first ORDER BY SJ) over(partition by XH), 'YYYY-MM-DD HH24:MI:SS'), 'MISS') AS FLG
,XM,XH,SJ,ROW_NUMBER()over(partition by XH ORDER BY SJ) as RN FROM TEST)
WHERE RN= 1 OR FLG >60
阿。。已经结帖了?害我倒腾半天。。555555555算了,虽然没分,还是把我的代码帖出来参考吧。。with
t1 as -- 查询出所有的重复数据
(
select * from u_time where (username,userno) in
( select username ,userno from u_time group by username ,userno having count(*)>1)
),
t2 as --去掉所有的重复数据的集合
(
select u1.u_date ,u1.rowid as uurowid ,u1.username ,u1.userno from u_time u1,
(select min(rowid) as urowid , username ,userno from u_time group by username ,userno having count(*) >1 ) u2
where u1.rowid = u2.urowid
)
select distinct t1.username ,t1.userno ,t1.u_date from t1,t2
where t1.username = t2.username
and t1.userno = t2.userno
and abs(t1.u_date - t2.u_date)*24*60*60 >=0
and abs(t1.u_date - t2.u_date)*24*60*60 <=60
--查询出注册时间在1分钟以内的数据
SQL文我建表试过,测试通过
u_time 的表结构如下create table u_time (
username char(7),
userno char(6),
u_date date
)测试数据就是楼主的数据。。啊啊啊啊啊啊啊啊啊啊啊啊啊啊。。苍天啊