一张表有这么几字段
CUSTOMI USERNUMBER SERVICEID CUSTOMDATE STATE
1 20507 1345176328 102 2008-12-11 1
2 20508 1345176328 65 2009-6-23 1
3 20509 1345176328 65 2009-6-23
4 20510 1345176328 65 2009-6-23 1
我想筛选后的的数据是:
1 20507 1345176328 102 2008-12-11 1
2 20508 1345176328 65 2009-6-23 1
CUSTOMI USERNUMBER SERVICEID CUSTOMDATE STATE
1 20507 1345176328 102 2008-12-11 1
2 20508 1345176328 65 2009-6-23 1
3 20509 1345176328 65 2009-6-23
4 20510 1345176328 65 2009-6-23 1
我想筛选后的的数据是:
1 20507 1345176328 102 2008-12-11 1
2 20508 1345176328 65 2009-6-23 1
where exists(
select 1 from table1 where CUSTOMI <t.CUSTOMI
and USERNUMBER =t.USERNUMBER
and SERVICEID =t.SERVICEID
and CUSTOMDATE =t.CUSTOMDATE
)
from (
select t.*,
row_number() over(partition by SERVICEID order by CUSTOMI asc) rn
from t
)
where rn = 1
FROM (SELECT row_number() over(PARTITION BY USERNUMBER, SERVICEID ORDER BY CUSTOMI) rn FROM table1)
WHERE rn = 1;
SELECT CUSTOMI, USERNUMBER, SERVICEID, CUSTOMDATE, STATE
FROM (SELECT row_number() over(PARTITION BY USERNUMBER, SERVICEID ORDER BY CUSTOMI) rn, table1.*
FROM table1)
WHERE rn = 1;
from (select tt.*,
row_number() over(partition by USERNUMBER, SERVICEID order by rownum) rn
from tt)
where rn = 1
order by CUSTOMI;
--删除重复数据有以下5种方法:
1.delete from table1 t
where exists(
select 1 from table1 where customi <t.customi
and usernumber =t.usernumber
and serviceid =t.serviceid
and customdate =t.customdate
)2.delete from table1 a
where a.rowid >(
select min(b.rowid)
from table1 b
where b.usernumber =a.usernumber
and b.serviceid =a.serviceid
and b.customdate =a.customdate
)3.delete from table1 a
where a.rowid <>(
select min(b.rowid)
from table1 b
where b.customi <a.customi
and b.usernumber =a.usernumber
and b.serviceid =a.serviceid
and b.customdate =a.customdate
)4.delete from table1 a
where exists(
select b.usernumber, b.serviceid, b.customdate,count(*)
from table1 b
group by b.usernumber, b.serviceid, b.customdate
having count(*) > 1
)
5.delete from table1 a
where exists(
select customi, usernumber, serviceid, customdate, state
from (select row_number() over(partition by usernumber, serviceid order by customi) rn from table1)
where rn = 1;
) 最高效的方法就是将重复的数据放到一张临时表中,然后关联大表(含重复数据的表)再进行删除。
1.delete from table1 t
where exists(
select 1 from table1 where customi <t.customi
and usernumber =t.usernumber
and serviceid =t.serviceid
and customdate =t.customdate
)2.delete from table1 a
where a.rowid >(
select min(b.rowid)
from table1 b
where b.usernumber =a.usernumber
and b.serviceid =a.serviceid
and b.customdate =a.customdate
)3.delete from table1 a
where a.rowid <>(
select min(b.rowid)
from table1 b
where b.usernumber =a.usernumber
and b.serviceid =a.serviceid
and b.customdate =a.customdate
)4.delete from table1 a
where exists(
select b.usernumber, b.serviceid, b.customdate,count(*)
from table1 b
group by b.usernumber, b.serviceid, b.customdate
having count(*) > 1
);
5.delete from table1 a
where exists(
select 1
from (select row_number() over(partition by usernumber, serviceid order by customi) rn
from table1
)
where rn = 1
);