以下是笔者在看cookbook时做的实验笔记,由于是PDF版,光看还是不行滴!(持续更新)
-------------------------------------------------------
第六章 使用字符串
--6.7 提取姓名的大写首字母缩写
SELECT replace(
translate( REPLACE('Dylan Xu', ' ','.'),
'abcdefghijklmnoopqrstuvwxyz',
rpad('#',26,'#')), '#') || '.'
FROM dual;
--6.8 按照字符串中部分内容排序
SELECT ename FROM emp ORDER BY substr(ename, -2, 2);
--6.9 按字符串中数字排序
CREATE OR REPLACE VIEW v_sqlbook1 AS
SELECT e.ename ||' '||
CAST(e.empno AS CHAR(4))||' '||
d.dname AS DATA
FROM emp e, dept d
WHERE e.deptno = d.deptno;
SELECT data
FROM v_sqlbook1
ORDER BY to_number(REPLACE(
translate(DATA, REPLACE(
translate(DATA,
'0123456789', '##########'), '#'), rpad('#', 20, '#')), '#'))
;
SELECT to_number(trim(REPLACE( translate('abc 123 bnh', 'abcdefghijklmnopqrstuvwxyz', '##########'), '#'))) FROM dual;
--6.10 根据表中一行创建分隔列表
SELECT deptno,
ltrim(sys_connect_by_path(ename, ','), ',') emps
FROM ( select deptno,
ename,
row_number() over (partition by deptno order by empno) rn,
count(*) over(partition by deptno) cnt
from emp
)
WHERE LEVEL = cnt
START WITH rn = 1
CONNECT BY PRIOR deptno = deptno AND PRIOR rn = rn -1;
--6.11 将分隔数据转换为多值IN列表
--6.15 分析IP地址
SELECT instr('a,bc,def,g', ',', 1,1) FROM dual;
SELECT ip,
substr(ip, 1, instr(ip, '.') - 1) a,
substr(ip, instr(ip, '.') +1, instr(ip, '.', 1, 2) - instr(ip, '.')-1 ) b,
substr(ip, instr(ip, '.', 1, 2) +1, instr(ip, '.', 1, 3) - instr(ip, '.', 1, 2)-1 ) c,
substr(ip, instr(ip, '.',1,3) + 1) d
FROM (SELECT '192.168.1.240' AS ip FROM dual);
第七章 使用数字
--7.6 生成累计和
SELECT ename, sal,
SUM(sal) over (ORDER BY sal, empno) AS running_total
FROM emp
ORDER BY 2;
--7.7 生成累乘积
--c1 sal大于0情况,使用对数和求得
SELECT empno, ename, sal,
exp(SUM(ln(sal)) over(ORDER BY sal, empno)) AS running_product
FROM emp
WHERE deptno = 10;
注:指数与对数运算法则:①ln(x)=log(e)X ②ln(x) + ln(y) = ln(x*y), e^ln(x) = x;
--c2 sal存在负数与0情况,使用model求得
SELECT empno, ename, sal, tmp AS running_product
FROM (
SELECT empno, ename, -sal AS sal
FROM emp
WHERE deptno =20
)
MODEL
DIMENSION BY(row_number() over (ORDER BY sal DESC) rn )
MEASURES(sal, 0 tmp, empno, ename)
RULES (
tmp[ANY] = CASE WHEN sal[cv() - 1] IS NULL THEN sal[cv()]
ELSE tmp[cv()-1]*sal[cv()]
END
)
----------------------------------------------
present by dylan.