sql语句常见总结

主要是一些常用的，但是容易出错的


1.数据表别名的嵌套查询（如果不写数据表的别名s，会报错）
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来删除