现有这样一个需求:
对于同一个objectid下的所有记录均满足
以下任意一个条件,则删除之:
(1)nettype not in(2,3) status = 4
(2)nettype in(2,3) status in(2,4,5)
--原表部分字段数据如下:
nettype objectid status
2 1 2
3 1 4
4 1 3
2 2 4
2 2 5
1 3 4
1 3 5
2 3 3
3 4 2
3 4 5
4 5 4
4 5 3--执行删除操作后,查询结果为:
nettype objectid status
2 1 2
3 1 4
4 1 3 --nettype=4(即not in(2,3)),而status<>4,则objectid对应的id都不能删
1 3 4
1 3 5
2 3 3 --nettype=2(即nettype in(2,3)),而status=3(即<>all(2,4,5)),则objectid对应的id都不能删
4 5 4
4 5 3
希望大家帮忙解答!谢谢!
对于同一个objectid下的所有记录均满足
以下任意一个条件,则删除之:
(1)nettype not in(2,3) status = 4
(2)nettype in(2,3) status in(2,4,5)
--原表部分字段数据如下:
nettype objectid status
2 1 2
3 1 4
4 1 3
2 2 4
2 2 5
1 3 4
1 3 5
2 3 3
3 4 2
3 4 5
4 5 4
4 5 3--执行删除操作后,查询结果为:
nettype objectid status
2 1 2
3 1 4
4 1 3 --nettype=4(即not in(2,3)),而status<>4,则objectid对应的id都不能删
1 3 4
1 3 5
2 3 3 --nettype=2(即nettype in(2,3)),而status=3(即<>all(2,4,5)),则objectid对应的id都不能删
4 5 4
4 5 3
希望大家帮忙解答!谢谢!
or (nettype in(2,3) and status in(2,4,5))????
nettype not in(2,3) status = 4
nettype in(2,3) status in(2,4,5)
哈哈 玩玩
with t as
(select nettype , objectid , status from table1
where nettype not in(2,3) and status = 4
union all
select nettype , objectid , status from table1
nettype in(2,3) and status in(2,4,5))
)
delete you_table where
nettype in (select netype from t) and status in( select status from t);
commit;
貌似效率不高
这个
with t as
(select nettype , status from you_table
where nettype not in(2,3) and status = 4
union all
select nettype , status from you_table
where nettype in(2,3) and status in(2,4,5))
)
delete you_table where
nettype in (select netype from t) and status in( select status from t);
commit;
我自己之前写的:
select *
from tb a
where (nettype not in(2,3) and status = 4)
or (nettype in(2,3) and status in(2,4,5))
and not exists(
select 1
from tb b
where b.id = a.id --id 为tb的主键
and nettype not in(2,3)
and status <> 4
and rownum = 1 union all
select 1
from tb c
where c.id = a.id --id 为tb的主键
and nettype in(2,3)
and status <> all(2,4,5)
and rownum = 1
) 不知道,为什么不能过滤:nettype = 1, status = 5这种情况。 解决这种情况,问题就都解决了!希望大家帮忙想想办法!
nettype objectid status
2 2 4
2 2 5
3 4 2
3 4 5
你删除的这4条记录,怎么满足你的条件?
以下任意一个条件,则删除之:
(1)nettype not in(2,3) status = 4
(2)nettype in(2,3) status in(2,4,5)我的理解是直接删除STATUD=4的所有记录,同时删除nettype in(2,3) 并且status in(2,5)的记录
from tb a
where
(select count(1) from tb b where b.objectid = a.objectid and ((nettype not in(2,3) and status = 4) or (nettype in(2,3) and status in(2,4,5))))
=(select count(1) from tb b where b.objectid = a.objectid)
select *
from tb a
where
not exists(select 1 from tb b where b.objectid = a.objectid and not ((nettype not in(2,3) and status = 4) or (nettype in(2,3) and status in(2,4,5))))
Connected to Oracle Database 10g Release 10.1.0.2.0
Connected as UCNZP
SQL>
SQL> with tmp as(
2 select '2' nettype ,'1' objectid,'2' status from dual
3 union all
4 select '3','1','4' from dual
5 union all
6 select '4','1','3' from dual
7 union all
8 select '2','2','4' from dual
9 union all
10 select '2','2','5' from dual
11 union all
12 select '1','3','4' from dual
13 union all
14 select '1','3','5' from dual
15 union all
16 select '2','3','3' from dual
17 union all
18 select '3','4','2' from dual
19 union all
20 select '3','4','5' from dual
21 union all
22 select '4','5','4' from dual
23 union all
24 select '4','5','3' from dual
25 )
26 select * from tmp where objectid in(
27 select distinct objectid from (
28 select a.*,
29 case when nettype not in(2,3) and status!='4' then '1'
30 when nettype in(2,3) and status not in(2,4,5) then '2'
31 else '0'
32 end case
33 from tmp a) c
34 where case!='0');NETTYPE OBJECTID STATUS
------- -------- ------
4 1 3
3 1 4
2 1 2
2 3 3
1 3 5
1 3 4
4 5 3
4 5 48 rows selectedSQL>
将上面的select 改为delete case='0' 就是你的要求
delete from tmp where objectid not in(
select distinct objectid from (
select a.*,
case when nettype not in(2,3) and status!='4' then '1'
when nettype in(2,3) and status not in(2,4,5) then '2'
else '0'
end case
from tmp a) c
where case!='0');
delete from tmp where objectid not in(
select distinct objectid from (
select a.*,
case when nettype not in(2,3) and status='4' then '1'
when nettype in(2,3) and status in(2,4,5) then '2'
else '0'
end case
from tmp a)
where case='0');
where exists (select 1 from t t2 where ((t2.nettype not in (2,3) and t2.status <> 4) or (t2.nettype in (2,3) and t2.status not in (2,4,5))) and t2.objectid = t1.objectid);注:把上面的表名t替换成需要的表名。
按照你的要求(1)nettype not in(2,3) status = 4
(2)nettype in(2,3) status in(2,4,5)
只要满足其中之一就要删除记录
那么1楼的兄弟的回答是没有错的!
还有按照你给的条件,nettype = 1, status = 5这种情况是不应当被删除的!
--nettype=4(即not in(2,3)),而status<>4,则objectid对应的id都不能删
--nettype=2(即nettype in(2,3)),而status=3(即<>all(2,4,5)),则objectid对应的id都不能删那楼主的意思的意思应该是:
如果某个id下的记录,只要存在一条记录,都不在那俩条件内,那么这个id就不删不知是不是楼主的意思如此说来,就得用not exists了
shan1119和gelyon的都是对的! 加上我自己写的!现在已经有三种解决方案!
with t as(
select '2' nettype ,'1' id,'2' status from dual
union all
select '3','1','4' from dual
union all
select '4','1','3' from dual
union all
select '2','2','4' from dual
union all
select '2','2','5' from dual
union all
select '1','3','4' from dual
union all
select '1','3','5' from dual
union all
select '2','3','3' from dual
union all
select '3','4','2' from dual
union all
select '3','4','5' from dual
union all
select '4','5','4' from dual
union all
select '4','5','3' from dual
)select id --这些是要删的,懒得insert 就这么写一下
from t
where id not in (
select distinct id --注:这是不删的那些id
from t where not(nettype not in(2,3) and status = 4)
and not (nettype in(2,3) and status in(2,4,5))
)结果跟楼主的相同