select * from ZB_RELI_ZDDXS t where t.RYMC_NAME in (select distinct RYMC_NAME from ZB_RELI_ZDDXS group by RYMC_NAME having count(RYMC_NAME)>1) and t.REPORTDATE between to_date('2008-7-28 11:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-7-28 11:00:59','yyyy-mm-dd hh24:mi:ss')
我这条语句把所有的都查出来了,但是我想有3条重复的查出2条并删掉,不知道怎么写
比如:
id name
11 张三
12 张三
13 张三
14 李四
15 李四
16 李四
delete from ZB_RELI_ZDDXS
where num_id in (select num_id from ZB_RELI_ZDDXS t where t.RYMC_NAME in (select distinct RYMC_NAME from ZB_RELI_ZDDXS group by RYMC_NAME having count(RYMC_NAME)>1) and t.REPORTDATE between to_date('2008-7-28 11:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-7-28 11:00:59','yyyy-mm-dd hh24:mi:ss'))
and rowid not in (select min(rowid) from ZB_RELI_ZDDXS t where t.RYMC_NAME in (select distinct RYMC_NAME from ZB_RELI_ZDDXS group by RYMC_NAME having count(RYMC_NAME)>1) and t.REPORTDATE between to_date('2008-7-28 11:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-7-28 11:00:59','yyyy-mm-dd hh24:mi:ss'))
这样也不可以,请教各位高人给指点一下
我这条语句把所有的都查出来了,但是我想有3条重复的查出2条并删掉,不知道怎么写
比如:
id name
11 张三
12 张三
13 张三
14 李四
15 李四
16 李四
delete from ZB_RELI_ZDDXS
where num_id in (select num_id from ZB_RELI_ZDDXS t where t.RYMC_NAME in (select distinct RYMC_NAME from ZB_RELI_ZDDXS group by RYMC_NAME having count(RYMC_NAME)>1) and t.REPORTDATE between to_date('2008-7-28 11:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-7-28 11:00:59','yyyy-mm-dd hh24:mi:ss'))
and rowid not in (select min(rowid) from ZB_RELI_ZDDXS t where t.RYMC_NAME in (select distinct RYMC_NAME from ZB_RELI_ZDDXS group by RYMC_NAME having count(RYMC_NAME)>1) and t.REPORTDATE between to_date('2008-7-28 11:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-7-28 11:00:59','yyyy-mm-dd hh24:mi:ss'))
这样也不可以,请教各位高人给指点一下
max(id)或min(id)
WHERE ROWID <>
(SELECT MAX(ROWID)
FROM ZB_RELI_ZDDXS T2
WHERE T1.RYMC_NAME = T2.RYMC_NAME
AND T2.REPORTDATE BETWEEN
TO_DATE('2008-7-28 11:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE('2008-7-28 11:00:59', 'yyyy-mm-dd hh24:mi:ss'))
AND REPORTDATE BETWEEN
TO_DATE('2008-7-28 11:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE('2008-7-28 11:00:59', 'yyyy-mm-dd hh24:mi:ss');
当然我要申明一下,这个写法不是我自己想出来的,我也是从别人那里学来的,依样画葫芦而已,呵呵.delete from ZB_RELI_ZDDXS where rowid in
(select rid
from (select rowid rid,row_number() over (partition by name order by rowid) rn
from ZB_RELI_ZDDXS )
where rn <> 1);
是使用分析函数结合ROWID
告诉你个
简单的方法create table table1 as select distinct * from table;
rename table1 to table;