sql语句常见总结

[b]主要是一些常用的，但是容易出错的[/b]


1.数据表别名的嵌套查询（[color=red]如果不写数据表的别名s，会报错[/color]）
select * from (select * from numproducer)s;
或select * from (select * from numproducer) as s;

------------------待续-----------------

2.通用：删除数据库中重复的数据

insert into a values( '1111 ', '1111 ');
insert into a values( '1112 ', '1111 ');
insert into a values( '1113 ', '1111 ');
insert into a values( '1114 ', '1111 ');
insert into a values( '1113 ', '1111 ');
insert into a values( '1114 ', '1111 ');
insert into a values( '1114 ', '1111 ');
insert into a values( '1112 ', '1112 ');
insert into a values( '1114 ', '1112 ');

delete from a where exists(select a2.bm,a2.mc from a a2 where a.bm=a2.bm and a.mc=a2.mc group by a2.bm,a2.mc having count(*)>1);
或
delete from a where (select count(*) from a a2 where a.bm=a2.bm and a.mc=a2.mc )>1


找到重复的数据
select distinct * from a where exists ( select a.bm,a.mc from a b where a.bm=b.bm and a.mc=b.mc group by b.bm,b.mc having Count(*)>1 );
或简单一点：select bm,mc from a group by bm,mc having count(*)>1
或
select distinct * from a where (select count(*) from a a2 where a.bm=a2.bm and a.mc=a2.mc )>1


删除相同的保留一条
drop table #tab;
select distinct * into #tab from a;
truncate table a;
insert into a select * from #tab;

其他oracle可以通过rowid来删除