是关于医疗信息的统计问题相关的信息如下所示:信息ID 年龄 医疗ID 花费
2006001 15 01(骨科) 40
2006002 14 02(眼科) 50
2006003 15 03(鼻科) 20
2006004 16 02(眼科) 10
2006005 15 01(骨科) 30
2006006 14 02(眼科) 40
2006007 16 01(骨科) 10
2006008 14 01(骨科) 30
2006009 15 02(眼科) 40根据年龄和科室来统计,希望的出结果是
(年龄范围14-17,科室01-04)
年龄 理疗科室 次数 费用
14 01 1 30
14 02 2 90
14 03 0 0
14 04 0 0
15 01 2 70
15 02 1 40
15 03 1 20
15 04 0 0
16 01 1 10
16 02 1 10
16 03 0 0
16 04 0 0
17 01 0 0
17 02 0 0
17 03 0 0
17 04 0 0实际上的数据比这个要复杂,但是基本的逻辑就是这个了。希望大家指点指点。
2006001 15 01(骨科) 40
2006002 14 02(眼科) 50
2006003 15 03(鼻科) 20
2006004 16 02(眼科) 10
2006005 15 01(骨科) 30
2006006 14 02(眼科) 40
2006007 16 01(骨科) 10
2006008 14 01(骨科) 30
2006009 15 02(眼科) 40根据年龄和科室来统计,希望的出结果是
(年龄范围14-17,科室01-04)
年龄 理疗科室 次数 费用
14 01 1 30
14 02 2 90
14 03 0 0
14 04 0 0
15 01 2 70
15 02 1 40
15 03 1 20
15 04 0 0
16 01 1 10
16 02 1 10
16 03 0 0
16 04 0 0
17 01 0 0
17 02 0 0
17 03 0 0
17 04 0 0实际上的数据比这个要复杂,但是基本的逻辑就是这个了。希望大家指点指点。
age as 年龄 , office as 理疗科室
count(*) as 次数,sum(fee) as 费用
where (age>=14 and age <=17) and (office>=01 and office<=04)
group by age,office楼主试试吧,我没数据库,凭感觉写的,不知道对不对。
group by 年龄,医疗ID;
from table
where (年龄 between 14 and 17) and (科室 between 1 and 4)
order by 年龄,医疗ID
group by 年龄,医疗ID
from table
where (年龄 between 14 and 17) and (科室 between 1 and 4)
order by 年龄,医疗ID
group by 年龄,医疗ID
直接group by 就能出结果
CREATE OR REPLACE VIEW HospMang
(age, office)
AS
SELECT '14','01' FROM DUAL UNION SELECT '14','02' FROM DUAL UNION SELECT '14','03' FROM DUAL UNION SELECT '14','04' FROM DUAL union
SELECT '15','01' FROM DUAL UNION SELECT '15','02' FROM DUAL UNION SELECT '15','03' FROM DUAL UNION SELECT '15','04' FROM DUAL union
SELECT '16','01' FROM DUAL UNION SELECT '16','02' FROM DUAL UNION SELECT '16','03' FROM DUAL UNION SELECT '16','04' FROM DUAL union
SELECT '17','01' FROM DUAL UNION SELECT '17','02' FROM DUAL UNION SELECT '17','03' FROM DUAL UNION SELECT '17','04' FROM DUAL 如果在数据量小的情况下可以这么做,如果太大可以建一个自增长的序列的。然后写sql取出那些数:
select hm.age, hm.office, t.num, t.fee
from HospMang hm,
(select age, office, count(*) num, sum(fee) fee
from table
group by age, office) t
where hm.age = t.age(+)
and hm.office = t.office(+)这样就应该可以了吧
from table
where (年龄 between 14 and 17) and (科室 between 1 and 4)
group by 年龄,医疗ID
理論上這句語句就可以了吧
t1 varchar2(10), -- Entry_date
t2 varchar2(20), -- Age
t3 varchar2(20), -- DepartId
t4 varchar2(20) -- Amount
)
insert into tang values('2006001', '15', '01', '40');
insert into tang values('2006002', '14', '02', '50');
insert into tang values('2006003', '15', '03', '20');
insert into tang values('2006004', '16', '02', '10');
insert into tang values('2006005', '15', '01', '30');
insert into tang values('2006006', '14', '02', '40');
insert into tang values('2006007', '16', '01', '10');
insert into tang values('2006008', '14', '01', '30');
insert into tang values('2006009', '15', '02', '40');
select * from tang;select
a.t2,
b.rn t3,
sum(decode(b.rn,a.t3,a.t4,0)) t4
from tang a,(select
'0'||rownum rn
from dual
connect by rownum<=4) b --- 不知道你的理疗科室最大是多少,这里为4
group by a.t2, b.rn
order by t2, rnresultL
T2 T3 T4
14 01 30
14 02 90
14 03 0
14 04 0
15 01 70
15 02 40
15 03 20
15 04 0
16 01 10
16 02 10
16 03 0
16 04 0
select
c.age t2,
b.rn t3,
sum(decode(b.rn,a.t3,decode(c.age,a.t2,a.t4,0),0)) t4
from tang a,(select
'0'||rownum rn
from dual
connect by rownum<=4) b,(select
13+rownum age
from dual
connect by rownum<=4) c
group by c.age, b.rn
order by c.age, rn
RESULT:
T3 T4
14 01 30
14 02 90
14 03 0
14 04 0
15 01 70
15 02 40
15 03 20
15 04 0
16 01 10
16 02 10
16 03 0
16 04 0
17 01 0
17 02 0
17 03 0
17 04 0
count(*) 次数,sum(费用) 费用
where (年龄 between 14 and 17) and (office>=01 and office <=04)
group by 年龄,医疗ID
age as 年龄 , office as 理疗科室 from table
where (age>=14 and age <=17) and (office>=01 and office <=04)
group by age,office ;