A B --- --------- 1 abc 1 abcd 123 abcd //不要显示 2 as 2 asd 只有一条的怎么办,就不显示
SQL> select * from e;AAA BBB -------------------- --------- 101 1 102 2 103 3 103 43 102 5 1 1 1 1 1 1已选择8行。 SQL> select * from e,(select * from(select count(a.aaa) num,a.aaa from e a,e b where a.aaa=b.aaa an d a.bbb=b.bbb 2 group by a.aaa)where num<3) e1 where e.aaa(+)=e1.aaa;AAA BBB NUM AAA -------------------- --------- --------- -------------------- 101 1 1 101 102 2 2 102 102 5 2 102 103 3 2 103 103 43 2 103SQL>
标准答案: select a,b from (select a,b,count(*) from test group by a,b having count(*) <3);
你的“一一对应”是这样解释的吗? 假设a和b是满足如下函数关系: b = f(a),有a可唯一确定b。如果同一个a可得到不同的b,则定义为不是一一对应的。如果是这样的话:SELECT DISTINCT * FROM test WHERE a IN (SELECT a FROM test GROUP BY a HAVING MIN(b) <> MAX(b))
123 abcd //不要显示因为什么这个不显示?
select distinct a, b from test where a in ( select a from (select a, b from test group by a, b) group by a having count(*) > 1 ) / =========================== oldwain --------------------------- http://www.itpub.net/index.php?referrerid=32 http://www.linuxforum.net/ http://www.oraclefan.net/
--- ---------
1 abc
1 abcd
123 abcd //不要显示
2 as
2 asd
只有一条的怎么办,就不显示
-------------------- ---------
101 1
102 2
103 3
103 43
102 5
1 1
1 1
1 1已选择8行。
SQL> select * from e,(select * from(select count(a.aaa) num,a.aaa from e a,e b where a.aaa=b.aaa an
d a.bbb=b.bbb
2 group by a.aaa)where num<3) e1 where e.aaa(+)=e1.aaa;AAA BBB NUM AAA
-------------------- --------- --------- --------------------
101 1 1 101
102 2 2 102
102 5 2 102
103 3 2 103
103 43 2 103SQL>
select a,b from
(select a,b,count(*) from test
group by a,b
having count(*) <3);
假设a和b是满足如下函数关系:
b = f(a),有a可唯一确定b。如果同一个a可得到不同的b,则定义为不是一一对应的。如果是这样的话:SELECT DISTINCT *
FROM test
WHERE a IN (SELECT a FROM test GROUP BY a HAVING MIN(b) <> MAX(b))
where a in (
select a from
(select a, b from test group by a, b)
group by a
having count(*) > 1
)
/
===========================
oldwain
---------------------------
http://www.itpub.net/index.php?referrerid=32
http://www.linuxforum.net/
http://www.oraclefan.net/