oracle的一些使用技巧1

1.使用创建临时表

with student as (select 101 as sid, '张三' as sname, 10 as sage from dual union  select 102 as sid, '李四' as sname, 20 as sage from dual union  select 103 as sid, '王五' as sname, 30 as sage from dual)select * from student;

图1

2.将查询结果转置（使用pivot和union all将上面的student表转置）

注：如果不想建student表的话，就在这条sql语句的前面加上1中的前4行

select '学号' as item,       to_char(mt1.mate1) as mate1,       to_char(mt1.mate2) as mate2,       to_char(mt1.mate3) as mate3  from (select *          from (select t1.tempType, t1.sid                  from (select decode(s.sid,                                      101, 'type1',                                      102, 'type2',                                      103, 'type3') as tempType,                               s.sid,                               s.sname,                               s.sage                          from student s) t1)        pivot(sum(sid)           for tempType in('type1' as mate1,                          'type2' as mate2,                          'type3' as mate3))) mt1union allselect '姓名' as item, mt2.*  from (select *          from (select t2.tempType, t2.sname                  from (select decode(s.sid,                                      101, 'type1',                                      102, 'type2',                                      103, 'type3') as tempType,                               s.sid,                               s.sname,                               s.sage                          from student s) t2)        pivot(max(sname)           for tempType in('type1' as mate1,                          'type2' as mate2,                          'type3' as mate3))) mt2union allselect '年龄' as item,       to_char(mt3.mate1) as mate1,       to_char(mt3.mate2) as mate2,       to_char(mt3.mate3) as mate3  from (select *          from (select t3.tempType, t3.sage                  from (select decode(s.sid,                                      101, 'type1',                                      102, 'type2',                                      103, 'type3') as tempType,                               s.sid,                               s.sname,                               s.sage                          from student s) t3)        pivot(sum(sage)           for tempType in('type1' as mate1,                          'type2' as mate2,                          'type3' as mate3))) mt3;

图2

附：更为简便的写法

select col as item, mate1, mate2, mate3  from (select rn, col, val          from (select rownum rn,                       to_char(student.sid) as sid,                       student.sname,                       to_char(student.sage) as sage                  from student) unpivot(val for col in(sid, sname, sage)))pivot(max(val)   for rn in(1 as mate1, 2 as mate2, 3 as mate3));

3.查询金额所占比例（使用over，round，to_char）

with student as(select 101 as sid, '北京' as city, 10 as money from dual unionselect 102 as sid, '上海' as city, 20 as money from dual unionselect 103 as sid, '广州' as city, 30 as money from dual)select sid, city, money,to_char(round(money/sum(money) over(),2),'fm9999990.00') as moneyRatio from student;

图3

4.现有两张表（图4）

图4

with t_class as(select 1 as cid, '1班' as className from dual unionselect 2 as cid, '2班' as className from dual),t_student as(select 101 as sid, '张三' as sname, '北京' as scity, 1 as cid from dual union    select 102 as sid, '李四' as sname, '上海' as scity, 1 as cid from dual union    select 103 as sid, '王五' as sname, '广州' as scity, 1 as cid from dual union    select 104 as sid, '赵六' as sname, '深圳' as scity, 2 as cid from dual union    select 105 as sid, '钱七' as sname, '珠海' as scity, 2 as cid from dual union    select 106 as sid, '孙八' as sname, '厦门' as scity, 2 as cid from dual)

（1）统计各班级的学生、来自的城市、以及班级人数（使用wm_concat）

查询结果图

图5

select tc.cid, tc.className,       wm_concat(ts.sid) as c_sid,       wm_concat(ts.sname) as c_sname,       wm_concat(ts.scity) as c_scity,       count(sid) as c_count  from t_class tc  left join t_student ts    on tc.cid = ts.cidgroup by tc.cid, tc.className;

（2）统计各班级的学生人数

查询结果图

图6

方法一：

select t2.cid, t1.className, t2.c_count  from t_class t1  left join (select tc.cid, count(sid) as c_count               from t_class tc               left join t_student ts                 on tc.cid = ts.cid              group by tc.cid) t2    on t1.cid = t2.cid;

方法二（写法更为简单）：

select tc.cid, max(className) as className, count(sid) as c_count  from t_class tc  left join t_student ts    on tc.cid = ts.cid group by tc.cid;