找到学生ID列表。 SQL [英] find the list of student id . sql

问题描述

为此编写SQL查询。



查找注册一门课程英语或信德语但未注册两门课程的学生ID列表。



表名：课程

有两栏

courseid | cname



表名：studentcourse

有两列

studentid | courseid

解决方案

我们不喜欢做功课。如果你遇到问题，人们就会准备好帮助解决具体问题。但是，你必须表明你已经尝试过并明确指出问题所在。做和尝试自己是学习的唯一方法，我敢打赌，如果你尝试，你会成功。

尝试这个查询..

i希望这会帮助你..

 选择 [StdId]，count（distinct（[CourseId]））来自 [dbo]。[tbl_Student_Course]  group  by [StdId] 
有计数（distinct（[CourseId] ]））=  1  

另一种没有Count，Group By和Distinct的解决方案：



非常详细，我猜它比其他的慢，因为它的嵌套：

 SELECT studentId 
 FROM studentcourse S JOIN course C ON S.courseid = C.courseid 
 WHERE C.cname IN（'  English'）
 AND 
 studentId NOT IN 
（SELECT studentId FROM studentcourse S JOIN course C ON S.cou rseid = C.courseid 
 WHERE C.cname IN（'  Sindhi'））
 
 UNION 
 
 SELECT studentId 
 FROM studentcourse S JOIN course C ON S.courseid = C.courseid 
 WHERE C.cname IN（'  Sindhi'）
 AND 
 studentId NOT IN 
（SELECT studentId FROM studentcourse S JOIN课程C ON S.courseid = C.courseid 
 WHERE C.cname IN（'  English'））

write sql query for that.

find the list of student id who enrolled in one course english or Sindhi but not enrolled in both courses.

table name : course
having two columns
courseid|cname

table name : studentcourse
having two columns
studentid|courseid

解决方案

We don't do homework. If you get stuck to something, people are ready to help in specific questions. However, you have to show that you have tried yourself and clearly point out what is the problem. Doing and trying yourself is the only way to learn and I bet that if you try, you'll succeed.

Try this Query..
i hope this will help you..

select [StdId],count(distinct([CourseId]))  from [dbo].[tbl_Student_Course] group by [StdId]
 having count(distinct([CourseId]))=1

Yet another solution without Count, Group By and Distinct:

Pretty verbose and I guess slower than the other ones because of its nestings:

SELECT studentId
FROM studentcourse S JOIN course C ON S.courseid=C.courseid
WHERE C.cname IN ('English')
AND
studentId NOT IN
(SELECT studentId FROM studentcourse S JOIN course C ON S.courseid=C.courseid
WHERE C.cname IN ('Sindhi'))

UNION

SELECT studentId
FROM studentcourse S JOIN course C ON S.courseid=C.courseid
WHERE C.cname IN ('Sindhi')
AND
studentId NOT IN
(SELECT studentId FROM studentcourse S JOIN course C ON S.courseid=C.courseid
WHERE C.cname IN ('English'))

这篇关于找到学生ID列表。 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！