现有这个几个表:
Student(Sno,Sname,Sage,Ssex) 学生表
Course(Cno,Cname,Tno) 课程表
SC(Sno,Cno,score) 成绩表
Teacher(Tno,Tname) 教师表要求:查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名” 这个怎么写?注意:查询出学号为‘2’的学生选的课程不止一个 (如何查出其他学生选的课程与学号为2 的学生选修的这些课完全一样)该SQL怎么写 求助一下
谢谢!
Student(Sno,Sname,Sage,Ssex) 学生表
Course(Cno,Cname,Tno) 课程表
SC(Sno,Cno,score) 成绩表
Teacher(Tno,Tname) 教师表要求:查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名” 这个怎么写?注意:查询出学号为‘2’的学生选的课程不止一个 (如何查出其他学生选的课程与学号为2 的学生选修的这些课完全一样)该SQL怎么写 求助一下
谢谢!
如:选了语文,3号同学选了 语文/英语,是否成立
以成立为例
e.g.
SELECT *
FROM Student AS S
WHERE Sno<>'2' AND NOT EXISTS ( SELECT *
FROM SC AS SC2
WHERE Sno = '2'
AND NOT EXISTS ( SELECT 1
FROM SC
WHERE Sno = S.Sno
AND Cno = SC2.Cno ) )
if not object_id(N'Tempdb..#Student') is null
drop table #Student
Go
Create table #Student([Sno] int,[Sname] nvarchar(22),[Sage] int,[Ssex] nvarchar(21))
Insert #Student
select 1,N'张三',18,N'女' union all
select 2,N'李四',19,N'男' union all
select 3,N'王五',19,N'男'
GO
if not object_id(N'Tempdb..#Course') is null
drop table #Course
Go
Create table #Course([Cno] int,[Cname] nvarchar(22),[Tno] int)
Insert #Course
select 1,N'语文',1 union all
select 2,N'数学',1 union all
select 3,N'英语',1
GO
if not object_id(N'Tempdb..#SC') is null
drop table #SC
Go
Create table #SC([Sno] int,[Cno] int,[score] int)
Insert #SC
select 1,1,90 union all
select 1,2,85 union all
select 2,2,95 union all
select 2,3,85 union all
select 2,4,75 union all
select 3,2,90 union all
select 3,3,80 union all
select 3,4,70
Go
--测试数据结束
SELECT *
FROM #Student
WHERE Sno IN ( SELECT #SC.Sno
FROM #SC
JOIN ( SELECT *
FROM #SC
WHERE Sno = 2
) t ON t.Cno = #SC.Cno
AND t.Sno <> #SC.Sno
GROUP BY #SC.Sno
HAVING COUNT(1) = ( SELECT COUNT(1)
FROM #SC
WHERE Sno = 2
) )
select stu.sno,stu.sname from student stu join (select sno from (select sno,wm_concat(cno) subject from sc group by sno) k where subject=(select subject from (select sno,wm_concat(cno) subject from sc group by sno) where sno='s001') and sno<>'s001') g on g.sno=stu.sno;
A.Sno
,A.Sname
FROM
tb_Student as A
JOIN tb_SC as B on A.Sno=B.Sno
JOIN tb_Course as C on C.Cno=B.Cno
WHERE
C.Co=
(SELECT C.Co
FROM tb_Student as A
JOIN tb_SC as B on A.Sno=B.Sno
JOIN tb_Course as C on C.Cno=B.Cno
WHERE A.Sno='2')
你自己运行时也可把第7、8行的删去因为重复了,我没删,怕你不好理解,但删去使代码更简洁
希望对你有帮助