SQLServer服务器中,给定表StudentExercise,其中字段为StudentCode(nvarchar), ExerciseID(int),Scores(int), ExerciseContent(string),写一条SQL语句列出做了3次以上作业的学生,列出学生代码就可以。 接上一题,SQLServer服务器中,给定表StudentInfo,其字段StudentCode(nvarchar),StudentName(nvarchar),写出一条语句查出对于所有学生号码为10的学生作业分数记录情况,对于没有做作业的学生显示0分,要求显示字段为StudentCode,StudentExercise,Scores.
SQLServer服务器中,给定表StudentExercise,其中字段为StudentCode(nvarchar), ExerciseID(int),Scores(int), ExerciseContent(string),写一条SQL语句列出做了3次以上作业的学生,列出学生代码就可以。 接上一题,SQLServer服务器中,给定表StudentInfo,其字段StudentCode(nvarchar),StudentName(nvarchar),写出一条语句查出对于所有学生号码为10的学生作业分数记录情况,对于没有做作业的学生显示0分,要求显示字段为StudentCode,StudentExercise,Scores.
学生号码為StudentCode ?
StudentExercise写错了 是StudentName
b.*,isnull(a.score,0) as score
from
StudentInfo a left join StudentExercise b
on
a.studentcode=b.studentcode
where
a.StudentCode='10'
StudentExercise
group by StudentCode
having count(1)>=3
from StudentInfo a
left join StudentExercise b
on a.StudentCode = b.StudentCode
where a.StudentCode = '10'
select
a.*,isnull(b.score,0) as score
from
StudentInfo a left join StudentExercise b
on
a.studentcode=b.studentcode
where
a.StudentCode='10'---修改一下
SELECT b.StudentCode,b.StudentName
FROM StudentExercise AS a
INNER JOIN StudentInfo AS b ON a.StudentCode=b.StudentCode
GROUP BY b.StudentCode,b.StudentName
HAVING COUNT(1)>1
SELECT
b.StudentCode,b.StudentName,ISNULL(a.Scores,0)
FROM StudentExercise AS a
right JOIN StudentInfo AS b ON a.StudentCode=b.StudentCode
on a.ExerciseID=10
猜一個