点击(此处)折叠或打开

行列转换
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;
select temp.dno, sum(temp.PRESIDENT_job),sum(temp.MANAGER_job),sum(temp.ANALYST_job),sum(temp.CLERK_job),sum(temp.SALESMAN_job)
from(
select deptno dno,
(select sum(sal) from emp where job = \'PRESIDENT\' and empno = e.empno) PRESIDENT_job,
(select sum(sal) from emp where job = \'MANAGER\' and empno = e.empno) MANAGER_job,
(select sum(sal) from emp where job = \'ANALYST\' and empno = e.empno) ANALYST_job,
(select sum(sal) from emp where job = \'CLERK\' and empno = e.empno) CLERK_job,
(select sum(sal) from emp where job = \'SALESMAN\' and empno = e.empno)SALESMAN_job
from emp e) temp
group by temp.dno
order by temp.dno;
select * from (
select deptno , job , sal ,
sum(sal) over (partition by deptno) sum_sal,
max(sal) over (partition by deptno) max_sal,
min(sal) over (partition by deptno) min_sal
from emp)
PIVOT (
sum(sal)
for job in(
\'PRESIDENT\' AS PRESIDENT_job,
\'MANAGER\' AS MANAGER_job,
\'ANALYST\' AS ANALYST_job,
\'CLERK\' AS CLERK_job,
\'SALESMAN\' AS SALESMAN_job
)
);
select * from (select deptno , job ,sal from emp)
PIVOT XML (
sum (sal)
for job in (ANY)
)order by deptno;
select * from (
select deptno , job , sal
from emp)
PIVOT (
sum(sal) AS sum_sal , max(sal) max_sal
for job in(
\'PRESIDENT\' AS PRESIDENT_job,
\'MANAGER\' AS MANAGER_job,
\'ANALYST\' AS ANALYST_job,
\'CLERK\' AS CLERK_job,
\'SALESMAN\' AS SALESMAN_job
)
);
alter table emp ADD(sex varchar(10) default \'男\');
update emp set sex=\'女\' where to_char(hiredate,\'yyyy\') = \'1981\';
commit;
select * from (
select deptno , job , sal ,sex
from emp)
PIVOT (
sum(sal) AS sum_sal , max(sal) max_sal
for (job,sex) in(
(\'PRESIDENT\' , \'男\') AS PRESIDENT_male_job,
(\'MANAGER\',\'女\' ) AS MANAGER_female_job,
(\'ANALYST\',\'男\') AS ANALYST_male_job,
(\'CLERK\',\'女\') AS CLERK_female_job
)
);
with temp AS (
select * from (select deptno , job , sal from emp)
PIVOT (
sum(sal)
for job in (
\'PRESIDENT\' AS PRESIDENT_job,
\'MANAGER\' AS MANAGER_job,
\'ANALYST\' AS ANALYST_job,
\'CLERK\' AS CLERK_job,
\'SALESMAN\' AS SALESMAN_job
)
)ORDER BY deptno
)select * from temp
UNPIVOT (
sal_sum for job in(
PRESIDENT_job AS \'PRESIDENT\',
MANAGER_job AS \'MANAGER\',
ANALYST_job AS \'ANALYST\',
CLERK_job AS \'CLERK\',
SALESMAN_job AS \'SALESMAN\'
)
)ORDER BY deptno;
select consult.user_id,consult.LiCai,DaiKuan,ZiXu
from
(select user_id, (select service_type from user_consult where service_type=3 and c.id = id)LiCai,
(select service_type from user_consult where service_type=81 and c.id = id )DaiKuan,
(select service_type from user_consult where service_type=79 and c.id = id)ZiXu from user_consult c)consult
group by consult.user_id

ITPub博客

Oracle 开发学习篇行列转换

最新文章

ITPub博客

Oracle 开发学习篇 行列转换

最新文章

Oracle 开发学习篇行列转换