oralce常用sql积累

1.解表语句，表被锁后可按照下列语句去解释

--查询哪些对象被锁：
select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;
--杀死进程：
alter system kill session '6329,87478'; (其中<span style="font-family: Arial, Helvetica, sans-serif;">6329,87478</span>分别是上面查询出的sid,serial#)

2.查询树节点

--查上节点
SELECT * FROM org_pk_mod 
CONNECT BY PRIOR ID =  parent_id
START WITH ID=  '9134715'

--查下节点
SELECT *
  FROM <span style="font-family: Arial, Helvetica, sans-serif;">org_pk_mod</span>
CONNECT BY T.PARENT_ID = PRIOR T.ID
 START WITH T.ID = '9134715'

3.行转列

SELECT /*+ordered*/
 SUM(DECODE(A.X,
            TO_CHAR(ADD_MONTHS(TO_DATE('201311', 'yyyyMM'), -11), 'yyyyMM'),
            A.Y)) V1,
 SUM(DECODE(A.X,
            TO_CHAR(ADD_MONTHS(TO_DATE('201311', 'yyyyMM'), -10), 'yyyyMM'),
            A.Y)) V2,
 SUM(DECODE(A.X,
            TO_CHAR(ADD_MONTHS(TO_DATE('201311', 'yyyyMM'), -9), 'yyyyMM'),
            A.Y)) V3,
 SUM(DECODE(A.X,
            TO_CHAR(ADD_MONTHS(TO_DATE('201311', 'yyyyMM'), -8), 'yyyyMM'),
            A.Y)) V4,
 SUM(DECODE(A.X,
            TO_CHAR(ADD_MONTHS(TO_DATE('201311', 'yyyyMM'), -7), 'yyyyMM'),
            A.Y)) V5,
 SUM(DECODE(A.X,
            TO_CHAR(ADD_MONTHS(TO_DATE('201311', 'yyyyMM'), -6), 'yyyyMM'),
            A.Y)) V6,
 SUM(DECODE(A.X,
            TO_CHAR(ADD_MONTHS(TO_DATE('201311', 'yyyyMM'), -5), 'yyyyMM'),
            A.Y)) V7,
 SUM(DECODE(A.X,
            TO_CHAR(ADD_MONTHS(TO_DATE('201311', 'yyyyMM'), -4), 'yyyyMM'),
            A.Y)) V8,
 SUM(DECODE(A.X,
            TO_CHAR(ADD_MONTHS(TO_DATE('201311', 'yyyyMM'), -3), 'yyyyMM'),
            A.Y)) V9,
 SUM(DECODE(A.X,
            TO_CHAR(ADD_MONTHS(TO_DATE('201311', 'yyyyMM'), -2), 'yyyyMM'),
            A.Y)) V10,
 SUM(DECODE(A.X,
            TO_CHAR(ADD_MONTHS(TO_DATE('201311', 'yyyyMM'), -1), 'yyyyMM'),
            A.Y)) V11,
 SUM(DECODE(A.X, '201311', A.Y)) V12
  FROM (SELECT /*+ordered*/
         T.ACCT_MONTH X, SUM(T.COMM_FEE) Y, '佣金' S
          FROM CHNL_COMM_M T
         WHERE T.CHNL_ID IN (SELECT T.CHNL_ID
                               FROM DMCODE.DMCODE_CHNL T
                              WHERE ZONE_ID = 'Z36a1up')
           AND T.ACCT_MONTH BETWEEN
               TO_CHAR(ADD_MONTHS(TO_DATE('201311', 'yyyyMM'), -11),
                       'yyyyMM') AND '201311'
         GROUP BY T.ACCT_MONTH
         ORDER BY X) A

4.单元格合并（table列合并，去数据表里的合并数）

SELECT A.*,
       COUNT(1) OVER(PARTITION BY T1) *CASE
         WHEN ROW_NUMBER()
          OVER(PARTITION BY T1 ORDER BY T1) = 1 THEN
          1
         ELSE
          0
       END ROWSPAN1,
       COUNT(1) OVER(PARTITION BY T1, T2) *CASE
         WHEN ROW_NUMBER()
          OVER(PARTITION BY T1,
                   T2 ORDER BY T1,
                   T2) = 1 THEN
          1
         ELSE
          0
       END ROWSPAN2
  FROM (
        
        SELECT APP_CODE T1,
                APP_NAME,
                KPI_CODE T2,
                KPI_NAME,
                KPI_VALUE KPI_RANK_ASC
          FROM  DM_ANK_INFO_M
         WHERE KPI_CODE = 'KPI_0006'
           AND ACCT_MONTH = '201309'
           AND AREA_ID = '570'
         ORDER BY KPI_RANK_ASC) A

好多示例都找不到了，可惜