三个表: WL_SCORE_TAB , wl_course_tab , wl_student_tab
分别是成绩表.课程表.学生信息表要创建一个视图.
字段是:ws.class_id, wct.course_name,category,average_score
分别是:班级id,课程名称,类别,平均分
其中类别有三个:male,female,classmale是统计班级男生平均分(其中学生信息表里面的sex,W表示女,M表示男)
female是统计班级女生平均分
class是统计全班平均分出来视图大概效果要是这样的:
-----------------------------------------------------------------------
class_id course_name category average_score
1 a male 70.0
1 a female 60.0
1 a class 65.0
1 b male 50.0
1 b female 60.0
1 b class 60.0
2 a male 60.0
............
我写了一部分...实在不知道怎么改了...来求教高手.....create view SELECT_AVG_SCORE_V as Select ws.class_id, wct.course_name,case when ws.sex='M' then 'male' when ws.sex='W' then 'female' else 'class'end category,ROUND(AVG(wst.score), 2) as average_score from WL_SCORE_TAB wst, wl_course_tab wct, wl_student_tab wswhere wst.student_id=ws.student_id and wst.course_id=wct.course_id group by ws.class_id,wct.course_nameorder by class_id,course_id,category asc;
分别是成绩表.课程表.学生信息表要创建一个视图.
字段是:ws.class_id, wct.course_name,category,average_score
分别是:班级id,课程名称,类别,平均分
其中类别有三个:male,female,classmale是统计班级男生平均分(其中学生信息表里面的sex,W表示女,M表示男)
female是统计班级女生平均分
class是统计全班平均分出来视图大概效果要是这样的:
-----------------------------------------------------------------------
class_id course_name category average_score
1 a male 70.0
1 a female 60.0
1 a class 65.0
1 b male 50.0
1 b female 60.0
1 b class 60.0
2 a male 60.0
............
我写了一部分...实在不知道怎么改了...来求教高手.....create view SELECT_AVG_SCORE_V as Select ws.class_id, wct.course_name,case when ws.sex='M' then 'male' when ws.sex='W' then 'female' else 'class'end category,ROUND(AVG(wst.score), 2) as average_score from WL_SCORE_TAB wst, wl_course_tab wct, wl_student_tab wswhere wst.student_id=ws.student_id and wst.course_id=wct.course_id group by ws.class_id,wct.course_nameorder by class_id,course_id,category asc;
create view SELECT_AVG_SCORE_V as
with t1 as(
select st.class_id, cs.course_name,
avg(decode(st.sex,'M',sc.score)) "male",
avg(decode(st.sex,'W',sc.score)) "femail",
avg(sc.score) "class"
from wl_score_tab sc , wl_course_tab cs , wl_student_tab st
where sc.student_id=st.student_id and sc.course_id=cs.course_id
group by st.class_id, cs.course_name),
t2 as(
select rownum rn from dual connect by rownum<=3
)
select t1.class_id,t1.course_name,
decode(t2.rn,1,'male',2,'female',3,'class') catagory,
decode(t2.rn,1,"male",2,"female",3,"class") average_score
from t1, t2;
create view SELECT_AVG_SCORE_V as
Select ws.class_id, wct.course_name,
round(avg(decode(ws.sex,'M',wst.score)),2) male_score,
round(avg(decode(ws.sex,'W',wst.score)),2) female_score,
ROUND(AVG(wst.score), 2) as average_score
from WL_SCORE_TAB wst, wl_course_tab wct, wl_student_tab ws
where wst.student_id=ws.student_id and wst.course_id=wct.course_id
group by ws.class_id,wct.course_name
order by class_id,course_id
--你的稍改下也可以的
create view SELECT_AVG_SCORE_V as Select ws.class_id, wct.course_name,case when ws.sex='M' then 'male' when ws.sex='W' then 'female' else 'class'end category,ROUND(AVG(wst.score), 2) as average_score from WL_SCORE_TAB wst, wl_course_tab wct, wl_student_tab wswhere wst.student_id=ws.student_id and wst.course_id=wct.course_id group by ws.class_id,wct.course_name,case when ws.sex='M' then 'male' when ws.sex='W' then 'female' else 'class'endorder by class_id,course_id
试试这个,应该可以用的:with t1 as(
select st.class_id, cs.course_name,
avg(decode(st.sex,'M',sc.score)) "male",
avg(decode(st.sex,'W',sc.score)) "female",
avg(sc.score) "class"
from wl_score_tab sc , wl_course_tab cs , wl_student_tab st
where sc.student_id=st.student_id and sc.course_id=cs.course_id
group by st.class_id, cs.course_name),
t2 as(
select rownum rn from dual connect by rownum<=3
)
select t1.class_id,t1.course_name,
decode(t2.rn,1,'male',2,'female',3,'class') catagory,
decode(t2.rn,1,"male",2,"female",3,"class") average_score
from t1, t2
order by class_id,course_name,catagory desc
列里分组是decode,行里分组是group by create view SELECT_AVG_SCORE_V as Select ws.class_id, wct.course_name, AVG(wst.score) average_score from WL_SCORE_TAB wst, wl_course_tab wct, wl_student_tab wswhere wst.student_id=ws.student_id and wst.course_id=wct.course_id group by grouping sets((ws.class_id,wct.course_name,ws.sex),(ws.class_id,wct.course_name))order by class_id,course_id,category asc;