解决方案 »
- oracle字符集
- 存储过程commit问题
- 请大牛帮忙看下
- 一个关于数据查询语句的问题
- 从网上搜了一个ORACLE发送邮件的过程,目前邮件可以正常发送,但接收到的附件都是空的,请大神帮忙看看啥情况?
- 请问装了ORACLE9i,怎么配置!
- 请问Oracle的错误号
- update HR_DEGREE set id = ( select SEQ_HR_DEGREE_ID.nextval from dual );的问题
- ORACLE8.15客户端的安装问题,怪!
- ORACLE A表存储父节点及所有子集的数据,B表存父ID及对应的数据,查询出父ID下所有子集对应的B表数据
- 高分请教数据库定时扫描是否合适?
- oracle存储过程编译不过
FROM TESTTABLE T
GROUP BY T.KESHI,T.YS
大概就是这么个意思。
select '1' as yf,'k1' as ks,'y1' as ys,1 as sl from dual
union all
select '1' as yf,'k2' as ks,'y2' as ys,2 as sl from dual
union all
select '2' as yf,'k2' as ks,'y2' as ys,1 as sl from dual
union all
select '2' as yf,'k3' as ks,'y3' as ys,6 as s1 from dual
)
select ks,ys,max(decode(yf,'1',sl,0)) as yy,max(decode(yf,'2',sl,0)) as ey from test group by ks,ys order by ks
from tb_depart_info_1 a, tb_depart_info_2 b
where a.depart_no(+) = b.depart_no
create table TEST_20130207
(
STATICE_TIME VARCHAR2(20),
SUBJECT_NAME VARCHAR2(10),
DOCTOR_NAME VARCHAR2(10),
ORDER_NUMBER VARCHAR2(10)
)
;
comment on table TEST_20130207
is '测试用临时表';
comment on column TEST_20130207.STATICE_TIME
is '月份';
comment on column TEST_20130207.SUBJECT_NAME
is '科室';
comment on column TEST_20130207.DOCTOR_NAME
is '医生';
comment on column TEST_20130207.ORDER_NUMBER
is '数量';prompt Disabling triggers for TEST_20130207...
alter table TEST_20130207 disable all triggers;
prompt Loading TEST_20130207...
insert into TEST_20130207 (STATICE_TIME, SUBJECT_NAME, DOCTOR_NAME, ORDER_NUMBER)
values ('201201', 'k1', 'y1', '1');
insert into TEST_20130207 (STATICE_TIME, SUBJECT_NAME, DOCTOR_NAME, ORDER_NUMBER)
values ('201201', 'k2', 'y2', '2');
insert into TEST_20130207 (STATICE_TIME, SUBJECT_NAME, DOCTOR_NAME, ORDER_NUMBER)
values ('201202', 'k2', 'y2', '1');
insert into TEST_20130207 (STATICE_TIME, SUBJECT_NAME, DOCTOR_NAME, ORDER_NUMBER)
values ('201202', 'k3', 'y3', '6');
commit;
这是最终的sql语句,你看看
SELECT t.subject_name 科室, t.doctor_name 医生,
sum(CASE WHEN t.statice_time='201201' THEN t.order_number
ELSE '0' END) 一月,
sum(CASE WHEN t.statice_time='201202' THEN t.order_number
ELSE '0' END) 二月 FROM test_20130207 t
GROUP BY t.subject_name , t.doctor_name
ORDER BY t.subject_name