首页 > 代码库 > 50个查询系列-第11个查询:查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;

50个查询系列-第11个查询:查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;

select distinct( tblstudent.StuId) from tblstudent ,

tblscore
where  tblscore.CourseId
 in
(
select tblscore.CourseId from tblscore where tblscore.StuId=1001 
)
and tblstudent.StuId=tblscore.StuId

  答案提供两种方法;

1:

Select DistInct st.StuId,StuName From tblStudent st
  Inner Join tblScore sc ON st.StuId=sc.StuId
   Where sc.CourseId IN (Select CourseId From tblScore Where StuId=1001)

2:

 ------嵌套子查询
 Select StuId,StuName From tblStudent
  Where StuId In
  (
   Select Distinct StuId From tblScore Where CourseId In (Select CourseId From tblScore Where StuId=1001)
  )

 

50个查询系列-第11个查询:查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;