大家好，我是讯享网，很高兴认识大家。



列字段的处理 SQL

——Case：用于实现多条件判断，在WHEN之后编写条件，而在THEN之后编写条件满足的显示操作，如果都不满足则使用ELSE中的表达式处理

——DECODE：多值判断，如果某一个列（或某一个值）与判断值相同，则使用指定的显示结果输出，如果没有满足条件，则显示默认值

SQL> select deptno,sum(sal) from emp group by deptno;

 

Select

case

when deptno=10 then ‘ACCOUNTING‘

when deptno=20 then ‘RESERCH‘

when deptno=30 then ‘SALES‘

end,

sum(sal) from emp

group by deptno

DECODE()函数语法结构：

decode (expression, search_1, result_1)
decode (expression, search_1, result_1, search_2, result_2)
decode (expression, search_1, result_1, search_2, result_2, …., search_n, result_n)

decode (expression, search_1, result_1, default)
decode (expression, search_1, result_1, search_2, result_2, default)
decode (expression, search_1, result_1, search_2, result_2, …., search_n, result_n, default)

比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。

select

decode(deptno,

10, ‘ACCOUNTING‘,

20, ‘RESERCH‘,

30, ‘SALES‘

),

sum(sal) from emp

group by deptno

DECODE行转列

SQL> select job,ename,sal from emp where job=‘MANAGER‘;

 

JOB       ENAME             SAL

——— ———- ———-

MANAGER   JONES            2975

MANAGER   BLAKE            2850

MANAGER   CLARK            2450

 

SQL> select job,decode(ename,‘BLAKE‘,SAL) BLAKE,decode(ename,‘JONES‘,SAL) JONES,decode(ename,‘CLARK‘,SAL) CLARK from emp where job=‘MANAGER‘;

 

JOB            BLAKE      JONES      CLARK

——— ———- ———- ———-

MANAGER                    2975

MANAGER         2850

讯享网

MANAGER                               2450

 

SQL> select job,sum(decode(ename,‘BLAKE‘,SAL)) BLAKE,sum(decode(ename,‘JONES‘,SAL)) JONES,sum(decode(ename,‘CLARK‘,SAL)) CLARK from emp where job=‘MANAGER‘ group by job;

 

JOB            BLAKE      JONES      CLARK

——— ———- ———- ———-

MANAGER         2850       2975       2450

 

SQL> select job,avg(decode(ename,‘BLAKE‘,SAL)) BLAKE,max(decode(ename,‘JONES‘,SAL)) JONES,min(decode(ename,‘CLARK‘,SAL)) CLARK from emp where job=‘MANAGER‘ group by job;

 

JOB            BLAKE      JONES      CLARK

——— ———- ———- ———-

MANAGER         2850       2975       2450

查询每个部门中各个职位的总工资 —— 按照部门编号及职位进行分组

SELECT deptno , job , SUM(sal)

FROM emp

GROUP BY deptno , job ;

 

 

查询每个部门中各个职位的总工资 —— 将多条工资统计信息放在一行上进行显示

SELECT deptno ,

SUM(DECODE(job, ‘PRESIDENT‘ , sal , 0 )) PRESIDENT_JOB ,

SUM(DECODE(job, ‘MANAGER‘ , sal , 0)) MANAGER_JOB ,

SUM(DECODE(job , ‘ANALYST‘ , sal , 0 )) ANALYST_JOB ,

SUM(DECODE(job , ‘CLERK‘ , sal, 0 )) CLERK_JOB ,

SUM(DECODE(job , ‘SALESMAN‘ , sal , 0)) SALESMAN_JOB

FROM emp

GROUP BY deptno ;

 

DECODE()函数是ORACLE自己的特色，如没有DECODE()函数，

一般数据库利用SELECT子句使用子查询方式完成.

SELECT temp.dno,SUM(manager_job),SUM(clerk_job)

FROM(

    SELECT deptno dno,

        (SELECT SUM(sal) FROM emp WHERE job=‘MANAGER‘ AND empno=e.empno) manager_job,

        (SELECT SUM(sal) FROM emp WHERE job=‘CLERK‘ AND empno=e.empno) clerk_job

    FROM emp e) temp

GROUP BY temp.dno;