HOW TO write a Select statement (100分)

  • 主题发起人 主题发起人 hpretty
  • 开始时间 开始时间
H

hpretty

Unregistered / Unconfirmed
GUEST, unregistred user!
now if I have three talbe defined like so

table 1
subjectid int
name varchar(8)

table 2
studentno int,
subject subjectid,
mark int ,
term char

table 3
studentno int
class int

give you a param class I want display the data like so
studentno subject1 subject2,subject3,...
subject1,2,3 defined as subjectid

if you do not under stand me,give a Email,I'l give you full statment
 
你是不是想得到学号为studentno的学生学的课程?
不太明白你的问题。
 
表达清楚一点!
 
select C.Studentno,C.Class,B.Mark,B.Term,A.name
from table1 A,table2 B,table3 C
where C.Studentno=B.Studentno
and B.subject=C.subjectid
 
I wonder what kind of database you use,if oracle
it would look like this( suppose you want display "mark"):

select studentno,sum(decode(subjectid,subjectid1,mark,0)),
sum(decode(subjectid,subjectid2,mark,0)),
sum(decode(subjectid,subjectid3,mark,0)),...
from table2 group by studentno

that's all.
 
我的意思是说,我把学生信息放在学生表中(有班级一列),课程号放在课程表中,
成绩信息包括学号、学期、课程号、分数,
现在我要查询的是,给你一个班号、学期,要把该班所有的学生的成绩(包括
所有的课程)查询出来,就这个意思,谁能?,告诉我一声!
 
如果课程数目固定,可用feizai的方法,
加条件studentid in (select studentid from table2 where class='cls')
and term='term'
如不固定,用动态SQL从课程表table1取数拼sql语句,同上
 
如果某个学生有subject1,subject2,subject4
另一个有subject3,subject5,subject10
你希望怎麽处理?
 
多人接受答案了。
 
后退
顶部