我正在制作一个系统,该系统使用 SQL Server Management Studio 显示学生时间表并将其链接到 Visual Studio 程序中(这与我的问题无关,但只是添加上下文),但是我现在的数据库表遇到了一个重大问题,我需要一些帮助。
我项目的基本背景是有3张表,Lesson
、Student
和Subject
。这些表填充了小型测试样本数据集。
Lesson
表:
LessonID DayOfWk PeriodValue SubjectID Room blockLessonIsIn
-------------------------------------------------------------------
1 Mon 2 1 G8 2
2 Mon 3 1 G8 2
3 Mon 4 1 G8 2
4 Mon 5 2 N5 1
5 Tue 1 3 SF5 4
6 Tue 3 2 N7 1
7 Wed 1 3 SF5 4
8 Wed 2 1 H9B 2
9 Wed 5 1 G8 2
10 Thu 1 3 SF4 4
11 Thu 3 2 N7 1
12 Thu 5 3 SF5 4
13 Fri 1 3 SF5 4
14 Fri 2 1 G8 2
15 Fri 3 1 H9B 2
16 Fri 4 2 SP2 1
17 Mon 1 5 H1 1
18 Tue 5 5 H1 1
19 Thu 3 5 H1 1
20 Fri 4 5 H1 1
21 Wed 4 4 S1 3
22 Fri 5 4 S1 3
23 Tue 1 2 N1 4
24 Tue 2 2 N1 4
25 Wed 1 2 N1 4
26 Thu 1 2 N1 4
27 Thu 4 2 N1 4
Subject
表:
SubjectID Title
---------------------------------
1 Computing
2 Maths
3 Economics
4 Physics
5 Geography
Student
表:
UserID Forname Surname SchlYear InOrOut Block1 Block2 Block3 Block4 Pword
----------------------------------------------------------------------------------
1 Jake Richardson 13 1 2 1 NULL 3 password
2 Russell Penn 13 1 5 1 NULL 2 russpass
3 Xander Sheppard 13 1 2 1 4 NULL xander
4 Dan Bostock 13 1 2 1 4 NULL pass
这些表通过以下方式相互链接:
Lesson
表 -通过SubjectID
作为外键>Subject
表Subject
表 - 通过Block1
、Block2
、Block3
和Block4
>Student
表,每个表SubjectID
作为外键,每个块与一个主题相关,每个学生最多有 4 个可能的科目
我的目的是查询表,以便在查询运行具有userID
输入个人的日期和时间值时可以找到一个奇异值,从而找到个人在该日期和时间的课程。
我目前的查询如下,如果试图找到例如杰克理查森在星期二第 1 期的课程:
SELECT
lesson.SubjectID
FROM
lesson_tbl AS lesson
LEFT OUTER JOIN
student_tbl AS subject1 ON lesson.SubjectID = subject1.Block1
LEFT OUTER JOIN
student_tbl AS subject2 ON lesson.SubjectID = subject2.Block2
LEFT OUTER JOIN
student_tbl AS subject3 ON lesson.SubjectID = subject3.Block3
LEFT OUTER JOIN
student_tbl AS subject4 ON lesson.SubjectID = subject4.Block4
WHERE
lesson.DayOfWk = 'Mon'
AND lesson.PeriodValue = '5'
AND ((subject1.UserID = '1' AND lesson.blockLessonIsIn = '1') OR
(subject2.UserID = '1' AND lesson.blockLessonIsIn = '2') OR
(subject3.UserID = '1' AND lesson.blockLessonIsIn = '3') OR
(subject4.UserID = '1' AND lesson.blockLessonIsIn = '4'));
这将成功返回值 2,即数学。
但是,例如,如果我们在星期二周期 1 中搜索 Russell Penn 的值:
SELECT
lesson.SubjectID
FROM
lesson_tbl AS lesson
LEFT OUTER JOIN
student_tbl AS subject1 ON lesson.SubjectID = subject1.Block1
LEFT OUTER JOIN
student_tbl AS subject2 ON lesson.SubjectID = subject2.Block2
LEFT OUTER JOIN
student_tbl AS subject3 ON lesson.SubjectID = subject3.Block3
LEFT OUTER JOIN
student_tbl AS subject4 ON lesson.SubjectID = subject4.Block4
WHERE
lesson.DayOfWk = 'Tue'
AND lesson.PeriodValue = '1'
AND ((subject1.UserID = '2' AND lesson.blockLessonIsIn = '1') OR
(subject2.UserID = '2' AND lesson.blockLessonIsIn = '2') OR
(subject3.UserID = '2' AND lesson.blockLessonIsIn = '3') OR
(subject4.UserID = '2' AND lesson.blockLessonIsIn = '4'));
然后返回值是 3 个 2 个值,即 3 个数学值,而它应该只返回 1 个数学值,因为在我添加的阻塞约束内,星期二在第 1 周期只有 1 个数学值。
我知道数学跨越了块 1 和块 4,但这就是想法,块 LessonIsIn 旨在纠正这样一个事实,即它应该只选择与数学所在的特定块相关的课程。
我的数据库设计是否存在严重错误,还是我愚蠢地错过了一个明显的错误。数据库将被塑造到我的程序中,该程序已经创建以传递诸如DayOfWk和PeriodValue之类的值,因此这只是唯一阻碍我前进的因素,但是它让我完全不知道在哪里选择了3个数学值。
请注意,时间表不需要列,因此,如果未引用它们(即课程表中的房间),请忽略它们。
如果您对我的程序或我可能忘记提及的任何内容还有其他疑问,或者需要我更详细地了解我试图通过某些代码行实现的目标,请询问,因为这是我程序中的主要绊脚石。
感谢你们中的任何人能够提供的任何帮助!
我认为您的课程表中可能存在一些数据损坏。在阅读了您的问题以及您希望能够做什么之后,我认为课程中的每个(天,期间)对都应该是唯一的,这似乎是合理的。例如,如果一个学生在星期三的1期,那么我们应该能够肯定地说他在数学。然而,情况似乎并非如此。如果我们运行以下查询,
select dayofwk, periodValue, COUNT(*)
from lesson_tbl
group by dayofwk, periodValue
having COUNT(*) > 1
我们得到以下结果
+ ------- + ----------- + ----- +
| dayofwk | periodValue | count |
+ ------- + ----------- + ----- +
| thu | 1 | 2 |
| tue | 1 | 2 |
| wed | 1 | 2 |
| thu | 3 | 2 |
| fri | 4 | 2 |
+ ------- + ----------- + ----- +
这告诉我们这些(日,周期)组合不是唯一的。如果一个学生在星期三的第 1 期,那么我们无法确定他是在数学还是经济。
从这里开始做什么
(1)如果你不相信一个(天、句号)组合在课程表中应该是唯一的,那么你想要的是不可能的。您将无法找到 (用户、天、期间)的"一个奇异值"。
(2)如果您认为(天,期间)组合在课程表中必须是唯一的,请修复损坏的数据。然后生成以下查询
declare @dayQuery varchar(3) = 'tue'
declare @PeriodQuery int = 1
declare @userQuery int = 1
select *
from lesson_tbl L
inner join student_tbl U
on case L.blockLessonIsIn when 1 then U.Block1
when 2 then U.Block2
when 3 then U.Block3
when 4 then U.Block4
end = L.subjectID
where L.dayofwk = @dayQuery
and L.periodValue = @PeriodQuery
and U.userID = @userQuery