select t.* from t, (select aid,rowid rid from t where aid in(select aid from T group by aid having count(aid)>1) and bname='未分组') b where b.rid<>t.rowid;
select * from ( select aid, anme, bid, bname ,count(1) over(partition by aid) cnt from t ) t where cnt = 1 OR bname <> '未分组'
select a.* from viewname a where not exists ( select * from viewname b where b.bid<>'未分组' and a.aid=b.aid and a.aname=b.bname ) union all select * from viewname where bid<>'未分组'
-- 这样写 SQL> select * 2 from test 3 where rowid not in (select rid 4 from (select test.rowid rid, 5 test.*, 6 row_number() over(partition by aid order by aid) rn 7 from test) 8 where rn <> 1 9 and bname = '未分组') 10 order by aid 11 /AID ANAME BID BNAME --- ----- ------ ------ 1 a 1 b1 2 a2 2 b2 3 a3 2 b2 3 a3 1 b1 4 a4 未分组 未分组 5 a5 3 b36 rows selectedSQL>
否则都不知道你要做什么
1 1 a 1 b1
2 1 a 未分组 未分组
3 2 a2 2 b2
4 2 a2 未分组 未分组
5 3 a3 1 b1
6 3 a3 2 b2
7 3 a3 未分组 未分组
8 4 a4 未分组 未分组
9 5 a5 3 b3
10 5 a5 未分组 未分组这是个视图的数据..如何才能显示成这样
aid aname bid bname
1 1 a 1 b1
2 2 a2 2 b2
3 3 a3 1 b1
4 3 a3 2 b2
5 4 a4 未分组 未分组
6 5 a5 3 b3
1 1 a 1 b1
2 1 a 未分组 未分组
3 2 a2 2 b2
4 2 a2 未分组 未分组
5 3 a3 1 b1
6 3 a3 2 b2
7 3 a3 未分组 未分组
8 4 a4 未分组 未分组
9 5 a5 3 b3
10 5 a5 未分组 未分组
结果
aid aname bid bname
1 1 a 1 b1
2 2 a2 2 b2
3 3 a3 1 b1
4 3 a3 2 b2
5 4 a4 未分组 未分组
6 5 a5 3 b3
小弟水平有限。。搞了一天没搞出来。。
(select aid,rowid rid from t
where aid in(select aid from T
group by aid
having count(aid)>1)
and bname='未分组') b
where b.rid<>t.rowid;
1 1 A1 1 B1
2 1 A1 未分组 未分组
3 2 A2 2 B2
4 2 A2 未分组 未分组
5 3 A3 1 B1
6 3 A3 2 B2
7 3 A3 未分组 未分组
8 4 A4 未分组 未分组
9 5 A5 3 B3
10 6 A5 未分组 未分组
from (
select aid, anme, bid, bname
,count(1) over(partition by aid) cnt
from t
) t
where cnt = 1
OR bname <> '未分组'
from viewname a
where not exists (
select * from viewname b
where b.bid<>'未分组'
and a.aid=b.aid and a.aname=b.bname
)
union all
select *
from viewname
where bid<>'未分组'
SQL> select *
2 from test
3 where rowid not in (select rid
4 from (select test.rowid rid,
5 test.*,
6 row_number() over(partition by aid order by aid) rn
7 from test)
8 where rn <> 1
9 and bname = '未分组')
10 order by aid
11 /AID ANAME BID BNAME
--- ----- ------ ------
1 a 1 b1
2 a2 2 b2
3 a3 2 b2
3 a3 1 b1
4 a4 未分组 未分组
5 a5 3 b36 rows selectedSQL>