Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
LOCK TABLES logs_binds_students WRITE, submodules READ, modules READ,
disciplines READ, students_groups READ, record_books WRITE;
-- --------------------------------------------------------
# logs_bind_students
# 1. создать поле RecordBookID
# 2. заполнить поле RecordBookID
# 3. удалить все связи поля StudentID и потом само поле
# 4. создать связи для поля RecordBookID
ALTER TABLE `logs_binds_students`
ADD COLUMN `RecordBookID` int(11),
ADD KEY (`RecordBookID`);
UPDATE `logs_binds_students`
INNER JOIN disciplines on disciplines.ID = logs_binds_students.DisciplineID
INNER JOIN students_groups on students_groups.SemesterID = disciplines.SemesterID
AND students_groups.StudentID = logs_binds_students.StudentID
SET logs_binds_students.RecordBookID = students_groups.RecordBookID;
ALTER TABLE `logs_binds_students`
DROP FOREIGN KEY `logs_binds_students_ibfk_3`,
DROP INDEX `StudentID`,
DROP COLUMN `StudentID`;
ALTER TABLE `logs_binds_students`
ADD CONSTRAINT `logs_binds_students_ibfk_3` FOREIGN KEY (`RecordBookID`) REFERENCES `record_books` (`ID`);
#
# SELECT rating_table.StudentID,
# rating_table.RecordBookID,
# rating_table.TeacherID,
# rating_table.SubmoduleID,
# record_books.ID
# FROM rating_table
# LEFT JOIN record_books on rating_table.RecordBookID = record_books.ID
# Where record_books.ID IS NULL;
#
# SELECT rating_table.StudentID, record_books.ID FROM rating_table
# INNER JOIN submodules on submodules.ID = rating_table.SubmoduleID
# INNER JOIN modules on modules.ID = submodules.ModuleID
# INNER JOIN disciplines on disciplines.ID = modules.DisciplineID
# INNER JOIN students_groups on students_groups.SemesterID = disciplines.SemesterID
# AND students_groups.StudentID = rating_table.StudentID
# INNER JOIN study_groups on study_groups.ID = students_groups.GroupID
# INNER JOIN grades on grades.ID = study_groups.GradeID
# INNER JOIN record_books on
# record_books.Degree = grades.Degree and
# record_books.StudentID = rating_table.StudentID
# WHERE record_books.ID IS NULL;
#
# SELECT * FROM record_books WHERE record_books.StudentID = 182;
#
# SELECT * FROM view_students WHERE view_students.StudentID = 182;
UNLOCK TABLES;