看着简单,就是做不来,选出不是一一对应的,高分送高手

[yanleigis]

create table test (a varchar2(3), b varchar2(10));
insert into test values ('1','abc');
insert into test values ('1','abc');
insert into test values ('1','abcd');
insert into test values ('2','as');
insert into test values ('2','asd');
insert into test values ('123','abcd');
insert into test values ('123','abcd');
insert into test values ('123','abcd');
insert into test values ('123','abcd');
commit;
结果应为:
1 abc
1 abcd
2 as
2 asd

[penitent]

不明白
select distinct a,b from table

[bzszp]

select distinct a,b from test;

[bzszp]

是不是去掉多条完全相同的啊？
如果是这样
只有一条的怎么办，也不显示？

[yanleigis]

A B
--- ---------
1 abc
1 abcd
123 abcd //不要显示
2 as
2 asd
只有一条的怎么办，就不显示

[cyberflying]

还是不明白，你的“一一对应”是什么意思？

[bzszp]

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 103

SQL>

[liqiangliu]

标准答案：
select a,b from
(select a,b,count(*) from test
group by a,b
having count(*) <3);

[cyberflying]

你的“一一对应”是这样解释的吗？
假设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 //不要显示

因为什么这个不显示?

[oldwain]

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/