oracle 统计数据

[wswzwz]

我有如下数据
一月份 二月份
科室 医生 数量 科室 医生 数量
k1 y1 1
k2 y2 2 k2 y2 1
k3 y3 6

以上为查询出来的数据
我要将以上两块数据合并到一起如下
科室 医生 一月 二月
k1 y1 1 0
k2 y2 2 1
k3 y3 0 6

[yuppy]

你的表里面该有区分月份的字段把。 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

大概就是这么个意思。

[小海葵1]

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

[睿智天成]

select table1.科室,table1.医生,table1.数量 '一月',nvl(table2.数量,0) '二月' from table1 left join table2 on table1.医生=table2.医生 group by table1.科室

[lovezxb0312]

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

[善若止水]

这是建表语句，包括字段的说明以及测试数据 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