比如现在有三张表:
表1是学生信息表(student),有字段id,name
-----------------
id | name
-----------------
1 | 张三
-----------------
表2是科目表(course),有字段id,name
-----------------
id | name
-----------------
1 | 语文
-----------------
2 | 数学
-----------------
3 | 英语
-----------------
表3是成绩表(score),有字段id, student_id, course_id, score
-------------------------------------------------
id | student_id | course_id | score
-------------------------------------------------
1 | 1 | 1 | 70
-------------------------------------------------
2 | 1 | 2 | 80
-------------------------------------------------
3 | 1 | 3 | 81
--------------------------------------------------现在需求是用SQL语句输出结果集的形式是:
-------------------------------------------------
student_name | 语文 | 数学 | 英语
-------------------------------------------------
张三 | 70 | 80 | 81
-------------------------------------------------求大神指教....
表1是学生信息表(student),有字段id,name
-----------------
id | name
-----------------
1 | 张三
-----------------
表2是科目表(course),有字段id,name
-----------------
id | name
-----------------
1 | 语文
-----------------
2 | 数学
-----------------
3 | 英语
-----------------
表3是成绩表(score),有字段id, student_id, course_id, score
-------------------------------------------------
id | student_id | course_id | score
-------------------------------------------------
1 | 1 | 1 | 70
-------------------------------------------------
2 | 1 | 2 | 80
-------------------------------------------------
3 | 1 | 3 | 81
--------------------------------------------------现在需求是用SQL语句输出结果集的形式是:
-------------------------------------------------
student_name | 语文 | 数学 | 英语
-------------------------------------------------
张三 | 70 | 80 | 81
-------------------------------------------------求大神指教....
score as ( select 1 id, 1 student_id, 1 course_id, 70 score from dual
union all
select 1 id, 1 student_id, 2 course_id, 80 score from dual
union all
select 1 id, 1 student_id, 3 course_id, 81 score from dual )
select b.name student_name, sum(decode(a.course_id, 1, score)) 语文,
sum(decode(a.course_id, 2, score)) 数学,
sum(decode(a.course_id, 3, score)) 英语
from score a, student b
where a.student_id = b.id
group by b.name;