如何使用sql server检查学生课程系统中的时间冲突 [英] How to check time conflict in student course system using sql server

问题描述

我想使用sql server检查学生课程系统中的时间冲突，这样如果学生的课程与该学生的另一门课程发生冲突，学生就无法将课程添加到他的计划中。



i尝试下面的sql语句，它在同一个表（CouseSection）中正常工作，它给了我那个课程（系统分析）与（硬件）冲突但不是同一个学生。



i希望为同一个学生获得相同的结果（student_id）

i不知道如何使用student_id获得相同的结果



这里是sql语句

i want to check time conflict in student course system using sql server so that student can't add course to his plan if it con conflicts in time with another course for that student.

i try the following sql statments and it works correctly in the same table (CouseSection) and it gives me that course (system analysis) conflict with (hardware) But not for the same student.

i want to get the same result for the same student (student_id)
i don't know how to get the same result using student_id

here is sql statement

SELECT     
    t1.crs_sec_id, 
    t1.start_time, 
    t1.end_time, 
    t2.crs_sec_id AS overlapid
FROM         
    CourseSection AS t1 
    INNER JOIN CourseSection AS t2 
    ON t1.start_time < t2.end_time AND t1.end_time > t2.start_time 
    AND t1.date = t2.date
    AND t1.crs_sec_id <> t2.crs_sec_id
WHERE     
    (t1.Course_id = 4)

它给我这个结果：

it gives me this result:

crs_sec_id	- start_time	   -      end_time	 -       overlapid
5	       -  10:00:00.0000000 - 	12:00:00.0000000  -	1

i想要为同一个学生获得相同的结果（student_id）

i不知道如何使用student_id获得相同的结果？

我可以将（student_id）放在上一个语句中以获得相同的结果吗？



这里是我数据库中的关系

http： //www.4shared.com/photo/ice8bR9Qba/db_online.html [ ^ ]



和学生页面

HTTP：// www.4shared.com/photo/BxqqgUcCba/page.html [ ^ ]



我在sql server中的尝试

http://www.4shared.com/photo/7dJyXY0Mce/dbconflict.html [ ^ ]

i want to get the same result for the same student (student_id)
i don't know how to get the same result using student_id?
where i can put the (student_id) in the previous statement to get the same result?

here is the relationships in my database
http://www.4shared.com/photo/ice8bR9Qba/db_online.html[^]

and student page
http://www.4shared.com/photo/BxqqgUcCba/page.html[^]

my try in sql server
http://www.4shared.com/photo/7dJyXY0Mce/dbconflict.html[^]

推荐答案

试试这个...

Try this...

SELECT     
    stdc.std_id,
    cs1.crs_sec_id, 
    cs1.start_time, 
    cs1.end_time, 
    cs2.crs_sec_id AS overlapid
FROM         
    StudentCource stdc
    INNER JOIN CourseSection AS cs1
        ON stdc.crs_sec_id = cs1.crs_sec_id 
    INNER JOIN CourseSection AS cs2
        ON stdc.crs_sec_id = cs2.crs_sec_id 
        AND cs1.crs_sec_id <> cs2.crs_sec_id
        AND cs1.date = cs2.date
        (   
            cs2.start_time BETWEEN cs1.start_time AND cs1.end_time 
            OR  
            cs2.end_time BETWEEN cs1.start_time AND cs1.end_time
        )
WHERE     
    cs2.crs_sec_id IS NOT NULL

快乐编码！

：）

Happy Coding!
:)

这篇关于如何使用sql server检查学生课程系统中的时间冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！