ORACLE GROUP BY 如何去重 小汇

 

2010年12月17日14:30:02

例如有如下表结构和值
table
fid name sex
1    a       男   
2    b       男
3    c        女
4    d        女
5    a       男
6    b       男
方案一：distinct
select distinct name from table
得到结果：
name
a
b
c
d
实现效果，那如果要同时打开其它记录呢？再试试
select distinct name,id from table
测试没什么效果，查下得知，这样实际是要name和id字段都重复才被筛选。继续查找可得如下方法：
方案二：group by
select *, count(distinct name) from table group by name
Oracle下测试失败，据说MYSQL下通过，不管，继续思考....
翻翻书，试试
select min(fid),name,sex from table group by name (此处从别处网摘，个人认为该是这样：GROUP BY name,sex)
成功，现实如下结果：
fid name sex
1     a        男
2     b        男
3     c         女
4     d        女
继续思考，如果要打开所有记录，不指定字段用（*），貌似这方法就不行了！
select * from table where fid in(Select min(fid) FROM table group by name)
测试成功
fid name sex
1      a         男
2      b         男
3      c          女
4      d          女
方案三：
所有某字段不重复的记录
select * from table where name in(select name from table group by name having count(name)=1)
得到如下结果：
fid name sex
1       a       男
2       b       男
5       a       男
6       b       男
再多的字段都全部现实。哎，原来如此简单！回顾网上方法distinct，Inner Join等等，麻烦，而且有很大局限性.
总结如下：
select distinct name from table查询不重复记录的单个字段
select * from table where fid in(Select min(fid) FROM table group by name)查询不重复记录的所有字段值
select * from table where name in(select name from table group by name having count(name)=1)查询不重复（可设定重复数为任意次数）的所有记录
************************************************************************************************

方案四：是我自己用的，大数据量的时候 in 的执行效果，让人望而却步,遂继续找。
用distinct关键字只能过滤查询字段中所有记录相同的（记录集相同），而如果要指定一个字段却没有效果，distinct使用的是二重循环来去重的，如果数据量非常大的时候，会导致性能急剧下降。
效率很低 。

select distinct name from tbl1 能消除重复记录，但只能取一个字段，现在要同时取id,name这2个字段的值。

select distinct id,name from tbl1 可以取多个字段，但只能消除这2个字段值全部相同的记录

所以用distinct达不到想要的效果，用group by   可以解决这个问题。

例如要显示的字段为A、B、C三个，而A字段的内容不能重复可以用下面的语句：

 

select A, min(B),min(C),count(*) from [table] where [条件] group by   A

having [条件] order by A desc

**********************************************************************************************

下面是自己的实验，创建表：
 create table s_dept( id number(2) primary key, name varchar2(30) ) / insert into s_dept values(1, 'HumanResource'); insert into s_dept values(2, 'Sales'); insert into s_dept values(3, 'Finanace'); COMMIT; create table s_emp( id number(2) primary key, name varchar2(25), dept number(2) references s_dept(id), salary number(6) ) insert into S_EMP (ID, NAME, DEPT, SALARY) values (1, 'zhangsan', 1, 2000); insert into S_EMP (ID, NAME, DEPT, SALARY) values (2, 'lisi', 2, 2000); insert into S_EMP (ID, NAME, DEPT, SALARY) values (3, 'wangwu', 1, 3000); insert into S_EMP (ID, NAME, DEPT, SALARY) values (4, 'zhaoliu', 1, 3500); insert into S_EMP (ID, NAME, DEPT, SALARY) values (5, 'lili', 2, 4500); insert into S_EMP (ID, NAME, DEPT, SALARY) values (6, 'lili', 1, 3000); insert into S_EMP (ID, NAME, DEPT, SALARY) values (7, 'zhaoliu', 1, 3500); insert into S_EMP (ID, NAME, DEPT, SALARY) values (8, 'lili', 2, 4500); COMMIT

SELECT MIN(D.ID)ID ,D.NAME,D.DEPT   FROM S_EMP D  GROUP BY D.NAME,D.DEPT ORDER BY ID ASC;

另外一种写法：

SELECT C.NAME ,C.DEPT ,MAX(ROWID) SYS_ID FROM S_EMP C GROUP BY C.NAME,C.DEPT ;