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
好多示例都找不到了,可惜