平时积累的一些SQL语句(转) 1

fxz_2008

/* Formatted on 2011-1-12 10:48:19 (QP5 v5.149.1003.31008) */---1.各个部门工资排名前几名的员工信息------------a.SELECT *  FROM emp a WHERE (SELECT COUNT (*)          FROM emp         WHERE deptno = a.deptno AND sal > a.sal) <= 2         --改变该值即可以得到相应名次       AND a.deptno IS NOT NULL;/*b.c.d区别注意：    1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果             2.rank()和dense_rank()的区别是：      --rank()是跳跃排序，有两个第二名时接下来就是第四名      --dense_rank()l是连续排序，有两个第二名时仍然跟着第三名*/--b.SELECT *  FROM (SELECT deptno,               ename,               sal,               ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) rn          FROM emp) WHERE rn <= 3;--c.SELECT *  FROM (SELECT deptno,               ename,               sal,               RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) rn          FROM emp) WHERE rn <= 3;--d.SELECT *  FROM (SELECT deptno,               ename,               sal,               DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) rn          FROM emp) WHERE rn <= 3;--e  SELECT a.deptno,         a.empno,         a.ename,         a.sal,         -- 可跳跃的rank         RANK () OVER (PARTITION BY a.deptno ORDER BY a.sal DESC) r1,         -- 密集型rank         DENSE_RANK () OVER (PARTITION BY a.deptno ORDER BY a.sal DESC) r2,         -- 不分组排序         RANK () OVER (ORDER BY sal DESC) r3    FROM scott.emp aORDER BY a.deptno, a.sal DESC;---2.获取所有员工中工资第几高的员工信息SELECT *  FROM (SELECT t.*, DENSE_RANK () OVER (ORDER BY t.sal DESC) RANK          FROM emp t) WHERE RANK = 3;--3.获取员工表中员工信息以及员工所在部门的最高工资和平均工资信息--------SELECT e.*, sa.maxsal, sa.avgsal  FROM emp e,       (  SELECT MAX (sal) maxsal, AVG (sal) avgsal, deptno            FROM emp ine        GROUP BY deptno) sa WHERE e.deptno = sa.deptno OR (e.deptno IS NULL AND sa.deptno IS NULL);--4.求员工工资所占部门总工资的比率  SELECT ename,         deptno,         sal,         TRUNC (sal * 100 / SUM (sal) OVER (PARTITION BY deptno), 2) percent    FROM empORDER BY deptno;--5.获取所有员工中工资最高的几位--a.SELECT *  FROM (  SELECT *            FROM emp        ORDER BY sal DESC) WHERE ROWNUM <= 2;--b.SELECT *  FROM (SELECT ename,               deptno,               sal,               ROW_NUMBER () OVER (ORDER BY sal DESC) ee          FROM emp) WHERE ee <= 4 AND ee >= 2;--6.获取各个部门工资排名在2到3 位的员工信息--a.SELECT *  FROM (SELECT deptno,               ename,               sal,               ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) rn          FROM emp) WHERE rn <= 3 AND rn >= 2;--b.SELECT *  FROM (SELECT deptno,               ename,               sal,               RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) rn          FROM emp) WHERE rn <= 3 AND rn >= 2;--c.SELECT *  FROM (SELECT deptno,               ename,               sal,               DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) rn          FROM emp) WHERE rn <= 3 AND rn >= 2;--7查出各个部门中工资大于平均工资的员工信息--a(good)SELECT *  FROM emp e WHERE e.sal > (  SELECT AVG (sal)                    FROM emp ine                GROUP BY deptno                  HAVING e.deptno = ine.deptno);--b(not good)  SELECT e.*, TRUNC (d.sal) avgsal    FROM emp e,         (  SELECT AVG (sal) sal, deptno              FROM emp          GROUP BY deptno) d   WHERE e.deptno = d.deptno AND e.sal > d.salORDER BY e.deptno;--8根据排序来取得后一个值和当前值相加的结果SELECT *  FROM (SELECT ename,               deptno,               sal,               SUM (sal)               OVER (ORDER BY sal ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)                  ee          FROM emp);---9.分页处理的相关语句----------a最正确SELECT *  FROM (SELECT ROWNUM AS num, ine.*          FROM (  SELECT *                    FROM emp                ORDER BY empno) ine         WHERE ROWNUM <= 7) WHERE num >= 3;--bSELECT *  FROM (  SELECT ROWNUM AS num, emp.*            FROM emp           WHERE ROWNUM <= 7        ORDER BY empno) WHERE num >= 3;--cSELECT *  FROM (  SELECT ROWNUM AS num, emp.*            FROM emp        ORDER BY empno) WHERE num >= 3 AND num <= 7;--11.根据工资高低来判断该员工的等级SELECT e.ename,       e.sal,       DECODE (SUBSTR (TO_CHAR (TRUNC (sal, -3)), 1, 1),               0, 'sorry',               1, '1ok',               2, '2ok',               3, '3ok',               4, '4ok',               5, '5ok',               6, '6ok',               7, 'higth',               'to enough')  FROM emp e;/*--12.  sal<1000  显示低工资   sal-1000<0   sign(sal-1000) = -1   1000<=sal<=3000  正常工资   3000<sal<=5000  高工资*/SELECT sal,       DECODE (          SIGN (sal - 1000),          -1, '低工资',          DECODE (             SIGN (sal - 3000),             -1, '正常工资',             0, '正常工资',             1, DECODE (SIGN (sal - 5000), -1, '高工资', '高工资')))          AS 工资状态  FROM emp;SELECT CASE WHEN sal >= 5000 THEN '高工资' END sal FROM emp;--13.一年以后的今天SELECT ADD_MONTHS (SYSDATE, 12) FROM DUAL;--14.一年以前的今天SELECT ADD_MONTHS (SYSDATE, -12) FROM DUAL;--15.本月第3天的日期SELECT ADD_MONTHS (LAST_DAY (SYSDATE) + 3, -1) FROM DUAL;--20.求最大值SELECT GREATEST (100,                 90,                 80,                 101,                 01,                 19)  FROM DUAL;--21.求最小值SELECT LEAST (100,              0,              -9,              10)  FROM DUAL;--30计算工资在2000以上的各种工作的平均工资SELECT job, AVG (sal)  FROM emp WHERE sal > 2000-- set amount maskset_amount_mask;END pre_form; PROCEDURE set_amount_maskISBEGIN   -------------------------------------   -- setup amount field's format mask   -- ----------------------------------    SET_ITEM_PROPERTY (      'HEADERS.TOTAL_AMOUNT',      FORMAT_MASK,      fnd_currency.       get_format_mask (         :parameter.currency_code,         GET_ITEM_PROPERTY ('HEADERS.TOTAL_AMOUNT', MAX_LENGTH)));    SET_ITEM_PROPERTY (      'LINES.LINE_AMOUNT',      FORMAT_MASK,      fnd_currency.       get_format_mask (:parameter.currency_code,                        GET_ITEM_PROPERTY ('LINES.LINE_AMOUNT', MAX_LENGTH)));END set_amount_mask; 动态提交请求APPS.FND_REQUEST.SUBMIT_REQUEST(APPLICATION IN VARCHAR2 DEFAULT NULL,PROGRAM IN VARCHAR2 DEFAULT NULL,DESCRIPTION IN VARCHAR2 DEFAULT NULL,START_TIME IN VARCHAR2 DEFAULT NULL,SUB_REQUEST IN BOOLEAN DEFAULT FALSE,chr(0),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','', '','','','','','','','','','','','','','','','','','','','', '','','','','','','','','','','','','','','','','','','','', '','','','','','','','','','','','','','','','','','','','', '','','','','','','','','','')RETURN NUMBER; 状态判断get_block_property('headers',status):SYSTEM.ModeGET_VIEW_PROPERTY(GET_ITEM_PROPERTY(:SYSTEM.CURSOR_ITEM, ITEM_CANVAS),WINDOW_NAMEGet_Block_Property( 'LINES_PROMPT', PREVIOUSBLOCK)GET_RECORD_PROPERTY(:SYSTEM.CURSOR_RECORD,'SHOPPEDAYOVERTB_V',Status );--:SYSTEM.RECORD_STATUS ;键弹性域定义和更新定义fnd_key_flex.define(BLOCK=>'Items',FIELD=>'EXPENSE_ACCID_DSP',APPL_SHORT_NAME=>'SQLGL',CODE=>'GL#',ID=>'EXPENSE_CCID',REQUIRED=>'Y',USEDBFLDS=>'N',updateable=>'',VALIDATE=> 'FULL',VRULE=> '\\nSUMMARY_FLAG\\nI\\nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\\nN',NUM=>'ARAMETER.CHART_OF_ACCOUNTS_ID');Form中执行SQL语句sql1:=' TRUNCATE TABLE cfnd_matrix_cells';FORMS_DDL(sql1);  SELECT a.empno,         a.ename,         a.sal,         -- 上面一行         LAG (a.sal) OVER (ORDER BY a.sal DESC) lag_1,         -- 下面三行         LEAD (a.sal, 1) OVER (ORDER BY a.sal DESC) lead_1    FROM scott.emp aORDER BY a.sal DESC;-- 用exists替代distinct的例子SELECT DISTINCT d.deptno, dname  FROM dept d, emp e WHERE d.deptno = e.deptno;SELECT deptno, dname  FROM dept d WHERE EXISTS          (SELECT 'x'             FROM emp e            WHERE d.deptno = e.deptno);-------=============行列转换部分处理================------------101.行列转换 行转列DROP TABLE t_change_lc;CREATE TABLE t_change_lc(   card_code   VARCHAR2 (3),   q           NUMBER,   bal         NUMBER);INSERT INTO t_change_lc       SELECT '001' card_code, ROWNUM q, TRUNC (DBMS_RANDOM.VALUE * 100) bal         FROM DUAL   CONNECT BY ROWNUM <= 4   UNION       SELECT '002' card_code, ROWNUM q, TRUNC (DBMS_RANDOM.VALUE * 100) bal         FROM DUAL   CONNECT BY ROWNUM <= 4;SELECT * FROM t_change_lc;  SELECT a.card_code,         SUM (DECODE (a.q, 1, a.bal, 0)) q1,         SUM (DECODE (a.q, 2, a.bal, 0)) q2,         SUM (DECODE (a.q, 3, a.bal, 0)) q3,         SUM (DECODE (a.q, 4, a.bal, 0)) q4    FROM t_change_lc aGROUP BY a.card_codeORDER BY 1;--102.行列转换 列转行DROP TABLE t_change_cl;CREATE TABLE t_change_clAS     SELECT a.card_code,            SUM (DECODE (a.q, 1, a.bal, 0)) q1,            SUM (DECODE (a.q, 2, a.bal, 0)) q2,            SUM (DECODE (a.q, 3, a.bal, 0)) q3,            SUM (DECODE (a.q, 4, a.bal, 0)) q4       FROM t_change_lc a   GROUP BY a.card_code   ORDER BY 1;SELECT * FROM t_change_cl;  SELECT t.card_code,         t.rn q,         DECODE (t.rn,  1, t.q1,  2, t.q2,  3, t.q3,  4, t.q4) bal    FROM (SELECT a.*, b.rn            FROM t_change_cl a,                 (    SELECT ROWNUM rn                        FROM DUAL                  CONNECT BY ROWNUM <= 4) b) tORDER BY 1, 2;--103.行列转换 行转列 合并DROP TABLE t_change_lc_comma;CREATE TABLE t_change_lc_commaAS   SELECT card_code, 'quarter_' || q AS q FROM t_change_lc;SELECT * FROM t_change_lc_comma;    SELECT t1.card_code, SUBSTR (MAX (SYS_CONNECT_BY_PATH (t1.q, ';')), 2) q      FROM (SELECT a.card_code,                   a.q,                   ROW_NUMBER () OVER (PARTITION BY a.card_code ORDER BY a.q) rn              FROM t_change_lc_comma a) t1START WITH t1.rn = 1CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn - 1 = PRIOR t1.rn  GROUP BY t1.card_code;--104.行列转换 列转行 分割DROP TABLE t_change_cl_comma;CREATE TABLE t_change_cl_commaAS       SELECT t1.card_code, SUBSTR (MAX (SYS_CONNECT_BY_PATH (t1.q, ';')), 2) q         FROM (SELECT a.card_code,                      a.q,                      ROW_NUMBER () OVER (PARTITION BY a.card_code ORDER BY a.q)                         rn                 FROM t_change_lc_comma a) t1   START WITH t1.rn = 1   CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn - 1 = PRIOR t1.rn     GROUP BY t1.card_code;SELECT * FROM t_change_cl_comma;  SELECT t.card_code,         SUBSTR (t.q, INSTR (';' || t.q,                             ';',                             1,                             rn), INSTR (t.q || ';',                                         ';',                                         1,                                         rn)                                  - INSTR (';' || t.q,                                           ';',                                           1,                                           rn))            q    FROM (SELECT a.card_code, a.q, b.rn            FROM t_change_cl_comma a,                 (    SELECT ROWNUM rn                        FROM DUAL                  CONNECT BY ROWNUM <= 100) b           WHERE INSTR (';' || a.q,                        ';',                        1,                        rn) > 0) tORDER BY 1, 2;-------------==================end 行列转换====================----------------105如果存在就更新，不存在就插入用一个语句实现DROP TABLE t_mg;CREATE TABLE t_mg(   code   VARCHAR2 (10),   NAME   VARCHAR2 (10));SELECT * FROM t_mg;MERGE INTO t_mg a     USING (SELECT 'the code' code, 'the name' NAME FROM DUAL) b        ON (a.code = b.code)WHEN MATCHEDTHEN   UPDATE SET a.NAME = b.NAMEWHEN NOT MATCHEDTHEN   INSERT     (code, NAME)       VALUES (b.code, b.NAME);---106-----========集合交并处理======================DROP TABLE t1;DROP TABLE t2;CREATE TABLE t1(   id     NUMBER,   NAME   VARCHAR2 (20));CREATE TABLE t2(   id     NUMBER,   NAME   VARCHAR2 (20));INSERT INTO t1     VALUES (1, 'a');INSERT INTO t1     VALUES (2, 'b');INSERT INTO t1     VALUES (3, 'c');INSERT INTO t1     VALUES (4, 'd');INSERT INTO t1     VALUES (5, '');SELECT * FROM t1;SELECT *  FROM t1 WHERE NAME IS NULL;INSERT INTO t2     VALUES (1, 'a');INSERT INTO t2     VALUES (2, 'b');INSERT INTO t2     VALUES (3, 'c');INSERT INTO t2     VALUES (12, 'a');INSERT INTO t2     VALUES (22, '');INSERT INTO t2     VALUES (32, '');SELECT * FROM t2;SELECT * FROM t1;SELECT * FROM t2;SELECT * FROM t1UNION                                                      --两表取并集，无重复值，且对结果排序SELECT * FROM t2;SELECT * FROM t1UNION ALL                                                  --两表取并集，有重复值，且结果不排序SELECT * FROM t2;SELECT * FROM t1MINUS                                                            --ti-t2 两表去差集SELECT * FROM t2;SELECT *  FROM t1INTERSECT --两表取交集DELETEFROM fnd_lobsWHERE file_id = l_gfm_id;FORMS_DDL('commit'); FORMS_DDL('commit');END IF;END IF;END IF;END;---未完转下