表: student(StrudentID为主键)
StrudentID name
101 小王
102 小张
103 小红
104 李四
105 王五表subject (subjectID为主键)
subjectID kemu
1 数学
2 语文
3 外语
4 体育
5 音乐表 grade(StrudentID、subjectID为主键)
StrudentID grade subjectID
101 60 2
101 85 1
102 80 3
103 46 1
104 50 3
104 85 4
105 90 2 用SQL语句实现下列要求:
1.列出每个同学成绩详细目录,得到结果要求如下:
学号 姓名 科目 成绩101 小王 语文 60
101 小王 数学 85
102 小张 外语 80
103 小红 数学 46
104 李四 外语 50
104 李四 体育 85
105 王五 语文 902。找出参加多门考试的同学的学号和姓名,得到结果要求如下:
101 小王
104 李四 3 。列出单科成绩最大的学生和成绩 ,结果要求如下:101 小王 数学 85
102 小张 外语 80
104 李四 体育 85
105 王五 语文 90
4.列出每个同学成绩详细目录,成绩用及格和不及格来显示,得到结果要求如下:
101 小王 语文 及格
101 小王 数学 及格
102 小张 外语 及格
103 小红 数学 不及格
104 李四 外语 不及格
104 李四 体育 及格
105 王五 语文 及格
StrudentID name
101 小王
102 小张
103 小红
104 李四
105 王五表subject (subjectID为主键)
subjectID kemu
1 数学
2 语文
3 外语
4 体育
5 音乐表 grade(StrudentID、subjectID为主键)
StrudentID grade subjectID
101 60 2
101 85 1
102 80 3
103 46 1
104 50 3
104 85 4
105 90 2 用SQL语句实现下列要求:
1.列出每个同学成绩详细目录,得到结果要求如下:
学号 姓名 科目 成绩101 小王 语文 60
101 小王 数学 85
102 小张 外语 80
103 小红 数学 46
104 李四 外语 50
104 李四 体育 85
105 王五 语文 902。找出参加多门考试的同学的学号和姓名,得到结果要求如下:
101 小王
104 李四 3 。列出单科成绩最大的学生和成绩 ,结果要求如下:101 小王 数学 85
102 小张 外语 80
104 李四 体育 85
105 王五 语文 90
4.列出每个同学成绩详细目录,成绩用及格和不及格来显示,得到结果要求如下:
101 小王 语文 及格
101 小王 数学 及格
102 小张 外语 及格
103 小红 数学 不及格
104 李四 外语 不及格
104 李四 体育 及格
105 王五 语文 及格
select sutdent.studentID, student.name, subject.kemu, grade.grade from student, subject, grade
where
student.studentID = subject.studentID
and subject.subjectID = grade.subjectID
and student.studentID = grade.studentID
SELECT a.StudentID,a.Name,b.kemu,g.grade
FROM grade g
INNER JOIN student a
ON a.studentid=g.studentid
INNER JOIN subject b
ON b.subjectid=g.subjectid
2SELECT a.* FROM student a
INNER JOIN
(SELECT Studentid FROM grade GROUP BY StudentID HAVING COUNT(1)>1) b
ON a.studentid=b.studentid3
SELECT a.*,b.kemu,mg FROM grade x
INNER JOIN (SELECT subjectid,MAX(grade) mg FROM grade group by subjectid) y
ON x.subjectid=y.subjectid AND x.grade=y.mg
INNER JOIN student a
ON a.studentid=x.studentid
INNER JOIN subject b
ON b.subjectid=x.subjectid4SELECT a.StudentID,a.Name,b.kemu,
CASE WHEN g.grade>=60 THEN '级格' ELSE '不及格' END
FROM grade g
INNER JOIN student a
ON a.studentid=g.studentid
INNER JOIN subject b
ON b.subjectid=g.subjectid
随手写的,未测试,写的我有点晕,几个表连,可能有手误.
Declare @Student Table(StudentID Int,Name Varchar(10))
Insert @Student Select 101,'小王'
Union All Select 102,'小张'
Union All Select 103,'小红'
Union All Select 104,'李四'
Union All Select 105,'王五'
---------------------------
Declare @Subject Table(SubjectId Int,KeMu Varchar(10))
Insert @Subject Select 1,'数学'
Union All Select 2,'语文'
Union All Select 3,'外语'
Union All Select 4,'体育'
Union All Select 5,'音乐'
---------------------------
Declare @Grade Table(StudentID Int,Grade Int,SubjectID Int)
Insert @Grade Select 101,60,2
Union ALl Select 101,85,1
Union ALl Select 102,80,3
Union ALl Select 103,46,1
Union ALl Select 104,50,3
Union ALl Select 104,85,4
Union ALl Select 105,90,2
--1.
Select A.StudentID,A.Name,C.KeMu,B.Grade
From @Student A Left Join @Grade B
On A.StudentID=B.StudentID
Inner Join @Subject C
On B.Subjectid=C.Subjectid
Order by A.StudentID
--2.Select A.StudentID,B.Name
From @Grade A Inner Join @student B
On A.Studentid=B.studentid
Group By A.StudentID,B.Name
Having Count(A.SubjectID)>1
--3.
Select A.StudentID ,A.Grade,B.Name,C.KeMu
From @Grade A Inner Join @Student B On A.StudentID=B.StudentID
Inner Join @Subject C
On A.SubJectId=C.SubjectID
Where A.StudentID In (Select Top 1 StudentID From @Grade Where SubjectID=A.SubjectID Order By Grade Desc )
--4.
Select A.StudentID,A.Name,C.KeMu,
Case when B.Grade>60 then '及格' Else '不及格' End As Grade
From @Student A Left Join @Grade B
On A.StudentID=B.StudentID
Inner Join @Subject C
On B.Subjectid=C.Subjectid
Order by A.StudentID
From Student A ,Grade B ,Subject C
Where A.StudentID=B.StudentID and B.Subjectid=C.Subjectid
Order by A.StudentID2.Select A.StudentID,Name From Grade A,student B
Where A.Studentid=B.studentid
Group By A.StudentID,B.Name Having Count(A.StudentID)>1
From Grade A,Student B,Subject C
Where A.StudentID=B.StudentID and A.SubJectId=C.SubjectID
and A.StudentID In (Select Top 1 StudentID From Grade Where SubjectID=A.SubjectID Order By Grade Desc )
4.Select A.StudentID,A.Name,C.KeMu,
Case when B.Grade>60 then '及格' Else '不及格' End As Grade
From Student A ,Grade B,Subject C
Where A.StudentID=B.StudentID and B.Subjectid=C.Subjectid
Order by A.StudentID
Case when B.Grade>=60 then '及格' Else '不及格' End As Grade
From Student A ,Grade B,Subject C
Where A.StudentID=B.StudentID and B.Subjectid=C.Subjectid
Order by A.StudentID
第三题的SQL有一个问题,就是如果最高分的同学如果有多个的话,无法一一列出来。
create table student(studentid int primary key , name varchar(20))
insert into student
select 101, '小王' union
select 102, '小张' union
select 103, '小红' union
select 104, '李四' union
select 105, '王五' create table subject (subjectid int primary key, kemu varchar(20))
insert into subject
select 1 , '数学' union
select 2 , '语文' union
select 3 , '外语' union
select 4 , '体育' union
select 5 , '音乐'
create table grade(studentid int , grade int , subjectid int , primary key ( studentid , subjectid) )
insert into grade
select 101 , 60 , 2 union
select 101 , 85 , 1 union
select 102 , 80 , 3 union
select 103 , 46 , 1 union
select 104 , 50 , 3 union
select 104 , 85 , 4 union
select 105 , 90 , 2 select * from student
select * from subject
select * from grade1 .select c.studentid as '学号',
a.name as '姓名',
b.kemu as '科目',
c.grade as '成绩'
from grade c
inner join subject b on c.subjectid = b.subjectid
inner join student a on c.studentid = a.studentid
order by c.studentid2.
select *
from student
where studentid in
(
select studentid
from grade
group by studentid
having count(1) >= 2 -- 多门
)
3 . select c.studentid as '学号',
a.name as '姓名',
b.kemu as '科目',
c.grade as '成绩'
from (
select * from grade d
where not exists
(
select 1 from grade
where d.subjectid = subjectid
and d.grade < grade
)
) c
inner join subject b on c.subjectid = b.subjectid
inner join student a on c.studentid = a.studentid
order by c.studentid4.select c.studentid as '学号',
a.name as '姓名',
b.kemu as '科目',
case when c.grade >= 60 then '及格'
when c.grade < 60 then '不及格'
end as '成绩'
from grade c
inner join subject b on c.subjectid = b.subjectid
inner join student a on c.studentid = a.studentid
order by c.studentiddrop table grade
drop table subject
drop table student
Insert Student Select 101,'小王'
Union All Select 102,'小张'
Union All Select 103,'小红'
Union All Select 104,'李四'
Union All Select 105,'王五'
---------------------------
CREATE TABLE Subject (SubjectId Int,KeMu Varchar(10))
Insert Subject Select 1,'数学'
Union All Select 2,'语文'
Union All Select 3,'外语'
Union All Select 4,'体育'
Union All Select 5,'音乐'
---------------------------
CREATE TABLE Grade (StudentID Int,Grade Int,SubjectID Int)
Insert Grade Select 101,60,2
Union ALl Select 101,85,1
Union ALl Select 102,80,3
Union ALl Select 103,46,1
Union ALl Select 104,50,3
Union ALl Select 104,85,4
Union ALl Select 105,90,2--1
SELECT a.StudentID,a.Name,b.kemu,g.grade
FROM grade g
INNER JOIN student a
ON a.studentid=g.studentid
INNER JOIN subject b
ON b.subjectid=g.subjectid
--2SELECT a.* FROM student a
INNER JOIN
(SELECT Studentid FROM grade GROUP BY StudentID HAVING COUNT(1)>1) b
ON a.studentid=b.studentid--3
SELECT a.*,b.kemu,mg FROM grade x
INNER JOIN (SELECT subjectid,MAX(grade) mg FROM grade group by subjectid) y
ON x.subjectid=y.subjectid AND x.grade=y.mg
INNER JOIN student a
ON a.studentid=x.studentid
INNER JOIN subject b
ON b.subjectid=x.subjectid--4SELECT a.StudentID,a.Name,b.kemu,
CASE WHEN g.grade>=60 THEN '级格' ELSE '不及格' END
FROM grade g
INNER JOIN student a
ON a.studentid=g.studentid
INNER JOIN subject b
ON b.subjectid=g.subjectid
DROP TABLE Student
DROP TABLE Subject
DROP TABLE Grade
select student.studentID as 学号,name as 姓名 ,kemu as 科目 ,grade as 分数
from grade , student ,subject
where student.studentID=grade.studentID
and subject.subjectID=grade.subjectID
order by student.studentID,grade
2~4没有一点问题啊,不知道楼主的意思是?……
create table student(
StrudentID int,
Name nvarchar(50)
)
insert into student select 101, '小王'
union all select 102, '小张'
union all select 103, '小红'
union all select 104, '李四'
union all select 105, '王五'
select * from student
--
create table subject(
subjectID int,
kemu nvarchar(10)
)
insert into subject select 1, '数学'
union all select 2, '语文'
union all select 3, '外语'
union all select 4, '体育'
union all select 5, '音乐'
select * from subject
--
create table grade(
StrudentID int,
grade int,
subjectID int
)
insert into grade select 101, 60, 2
union all select 101, 85, 1
union all select 102, 80, 3
union all select 103, 46, 1
union all select 104, 50, 3
union all select 104, 85, 4
union all select 105, 90, 2
select * from grade
--
--1
select grade.StrudentID as 学号,
student.Name as 姓名,
subject.kemu as 科目,
grade.grade as 成绩
from grade
left join student on grade.StrudentID=student.StrudentID
left join subject on grade.subjectID=subject.subjectID
--2
select student.StrudentID, student.Name
from student
where student.StrudentID in(
select StrudentID from grade group by StrudentID
having count(StrudentID)>1)
select grade.StrudentID as 学号,
student.Name as 姓名,
subject.kemu as 科目,
grade.grade as 成绩
from grade
left join student on grade.StrudentID=student.StrudentID
left join subject on grade.subjectID=subject.subjectID,
(select max(grade) AS grade, subjectID
from grade group by subjectID) as tmp_data
where grade.grade=tmp_data.grade and grade.subjectID=tmp_data.subjectID
order by grade.StrudentID
select grade.StrudentID as 学号,
student.Name as 姓名,
subject.kemu as 科目,
case when grade.grade>=60 then '及格' else '不及格' end as 等级
from grade
left join student on grade.StrudentID=student.StrudentID
left join subject on grade.subjectID=subject.subjectID