有这样一张表
STU-SCORE
Name varchar
Subject varchar(VALUES LIKE math, chinese, english)
Score number统计学生的考试成绩。
不是每个学生都参加了所有科目的考试,现在要得到bob, alice, peter的math, chinese, english考试成绩。如果没成绩,自动给0分。能否用一条SQL语句得到结果。谢谢
STU-SCORE
Name varchar
Subject varchar(VALUES LIKE math, chinese, english)
Score number统计学生的考试成绩。
不是每个学生都参加了所有科目的考试,现在要得到bob, alice, peter的math, chinese, english考试成绩。如果没成绩,自动给0分。能否用一条SQL语句得到结果。谢谢
select a.name,b.project,b.score from
student a,stu_score b where a.name=b.name(+)) c,project d where
c.project(+) = d.project大致是这样 自己修改下
FROM (SELECT a.NAME, b.subject
FROM (SELECT 'bob' NAME
FROM DUAL
UNION
SELECT 'alice' NAME
FROM DUAL
UNION
SELECT 'peter' NAME
FROM DUAL) a,
(SELECT 'math' subject
FROM DUAL
UNION
SELECT 'chinese' subject
FROM DUAL
UNION
SELECT 'english' subject
FROM DUAL) b) ns LEFT JOIN "STU-SCORE" ss ON ( ns.NAME =
ss.NAME
AND ns.subject =
ss.subject
)
但是实际上,在数据库设计的时候应该有student,subject两个配置表,分别保存学生和科目信息那么就应该是这样的:SELECT ns.NAME, ns.subject, NVL (ss.score, 0) score
FROM (SELECT a.NAME, b.subject
FROM studnet a, subject b) ns LEFT JOIN "STU-SCORE" ss ON ( ns.NAME =
ss.NAME
AND ns.subject =
ss.subject
)