Sql cookbook学习笔记

以下是笔者在看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.