如题:
student_course
NAME COURSE MARK
'张三' ‘Java’ 100
‘张三’ ‘Jdbc’ 100
'张三' ‘Hibernate’ 20
'李四' ‘Java’ 80
‘李四’ ‘Jdbc’ 80
'李四' ‘Hibernate’ 80
'王五' ‘Java’ 60
‘王五’ ‘Jdbc’ 60
'王五' ‘Hibernate’ 60
1.按要求写一条SQL语句显示效果为
NAME JAVA JDBC HIBERNATE
'张三' 100 100 20
‘李四’ 80 80 80
‘王五’ 60 60 60
听别人说有两种方法,一种是使用窗口函数,另一种不用使用窗口函数,请把两种都写上,并做一下解释,新手,有可能看不懂,谢谢了!!
student_course
NAME COURSE MARK
'张三' ‘Java’ 100
‘张三’ ‘Jdbc’ 100
'张三' ‘Hibernate’ 20
'李四' ‘Java’ 80
‘李四’ ‘Jdbc’ 80
'李四' ‘Hibernate’ 80
'王五' ‘Java’ 60
‘王五’ ‘Jdbc’ 60
'王五' ‘Hibernate’ 60
1.按要求写一条SQL语句显示效果为
NAME JAVA JDBC HIBERNATE
'张三' 100 100 20
‘李四’ 80 80 80
‘王五’ 60 60 60
听别人说有两种方法,一种是使用窗口函数,另一种不用使用窗口函数,请把两种都写上,并做一下解释,新手,有可能看不懂,谢谢了!!
SUM(CASE T.COURSE WHEN 'Java' THEN T.MARK ELSE 0) JAVA,
SUM(CASE T.COURSE WHEN 'Jdbc' THEN T.MARK ELSE 0) JDBC,
SUM(CASE T.COURSE WHEN 'Hibernate' THEN T.MARK ELSE 0) HIBERNATE
FROM STUDENT_COURSE T
GROUP BY T.NAME
SELECT T.NAME,
SUM(CASE T.COURSE
WHEN 'Java' THEN
T.MARK
ELSE
0
END) JAVA,
SUM(CASE T.COURSE
WHEN 'Jdbc' THEN
T.MARK
ELSE
0
END) JDBC,
SUM(CASE T.COURSE
WHEN 'Hibernate' THEN
T.MARK
ELSE
0
END) HIBERNATE
FROM STUDENT_COURSE T
GROUP BY T.NAME
2. moving averages within a specified range of rows,
3. a range of values, or
4. an interval of time.Window function can work with: SUM(), AVG(), MAX(), MIN(), COUNT(), VARIANCE(), and STDDEV().Window function can also work with FIRST_VALUE() and LAST_VALUE(), which return the first and last values in a window.
窗口函数在此无用武之地。
SUM(CASE T.COURSE
WHEN 'Java' THEN
T.MARK
ELSE
0
END) JAVA,
SUM(CASE T.COURSE
WHEN 'Jdbc' THEN
T.MARK
ELSE
0
END) JDBC,
SUM(CASE T.COURSE
WHEN 'Hibernate' THEN
T.MARK
ELSE
0
END) HIBERNATE
FROM STUDENT_COURSE T
GROUP BY T.NAME楼主试一下吧!
select
name ,
sum(decode(COURSE,'Java',MARK)) "JAVA",
sum(decode(COURSE,'Jdbc',MARK)) "JDBC",
sum(decode(COURSE,'Hibernate',MARK)) "HIBERNATE"
from
STUDENT_COURSE
group by NAME
SUM(decode(COURSE ,'Java',MARK)) JAVA,
SUM(decode(COURSE ,'JDBC',MARK)) JDBC,
SUM(decode(COURSE ,'HIBERNATE ',MARK)) HIBERNATE
FROM STUDENT_COURSE
GROUP BY NAME
sum(case course when 'JAVA' then else 0) java
sum(case course when 'JDBC' then else 0) JDBC
sum(case course when 'HIBERNATE' then else 0) HIBERNATE
from
student_course
group by name
(
select *
from (
select '张三' NAME ,'Java' COURSE ,'100' MARK from dual union all
select '张三' NAME ,'Jdbc' COURSE ,'100' MARK from dual union all
select '张三' NAME ,'Hibernate' COURSE ,'20' MARK from dual union all
select '李四' NAME ,'Java' COURSE ,'80' MARK from dual union all
select '李四' NAME ,'Jdbc' COURSE ,'80' MARK from dual union all
select '李四' NAME ,'Hibernate' COURSE ,'80' MARK from dual union all
select '王五' NAME ,'Java' COURSE ,'60' MARK from dual union all
select '王五' NAME ,'Jdbc' COURSE ,'60' MARK from dual union all
select '王五' NAME ,'Hibernate' COURSE ,'60' MARK from dual
)
)
select *
from sview
pivot(max(MARK) for COURSE in ('Java' as Java, 'Jdbc' as Jdbc, 'Hibernate' as Hibernate));参考http://www.oracle.com/technology/global/cn/pub/articles/oracle-database-11g-top-features/11g-pivot.html
请问decode(COURSE,'Java',MARK)这句话是什么意思喔!
select name,sum(decode(course,'Java',,0)) as Java,
sum(decode(course,'Jdbc',,0)) as Jdbc,
sum(decode(course,'Hibernate',,0)) as Hibernate
from student_course group by name;•含义解释:
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
该函数的含义如下:
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF