有一张成绩表,表结构和数据如下:
姓名:name | 课程: course | 成绩:score |
张三 | 语文 | 89 |
张三 | 数学 | 90 |
张三 | 英语 | 78 |
李四 | 语文 | 97 |
李四 | 数学 | 69 |
李四 | 英语 | 79 |
王五 | 英语 | 89 |
问:如何设计sql,查询到如下结果:
姓名:name | 语文 | 数学 | 英语 |
张三 | 89 | 90 | 78 |
李四 | 97 | 69 | 79 |
王五 | 0 | 0 | 89 |
建表sql如下:
create table tbl( insert into tbl(name,course,score) values('王五','英语','89'); |
这是一个典型的行变列的问题,
1、固定列数的行变列
根据“姓名”可以将源表中的若干记录合并成一条记录,那么就用“姓名”字段分组,再统计每一组中课程的成绩。
select name, |
decode函数相当于条件语句(if),可以将输入数值与函数中的参数列表相比较,根据输入值返回一个对应值。
根据course字段的值和“语文”匹配,如果相同则返回course字段的值,如果不相同或者为空则返回0。
因为使用了分组,所以要使用sum函数。
也可以使用case……when……then语句。
select name, |
2、不固定列数的行变列
如果课程是不固定的,那么就拼不出上面的sql,这种情况比较复杂,在Oracle中我们引入pl/sql编写存储过程来解决这个问题。
大概思路是,在packages中声明一个游标类型,用于在存储过程中返回游标类型的记录;在存储过程中查询考试科目,动态拼sql;用sql声明游标返回。
--packages中声明一个游标类型 |
create or replace procedure myprocedure (c out pkg_getrecord.myrctype )as |
3、将多列记录合并成一列(本题目中没有要求,再介绍一个oracle10G新加入的函数)
select name, count(*), wm_concat(score) |
wmsys.wm_concat是oracle10G新加入的函数,其功能是将多行转换成一合并列,并使用逗号分隔。查询结果如下:
name | count(*) | wm_concat(score) |
张三 | 3 | 97,69,79 |
李四 | 1 | 89 |
王五 | 3 | 89,90,78 |
www.javakc.com java软件工程师培训 就业率100%
java技术交流群 48554977