gpt4 book ai didi

sql - 改进我的 SQL Select 语句以选择尚未完全完成某个部分的学生

转载 作者:行者123 更新时间:2023-12-03 02:29:49 25 4
gpt4 key购买 nike

我的 SQL 技能非常有限。我在一所技术学院计算机科学专业二年级。我正在构建一个 Windows 窗体应用程序,该应用程序将允许我学院的 BAS 主任跟踪学生及其在整个类(class)中的进度。我对数据库设计有完全的控制权,所以如果你想出一种方法来帮助我找到一个涉及调整数据库的解决方案,这是可能的。

我正在尝试选择所有类(class)EnrollmentStatus 不为 3 的学生 1CreditSection。有 12 门类(class)的 CreditSection1

我使用的表格如下所示: Schema

我可以想出几种方法来用语音表达我的解决方案,但似乎无法编写 SQL:

SELECT * FROM Students WHERE each student has 12 entries in CourseEnrollment AND
CourseEnrollment.EnrollmentStatus = 3 AND Courses.CreditSection = 1

SELECT * FROM Students WHERE Courses.CourseID 1 thru 12 EXIST in
CourseEnrollment for each student AND CourseEnrollment.EnrollmentStatus = 3

我可以使用下面这个困惑的内容找到所需的解决方案,但是当我检查已完成 4 年类(class)的学生时......这个查询变得非常长,并且可能会消耗大量资源。

此查询选择不在已完成一门或多门给定类(class)的学生列表中的学生:

SELECT DISTINCT s.* FROM Students s
WHERE s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 1 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 2 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 3 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 4 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 5 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 6 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 7 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 8 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 9 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 10 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 11 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 12 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 13 AND ce.EnrollmentStatus = 3)

我的目标是弄清楚如何用 SQL 编写此查询,然后将其转换为 LINQ,这正是我最终需要的。如果有人可以帮助解决这一部分,我将不胜感激。

我已将上面的内容转换为 LINQ,它看起来同样可怕:

var query =
from student in datStudents.Students.AsEnumerable<dsStudentManager.StudentsRow>()
where !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 1 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
!(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 2 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
!(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 3 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
!(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 4 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
!(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 5 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
!(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 6 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
!(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 7 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
!(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 8 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
!(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 9 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
!(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 10 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
!(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 11 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
!(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 12 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
!(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 13 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID)
select new
{
id = student.StudentID,
rtcid = student.RTCStudentID,
firstname = student.FirstName,
lastname = student.LastName,
phone = student.Phone,
studentemail = student.StudentEmail,
personalemail = student.PersonalEmail,
address = student.Address,
city = student.City,
state = student.State,
zip = student.Zip,
birthdate = student.BirthDate,
gender = student.Gender,
notes = student.Notes,
studentdocumentslocation = student.StudentDocumentsLocation
};

最佳答案

使用带有 group by 和having 语句的子查询,您可以得到与此类似的结果:

 SELECT * FROM Students WHERE StudentID NOT IN (
SELECT s.StudentID FROM Students s
JOIN CourseEnrollment ce ON s.StudentID = ce.StudentID
JOIN Courses c ON ce.CourseID = c.CourseID
WHERE ce.EnrollmentStatus = 3 AND c.CreditSection = 1
GROUP BY s.StudentID
HAVING COUNT(*) = 12
)

内部查询构建学生返回的条件,“HAVING COUNT(*) = 12”会为您提供匹配 12 门类(class)的学生。如果您只学习部分类(class),您也可以尝试以下方法。

 SELECT * FROM Students WHERE StudentID NOT IN (
SELECT s.StudentID FROM Students s
JOIN CourseEnrollment ce ON s.StudentID = ce.StudentID
JOIN Courses c ON ce.CourseID = c.CourseID
WHERE ce.EnrollmentStatus = 3 AND c.CreditSection = 1
AND c.CourseID IN (1,2,3,4,5,6,7,8)
GROUP BY s.StudentID
HAVING COUNT(*) = 8 -- Number of courses in the ID in clause
)

希望这可以帮助您走上正轨。

关于sql - 改进我的 SQL Select 语句以选择尚未完全完成某个部分的学生,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/30903229/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com