2019-01-13

ORACLE之SQL篇-从日期提取时间的各部分

--从日期提取时间的各部分
--注意:返回的是服务器的时间!!!可能和本地有误差
SELECT to_number(to_char(SYSDATE, 'yyyy')) AS 年,
       to_number(to_char(SYSDATE, 'mm')) AS 月,
       to_char(SYSDATE, 'MON') AS 月,
       to_number(to_char(SYSDATE, 'dd')) AS 日,
       to_number(to_char(SYSDATE, 'hh24')) AS 时,
       to_number(to_char(SYSDATE, 'mi')) AS 分,
       to_number(to_char(SYSDATE, 'ss')) AS 秒,
       to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') AS 年月日时分秒,
       to_char(SYSDATE, 'dy') AS 星期
  FROM dual;

ORACLE之SQL篇-列出一年中每个季度的开始日期和结束日期

--列出一年中每个季度的开始日期和结束日期
SELECT add_months(trunc(SYSDATE, 'y'), (rownum - 1) * 3) q_start,
       add_months(trunc(SYSDATE, 'y'), rownum * 3) - 1 q_end
  FROM dual
CONNECT BY LEVEL < = 4;

ORACLE之SQL篇-创建横向直方图

--创建横向直方图
SELECT s.deptno
  FROM scott.emp s;
--deptno
--10 出现 3 次
--20 出现 5 次
--30 出现 6 次
SELECT s.deptno, SUM(1) over(PARTITION BY s.deptno)
  FROM scott.emp s
 ORDER BY s.deptno;
SELECT s.deptno, COUNT(1) AS cnt FROM scott.emp s GROUP BY s.deptno;
--创建直方图
SELECT s.deptno, lpad('*', COUNT(1), '*') AS cnt
  FROM scott.emp s
 GROUP BY s.deptno;

ORACLE之SQL篇-创建纵向直方图

--创建纵向直方图
SELECT s.deptno,
       s.empno,
       row_number() over(PARTITION BY s.deptno ORDER BY s.empno) AS rn,
       (case when s.deptno=10 then '*' else null end) as deptno_10,
       (case when s.deptno=20 then '*' else null end) as deptno_20,
       (case when s.deptno=30 then '*' else null end) as deptno_30
  FROM scott.emp s;
--继续处理
--10出现3次
--20出现5次
--30出现6次
select max(deptno_10) as deptno_10,max(deptno_20) as deptno_20,max(deptno_30) as deptno_30 from (
SELECT row_number() over(PARTITION BY s.deptno ORDER BY s.empno) AS rn,
       (case when s.deptno=10 then '*' else null end) as deptno_10,
       (case when s.deptno=20 then '*' else null end) as deptno_20,
       (case when s.deptno=30 then '*' else null end) as deptno_30
  FROM scott.emp s) x
  group by rn
  order by deptno_10 desc,deptno_20 desc,deptno_30 desc;

ORACLE之SQL篇-加减日月年

--加减日月年
SELECT SYSDATE FROM dual;
SELECT SYSDATE + 5 AS sysdate_5d, --5天
       add_months(SYSDATE, 5) AS sysdate_5m, --五月
       add_months(SYSDATE, 5 * 12) AS sysdate_5y, --五年
       SYSDATE - 5 AS sysdate_5d_ago, --前5天
       add_months(SYSDATE, -5) AS sysdate_5m_ago, --前五月
       add_months(SYSDATE, -5 * 12) AS sysdate_5y_ago --前五年
  FROM dual;

ORACLE之SQL篇-存取未来行

--存取"未来行"
SELECT s.ename,
       s.hiredate,
       s.sal,
       --显示下一行,如果没有则会放空
       lead(s.sal) over(ORDER BY s.hiredate) AS next_sal
  FROM scott.emp s;
SELECT ename, hiredate, sal, next_sal
  FROM (SELECT s.ename,
               s.hiredate,
               s.sal,
               lead(s.sal) over(ORDER BY s.hiredate) AS next_sal
          FROM scott.emp s)
 WHERE sal < next_sal;

ORACLE之SQL篇-对某列的值求和

--对某列的值求和
SELECT * FROM scott.emp s;
SELECT SUM(s.sal) AS sum_comm FROM scott.emp s;
--计算雇员编号为20的工资
SELECT SUM(s.sal) AS sum_comm FROM scott.emp s WHERE s.deptno = 20;
--计算每种编号的工资
SELECT SUM(s.sal) AS sum_comm, s.deptno FROM scott.emp s GROUP BY s.deptno;
--所有的聚合函数都会忽略NULL值
SELECT SUM(s.comm) AS sum_comm, s.deptno
  FROM scott.emp s
 GROUP BY s.deptno;
--可以把NULL值做处理,此时一目了然,10和20部门下的员工没有奖金
SELECT SUM(nvl(s.comm, 0)) AS sum_comm, s.deptno
  FROM scott.emp s
 GROUP BY s.deptno;

ORACLE之SQL篇-把结果集转置为一行

--把结果集转置为一行
SELECT s.deptno,
       decode(s.deptno, 10, 1, 0) AS deptno_10,
       decode(s.deptno, 20, 1, 0) AS deptno_20,
       decode(s.deptno, 30, 1, 0) AS deptno_30
  FROM scott.emp s
 ORDER BY s.deptno;
SELECT s.deptno,
       SUM(decode(s.deptno, 10, 1, 0)) AS deptno_10,
       SUM(decode(s.deptno, 20, 1, 0)) AS deptno_20,
       SUM(decode(s.deptno, 30, 1, 0)) AS deptno_30
  FROM scott.emp s
 GROUP BY s.deptno
 ORDER BY s.deptno;
--去掉s.deptno
SELECT SUM(decode(s.deptno, 10, 1, 0)) AS deptno_10,
       SUM(decode(s.deptno, 20, 1, 0)) AS deptno_20,
       SUM(decode(s.deptno, 30, 1, 0)) AS deptno_30
  FROM scott.emp s
 ORDER BY s.deptno;

ORACLE之SQL篇-抑制重复

--抑制重复
SELECT job FROM scott.emp s ORDER BY s.job;
--常用方法
SELECT DISTINCT job FROM scott.emp s ORDER BY s.job;
--第二种方法
SELECT s.job, row_number() over(PARTITION BY s.job ORDER BY s.job) rn
  FROM scott.emp s; --查询每个工作出现的次数
SELECT job, rn
  FROM (SELECT s.job,
               row_number() over(PARTITION BY s.job ORDER BY s.job) rn
          FROM scott.emp s) x
 WHERE rn = 1; --指定出现次数为1的值

ORACLE之SQL篇-求列的最大值和最小值

--求列的最大值和最小值

--查询所有员工中最高和最低值工资
SELECT MIN(s.sal) AS min_comm, MAX(s.sal) AS max_comm FROM scott.emp s;
--查询每个部门中员工最高和最低的工资
SELECT s.deptno, MIN(s.sal) AS min_comm, MAX(s.sal) AS max_comm
  FROM scott.emp s
 GROUP BY s.deptno;
--所有的聚合函数会忽略NULL值
SELECT s.deptno, MIN(s.comm) AS min_comm, MAX(s.comm) AS max_comm
  FROM scott.emp s
 GROUP BY s.deptno;
--可以把NULL值做处理,此时一目了然,10和20部门下的员工没有奖金
SELECT s.deptno,
       MIN(nvl(s.comm, 0)) AS min_comm,
       MAX(nvl(s.comm, 0)) AS max_comm
  FROM scott.emp s
 GROUP BY s.deptno;

ORACLE之SQL篇-求员工表的行数

--求员工表的行数
--当一个表中存在主键.我们可以使用如下方法统计表的数据量
SELECT COUNT(*) AS count_emp FROM scott.emp; --14
--针对部门统计
SELECT s.deptno, COUNT(*) AS count_deptno
  FROM scott.emp s
 GROUP BY s.deptno; --14
--由于所有聚合函数忽略NULL值,所以针对表中某字段统计表数据量可能会不准确
SELECT COUNT(s.comm) AS count_comm FROM scott.emp s; --错误统计为4了,实际表有14条数据
--通常为避免错误,我们使用一个常数,使其字段永远不存在NULL值去统计
SELECT COUNT(1) AS count1 FROM scott.emp s; --此SELECT COUNT(1)统计方式永远不会错误,也提高了效率

ORACLE之SQL篇-生成累乘积

--生成累乘积
SELECT s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno
  FROM scott.emp s;
SELECT s.empno,
       s.ename,
       s.job,
       s.mgr,
       s.hiredate,
       s.sal,
       --根据员工不同部门累乘工资
       exp(SUM(ln(s.sal)) over(ORDER BY s.deptno)) AS deptno_sal,
       s.comm,
       s.deptno
  FROM scott.emp s;
SELECT s.empno,
       s.ename,
       s.job,
       s.mgr,
       s.hiredate,
       s.sal,
       --根据员工不同部门分别累乘工资
       exp(SUM(ln(s.sal)) over(PARTITION BY s.deptno)) AS deptno_sal,
       s.comm,
       s.deptno
  FROM scott.emp s;

--注意:理解分别(PARTITION)和累乘(ORDER)的含义

ORACLE之SQL篇-生成累计和

--生成累计和
SELECT s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno
  FROM scott.emp s;
SELECT s.empno,
       s.ename,
       s.job,
       s.mgr,
       s.hiredate,
       s.sal,
       --根据员工不同部门累计统计工资(不会根据部门合计s.sal)
       SUM(s.sal) over(ORDER BY s.sal, s.deptno) AS deptno_sal,
       s.comm,
       s.deptno
  FROM scott.emp s;
SELECT s.empno,
       s.ename,
       s.job,
       s.mgr,
       s.hiredate,
       s.sal,
       --根据员工不同部门累计统计工资(会根据部门合计s.sal)
       SUM(s.sal) over(ORDER BY s.deptno) AS deptno_sal,
       s.comm,
       s.deptno
  FROM scott.emp s;
SELECT s.empno,
       s.ename,
       s.job,
       s.mgr,
       s.hiredate,
       s.sal,
       --根据员工不同部门分别统计工资
       SUM(s.sal) over(PARTITION BY s.deptno) AS deptno_sal,
       s.comm,
       s.deptno
  FROM scott.emp s;

--注意:理解分别(PARTITION)和累计(ORDER)的含义

ORACLE之SQL篇-生成累计差

--生成累计差
--用法和累计和一致
SELECT s.empno,
       s.ename,
       s.job,
       s.mgr,
       s.hiredate,
       s.sal,
       --根据员工不同部门累差统计工资(不会根据部门合计s.sal)
       SUM(CASE
             WHEN rownum = 1 THEN
              (s.sal)
             ELSE
              (-s.sal)
           END) over(ORDER BY s.sal, s.deptno) AS deptno_sal,
       s.comm,
       s.deptno
  FROM scott.emp s;

ORACLE之SQL篇-生成连续数字值

--生成连续数字值
SELECT LEVEL id FROM dual CONNECT BY LEVEL < = 10

ORACLE之SQL篇-确定一年内属于周内某一天的所有日期

--确定一年内属于周内某一天的所有日期
---例如需要查询一年内所有星期五的日子
--首先查出一年的每一天和对应的星期
---星期的显示方式和数据库配置有关,安装时选择了中文.所以使用的是国内适用的显示方式
WITH x AS
 (SELECT trunc(SYSDATE, 'y') + LEVEL - 1 AS dy,
         to_char(trunc(SYSDATE, 'y') + LEVEL - 1, 'dy') AS dy5
    FROM dual
  CONNECT BY LEVEL <=
             add_months(trunc(SYSDATE, 'y'), 12) - trunc(SYSDATE, 'y'))
SELECT dy, dy5 AS fri FROM x;
--通过条件查询等于星期五
WITH x AS
 (SELECT trunc(SYSDATE, 'y') + LEVEL - 1 AS dy,
         to_char(trunc(SYSDATE, 'y') + LEVEL - 1, 'dy') AS dy5
    FROM dual
  CONNECT BY LEVEL <=
             add_months(trunc(SYSDATE, 'y'), 12) - trunc(SYSDATE, 'y'))
SELECT dy, dy5 AS fri FROM x WHERE dy5 = '星期五';

ORACLE之SQL篇-确定一年内的天数

--确定一年内的天数
--通过下一年的第一天-当前年的第一天
SELECT add_months(trunc(SYSDATE, 'y'), 12) - trunc(SYSDATE, 'y') AS days,
       SYSDATE
  FROM dual;
--2020为闰年,所以应有366天
SELECT add_months(trunc(DATE '2020-01-01', 'y'), 12) -
       trunc(DATE '2020-01-01', 'y') AS days
  FROM dual;

ORACLE之SQL篇-确定一年是否是闰年

--确定一年是否是闰年
--是否是闰年我们可以通过判断2月最后一天是否是29日,如果是则是闰年
SELECT '2019是' || (CASE
         WHEN to_char(last_day(DATE '2019-02-01'), 'DD') = 29 THEN
          '闰年'
         ELSE
          '非闰年'
       END) AS days,
       '2020是' || (CASE
          WHEN to_char(last_day(DATE '2020-02-01'), 'DD') = 29 THEN
           '闰年'
          ELSE
           '非闰年'
        END) AS days2,
       --trunc(?, 'y')返回任何一年的第一天
        trunc(SYSDATE, 'y') AS now_year_day
  FROM dual;

ORACLE之SQL篇-确定两个日期之间的月份或年数

--确定两个日期之间的月份或年数
SELECT months_between(DATE '1988-01-01', s.hiredate) AS sysdatehiredate_months_between,
       months_between(DATE '1988-01-01', s.hiredate) / 12 AS sysdatehiredate_years_between,
       --trunc可以理解为干掉小数点后面的,只保留常数,不进位,也不做任何处理,只是截断
       trunc(months_between(DATE '1988-01-01', s.hiredate)) AS trunc_months_between,
       --如果不满一年则返回为0
       trunc(months_between(DATE '1988-01-01', s.hiredate) / 12) AS trunc_years_between,
       s.hiredate
  FROM scott.emp s;

ORACLE之SQL篇-给结果分等级

--给结果分等级
SELECT s.sal, dense_rank() over(ORDER BY s.sal) rnk FROM scott.emp s;

ORACLE之SQL篇-给结果集分页

--给结果集分页
SELECT row_number() over(ORDER BY s.sal) AS row_sal, s.sal
  FROM scott.emp s
 ORDER BY s.sal;

ORACLE之SQL篇-表示子-父-祖父关系

--表示子-父-祖父关系
--以下三句sql可以推测个员工MILLER的上级及其上上级的上级的关系为:MILLER-->CLARK-->KING
SELECT s.empno, s.mgr, s.ename FROM scott.emp s WHERE s.ename = 'MILLER'; --MILLER上级编号是7782
SELECT s.empno, s.mgr, s.ename FROM scott.emp s WHERE s.empno = '7782'; --通过查询便知,MILLER上级是CLARK,CLARK上级编号是7839
SELECT s.empno, s.mgr, s.ename FROM scott.emp s WHERE s.empno = '7839'; --通过查询便知,CLARK上级是KING
--以上sql可以使用一句sql完成
SELECT ltrim(sys_connect_by_path(ename, '-->'), '-->') AS root
  FROM scott.emp
 WHERE LEVEL = 3
 START WITH ename = 'MILLER'
CONNECT BY PRIOR mgr = empno;

--放开条件
SELECT ltrim(sys_connect_by_path(ename, '-->'), '-->') AS root
  FROM scott.emp
 WHERE LEVEL >1
 START WITH ename is not null
CONNECT BY PRIOR mgr = empno
order by root;

ORACLE之SQL篇-表示父-子关系

--表示父-子关系
SELECT s.ename || '的上级是' || o.ename AS ename_mgr, s.mgr, o.job, s.job
  FROM scott.emp s
  JOIN scott.emp o
    ON s.mgr = o.empno
 ORDER BY s.mgr;

ORACLE之SQL篇-计算两个日期之间的天数

--计算两个日期之间的天数
SELECT s.hiredate AS from1,
       trunc(SYSDATE) AS to1,
       trunc(SYSDATE) - s.hiredate AS from_to1,
       s.hiredate AS from2,
       DATE '1988-01-01' AS to2,
       DATE '1988-01-01' - s.hiredate AS from_to2
  FROM scott.emp s;

ORACLE之SQL篇-计算中间值

--计算中间值
SELECT s.sal FROM scott.emp s WHERE s.deptno = 20 ORDER BY s.sal; --明显中间值为2975
--使用median
SELECT median(s.sal) AS median_sal FROM scott.emp s WHERE s.deptno = 20;
--也可以使用,此方法扩展性强
SELECT percentile_cont(0.5) within GROUP(ORDER BY s.sal) AS median_sal
  FROM scott.emp s
 WHERE s.deptno = 20;

--可以查找首值和未值,当然可以max和min替代
SELECT percentile_cont(0) within GROUP(ORDER BY s.sal) AS median_sal
  FROM scott.emp s
 WHERE s.deptno = 20;
SELECT percentile_cont(1) within GROUP(ORDER BY s.sal) AS median_sal
  FROM scott.emp s
 WHERE s.deptno = 20;

ORACLE之SQL篇-计算平均值

--计算平均值
SELECT * FROM scott.emp s;
SELECT AVG(s.sal) AS avg_comm FROM scott.emp s;
--计算雇员编号为20的平均工资
SELECT AVG(s.sal) AS avg_comm FROM scott.emp s WHERE s.deptno = 20;
--计算每种编号的平均工资
SELECT AVG(s.sal) AS avg_comm, s.deptno FROM scott.emp s GROUP BY s.deptno;
--所有的聚合函数都会忽略NULL值(2200/4)
SELECT AVG(s.comm) AS avg_comm, s.deptno
  FROM scott.emp s
 GROUP BY s.deptno;
--可以把NULL值做处理,此时一目了然,10和20部门下的员工没有奖金(2200/6)
SELECT AVG(nvl(s.comm, 0)) AS avg_comm, s.deptno
  FROM scott.emp s
 GROUP BY s.deptno;

ORACLE之SQL篇-计算模式

--计算模式
--计算数据重复数量最多的
SELECT s.job FROM scott.emp s ORDER BY s.job; --明显SALESMAN和CLERK最多次数,倒序模式第一个为SALESMAN
--通过工作倒序排列出现最多的模式
SELECT MAX(job) keep(dense_rank FIRST ORDER BY cnt DESC) AS job
  FROM (SELECT s.job, COUNT(1) cnt FROM scott.emp s GROUP BY job);

ORACLE之SQL篇-计算相对总数的百分比

--计算相对总数的百分比
---第一步
SELECT s.job,
       COUNT(1) over(PARTITION BY s.job) AS num_emps,
       ratio_to_report(s.sal) over() * 100 AS pct
  FROM scott.emp s
 ORDER BY num_emps, s.job;
---第二步
SELECT job, SUM(pct)
  FROM (SELECT s.job, ratio_to_report(s.sal) over() * 100 AS pct
          FROM scott.emp s)
 GROUP BY job
 ORDER BY job;

--合计结果(检查总和是否是100%)
SELECT round(14.2980189491817 + 19.2937123169681 + 17.2265288544358 +
             28.5099052540913 + 20.671834625323,
             1) AS sum_row
  FROM dual

ORACLE之SQL篇-计算简单的小计

--计算简单的小计
--我们可以通过以下语句查看不同工作的工资合计,但我们无法同时展示合计到某一字段
SELECT s.job,
       (s.sal) AS sal,
       SUM(s.sal) over(PARTITION BY s.job) AS sum_sal
  FROM scott.emp s
 ORDER BY s.job;
SELECT DISTINCT s.job, SUM(s.sal) over(PARTITION BY s.job) AS sum_sal
  FROM scott.emp s
 ORDER BY s.job;
--解决方案:通过GROUPING可以合计所有的工资
SELECT (CASE GROUPING(s.job)
         WHEN 0 THEN
          s.job
         ELSE
          'TOTAL'
       END) AS job,
       SUM(s.sal) AS sal
  FROM scott.emp s
 GROUP BY ROLLUP(s.job)
 ORDER BY s.job;

ORACLE之SQL篇-跳过表中的N行

--跳过表中的N行
--跳过偶数行
SELECT row_sal, sal
  FROM (SELECT row_number() over(ORDER BY s.sal) AS row_sal, s.sal
          FROM scott.emp s
         ORDER BY s.sal)
 WHERE MOD(row_sal, 2) = 1;
--跳过奇数行
SELECT row_sal, sal
  FROM (SELECT row_number() over(ORDER BY s.sal) AS row_sal, s.sal
          FROM scott.emp s
         ORDER BY s.sal)
 WHERE MOD(row_sal, 2) = 0;

ORACLE之SQL篇-轮换行值

--轮换行值
SELECT s.ename,
       s.hiredate,
       s.sal,
       nvl(lead(s.sal) over(ORDER BY s.sal), MIN(s.sal) over()) AS FORWARD,
       nvl(lag(s.sal) over(ORDER BY s.sal), MAX(s.sal) over()) AS rewind
  FROM scott.emp s

ORACLE之SQL篇-返回某月的第一天和最后一天

--返回某月的第一天和最后一天
SELECT trunc(DATE '2019-05-20', 'MM') AS trunc_date,
       last_day(DATE '2019-05-20') AS last_day
  FROM dual;