id number content
001 666 ........
002 666 .........
003 001 ..........
004 002 ......
005 ......
006 004 ......
007 004 ......
表结构如上,id是pk,不会重复
number vchar2
现在想抽出number唯一存在,又不为null的数据,sql应该怎么写呢就是只抽出id为003,004的两条记录
001 666 ........
002 666 .........
003 001 ..........
004 002 ......
005 ......
006 004 ......
007 004 ......
表结构如上,id是pk,不会重复
number vchar2
现在想抽出number唯一存在,又不为null的数据,sql应该怎么写呢就是只抽出id为003,004的两条记录
应该还可以少一个嵌套吧select * from tableabc where number in (select number from tableabc group by number having count(*)=1)
from (
select id, number, contentid, count(1) over (partition by number) cnt
from t
)
where cnt = 1
where number in
(select number
from tableabc
where number is not null
group by number
having count(*)=1)
from (
select id, number, contentid, count(1) over (partition by number) cnt
from t where number is not null
)
where cnt = 1
select * from tableabc t
where number is not null and not exists (select 1
from tableabc
where number=t.number and id<>t.id
)
from (
select id, number, contentid, count(1) over (partition by number) cnt
from t where number is not null
)
where cnt = 1