泉州城乡住房建设厅网站,做电商怎么建网站,国家域名注册证书有用吗,肥西县建设局网站DECODE函数相当于一条件语句(IF),它将输入数值与函数中的参数列表相比较#xff0c;根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然#xff0c;如果未能与任何一个实参序偶匹配成功#xff0c;则函数也有默认的返回值。区别于… DECODE函数相当于一条件语句(IF),它将输入数值与函数中的参数列表相比较根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然如果未能与任何一个实参序偶匹配成功则函数也有默认的返回值。区别于SQL的其它函数DECODE函数还能识别和操作空值。 其具体的语法格式如下 DECODE(input_value,value,result[,value,result...][,default_result]); 其中 input_value试图处理的数值。DECODE函数将该数值与一系列的序偶相比较以决定最后的返回结果value是一组成序偶的数值。如果输入数值与之匹配成功则相应的结果将被返回。 对应一个空的返回值可以使用关键字NULL于之对应 result是一组成序偶的结果值 default_result未能与任何一序偶匹配成功时函数的返回的默认值。下面以例子说明 利用DECODE函数求出基于不同职位的(job)每个员工加薪后的工资值。涉及的表名:emp
SQL select ename Name,job,sal Salary,2 DECODE(job,SALESMAN,sal*1.15,3 CLERK, sal*1.20,4 ANALYST,sal*1.25,5 sal*1.40) New Salary6 from emp7 order by job;Name JOB Salary New Salary
------- --------- ---------- ----------
SCOTT ANALYST 3000 3750
FORD ANALYST 3000 3750
MILLER CLERK 1300 1560
JAMES CLERK 950 1140
SMITH CLERK 800 960
ADAMS CLERK 1100 1320
BLAKE MANAGER 2850 3990
JONES MANAGER 2975 4165
CLARK MANAGER 2450 3430
KING PRESIDENT 5000 7000
TURNER SALESMAN 1500 1725
MARTIN SALESMAN 1250 1437.5
WARD SALESMAN 1250 1437.5
ALLEN SALESMAN 1600 1840已选择14行。 在上面例子查询语句中DECODE函数执行的步骤如下(1)、当job为SALESMAN时DECODE函数返回表达式sal*1.5的值否则执行(2)(2)、当job为CLERK时DECODE函数返回表达式sal*1.20的值否则执行(3)(3)、当job为ANALYST时DECODE函数返回表达式sal*1.25的值否则执行(4)(4)、DECODE函数返回表达式sal*1.40的值。 最近10天中每天日志切换的量(即可分析10天的波度又可分析24小时内可很容易看出异常情况)
SELECT SUBSTR(TO_CHAR(first_time, MM/DD/RR HH:MI:SS),1,5) Day,SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),00,1,0)) H00,SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),01,1,0)) H01, SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),02,1,0)) H02,SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),03,1,0)) H03,SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),04,1,0)) H04,SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),05,1,0)) H05,SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),06,1,0)) H06,SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),07,1,0)) H07,SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),08,1,0)) H08,SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),09,1,0)) H09,SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),10,1,0)) H10,SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),11,1,0)) H11, SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),12,1,0)) H12,SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),13,1,0)) H13, SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),14,1,0)) H14,SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),15,1,0)) H15, SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),16,1,0)) H16, SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),17,1,0)) H17, SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),18,1,0)) H18, SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),19,1,0)) H19, SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),20,1,0)) H20, SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),21,1,0)) H21,SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),22,1,0)) H22 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, MM/DD/RR HH24:MI:SS),10,2),23,1,0)) H23, COUNT(*) TOTAL
FROM v$log_history a where first_timeto_char(sysdate-11)
GROUP BY SUBSTR(TO_CHAR(first_time, MM/DD/RR HH:MI:SS),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, MM/DD/RR HH:MI:SS),1,5) DESC; 转载于:https://www.cnblogs.com/Richardzhu/p/3598005.html
