项目中常见的SQL处理总结【一】

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

项目中常见的SQL处理总结【一】

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

1.如何查找、删除表中重复的记录

/**

方法原理：
1、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，

　 rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。

2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中

　 那些具有最大rowid的就可以了，其余全部删除。**/

DELETE FROM TEST A WHERE A.ROWID <> (SELECT MIN(B.ROWID) FROM TEST B WHERE A.ID = B.ID)

2.行列转换

例如：也就是说按【部门/性别】两个字段count人数，得到二维表

WITH A AS (SELECT '004' 部门, '2' 性别    FROM DUAL  UNION ALL  SELECT '002' 部门, '2' 性别    FROM DUAL  UNION ALL  SELECT '002' 部门, '2' 性别    FROM DUAL  UNION ALL  SELECT '003' 部门, '1' 性别    FROM DUAL  UNION ALL  SELECT '002' 部门, '1' 性别 FROM DUAL)SELECT 部门, COUNT(DECODE(性别, '1', 1)), COUNT(DECODE(性别, '2', 1))  FROM A GROUP BY 部门 ORDER BY 部门;WITH A as (select 1 AS 教师号, 2 AS 星期号, '有' AS 是否有课    FROM DUAL  UNION ALL  select 1 AS 教师号, 3 AS 星期号, '有' AS 是否有课    FROM DUAL  UNION ALL  select 2 AS 教师号, 1 AS 星期号, '有' AS 是否有课    FROM DUAL  UNION ALL  select 3 AS 教师号, 2 AS 星期号, '有' AS 是否有课    FROM DUAL  UNION ALL  select 1 AS 教师号, 2 AS 星期号, '有' AS 是否有课 FROM DUAL)select 教师号,       count(decode(星期号, 1, 1)) as 星期一 ,       count(decode(星期号, 2, 1))  AS 星期二,       count(decode(星期号, 3, 1))  as 星期三  from a group by 教师号;

3.按类别分类，取出序号最大/最小的一条语句

--从每个类别中取出序号最大的那一行WITH A AS (SELECT 1 序号, 'Z001' 类别, 'M' T    FROM DUAL  UNION ALL  SELECT 3 序号, 'Z001' 类别, 'T' T    FROM DUAL  UNION ALL  SELECT 4 序号, 'Z002' 类别, 'T' T    FROM DUAL  UNION ALL  SELECT 6 序号, 'Z003' 类别, '1' T    FROM DUAL  UNION ALL  SELECT 8 序号, 'Z003' 类别, '8' T FROM DUAL)SELECT 序号, 类别, T  FROM A A1 WHERE NOT EXISTS (SELECT 1          FROM A A2         WHERE A2.类别 = A1.类别           AND A2.序号 > A1.序号) ORDER BY 序号;--从每个类别中取出序号最大的那一行WITH A AS (SELECT 1 序号, 'Z001' 类别, 'M' T    FROM DUAL  UNION ALL  SELECT 3 序号, 'Z001' 类别, 'T' T    FROM DUAL  UNION ALL  SELECT 4 序号, 'Z002' 类别, 'T' T    FROM DUAL  UNION ALL  SELECT 6 序号, 'Z003' 类别, '1' T    FROM DUAL  UNION ALL  SELECT 8 序号, 'Z003' 类别, '8' T FROM DUAL)SELECT 序号, 类别, T  FROM A A1 WHERE 2 > (SELECT COUNT(1)              FROM A A2             WHERE A2.类别 = A1.类别               AND A2.序号 > A1.序号) ORDER BY 序号;

4.Oracle 分页（查询第6条到10条的数据信息）

采用数据库SQL分页需要两次SQL完成一个SQL计算总数量一个SQL返回分页后的数据优点：性能SQL返回分页后的数据好缺点：编码复杂，各种数据库语法不同，需要两次SQL交互。oracle数据库一般采用rownum来进行分页，常用分页语法有如下两种：1、直接通过rownum分页。2、采用rowid分页语法。--1、直接通过rownum分页。SELECT *  FROM (SELECT A.*, ROWNUM ROWNUM1          FROM (SELECT * FROM EMP P ORDER BY EMPNO) A         WHERE ROWNUM <= 10) WHERE ROWNUM1 > 5;SELECT T.*  FROM (SELECT A.*, ROWNUM RM FROM (SELECT * FROM EMP ORDER BY SAL) A) T WHERE T.RM BETWEEN 11 AND 15;-- 数据访问开销 = 索引IO+索引全部记录结果对应的表数据IO/*2、采用rowid分页语法。优化原理是通过纯索引找出分页记录的ROWID，再通过ROWID回表返回数据，要求内层查询和排序字段全在索引里。*/create index myindex on EMP(EMPNO, DEPTNO);SELECT B.*  FROM (SELECT *          FROM (SELECT A.*, ROWNUM ROWNUM1                  FROM (SELECT ROWID RID, EMPNO FROM EMP A) A                 WHERE ROWNUM <= 10)         WHERE ROWNUM1 > 5) A,       EMP B WHERE A.RID = B.ROWID;-- 数据访问开销 = 索引IO + 索引分页结果对应的表数据IO

5. 不足补充0

SELECT lpad(ROWNUM,2,'0') FROM dual CONNECT BY ROWNUM <=11;SELECT rpad(ROWNUM,2,'0') FROM dual CONNECT BY ROWNUM <=20;

6.把一个实例中的表复制到另一个实例中

-- 语法COPY {FROM DATABASE | TO DATABASE|FROM DATABASE TO DATABASE}{APPEND|CREATE|INSERT|REPLACE}DESTINATION_TABLE [(COLUMN, COLUMN, COLUMN, ...)]USING QUERY-- 举例COPY FROM EMS/EMS@SDMISTO TEST01/TEST01@TESTDBAPPEND C_FORMUSING SELECT * FROM C_FORM;