oracle 统计数据

wswzwz 2013-02-04 04:14:41
我有如下数据
一月份 二月份
科室 医生 数量 科室 医生 数量
k1 y1 1
k2 y2 2 k2 y2 1
k3 y3 6

以上为查询出来的数据
我要将以上两块数据合并到一起如下
科室 医生 一月 二月
k1 y1 1 0
k2 y2 2 1
k3 y3 0 6
...全文
291 5 点赞 打赏 收藏 举报
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
善若止水 2013-02-07
这是建表语句,包括字段的说明以及测试数据 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
  • 打赏
  • 举报
回复
lovezxb0312 2013-02-06
select b.depart_no, b.doctor_type, nvl(a.quntitity, 0), b.quntitity from tb_depart_info_1 a, tb_depart_info_2 b where a.depart_no(+) = b.depart_no
  • 打赏
  • 举报
回复
睿智天成 2013-02-06
select table1.科室,table1.医生,table1.数量 '一月',nvl(table2.数量,0) '二月' from table1 left join table2 on table1.医生=table2.医生 group by table1.科室
  • 打赏
  • 举报
回复
restbely 2013-02-05
with test as ( 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
  • 打赏
  • 举报
回复
yuppy 2013-02-04
你的表里面该有区分月份的字段把。 TESTTABLE 字段 KESHI, YS, YSCOUNT, MYMON
SELECT T.KESHI, T.YS, SUM(CASE WHEN MYMON = 1 THEN YSCOUNT ELSE NULL END) AA,SUM(CASE WHEN MYMON =  2 THEN YSCOUNT ELSE NULL END) BB
FROM TESTTABLE T
GROUP BY T.KESHI,T.YS
大概就是这么个意思。
  • 打赏
  • 举报
回复
相关推荐
发帖
Oracle
加入

1.6w+

社区成员

Oracle开发相关技术讨论
申请成为版主
帖子事件
创建了帖子
2013-02-04 04:14
社区公告
暂无公告