Code changes life

摘要： Select Top在不同数据库中的使用用法：1. Oracle数据库<ccid_nobr><ccid_code>SELECT * FROM TABLE1 WHERE ROWNUM<=N2. Infomix数据库<ccid_nobr><ccid_code>SELECT FIRST N * FROM TABLE13. DB2数据库<ccid_nobr><ccid_code>SELECT * ROW_NUMBER() OVER(ORDER BY COL1 DESC) AS ROWNUM WHERE ROWNUM<= 阅读全文

摘要： (一)1、查询表中重复数据。select * from peoplewhere peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录delete from peoplewhere peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)and ro 阅读全文

摘要： in 和 exists区别in 是把外表和内表作hash join，而exists是对外表作loop，每次loop再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：例如：表A（小表），表B（大表）1：select * from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引；select * from A where exists(select cc from B where c 阅读全文