mysql - SQL 连接以添加缺失的行

Question

我是 SQL 新手，需要一些帮助。在一个场景中，我想加入教师表，其中有教师所教科目的数据。我想通过连接添加一些缺失的行，以便我可以显示一年中正在教授某个类(class)但缺少其教师详细信息的行。适用于所有主题的任何术语。

请注意，可能有多个编号。一年任期

他们认为的教师和类(class)映射表

Teacher Class
Aman    9th
Ankit   9th
Abhinav 10th
Bharat  10th

教师和他们认为的术语映射表

Teacher Term
Aman    1stTerm
Ankit   2nd Term
Abhinav 2nd Term
Bharat  1stTerm

数据表

Subject YEAR    Teacher Marks
Maths   2014    Aman    80
Maths   2014    Ankit   85
Maths   2015    Abhinav 69
Science 2014    Abhinav 30
Science 2015    Aman    20

输出表

Subject Class   Teacher Class   Term        Marks
Maths   2014    Aman    9th     1stTerm     80
Maths   2014    Ankit   9th     2nd Term    85
Maths   2015    Abhinav 10th    2nd Term    69
Maths   2015    Bharat  10th    1stTerm     0
Science 2014    Abhinav 10th    2nd Term    30
Science 2014    Bharat  10th    1stTerm     0
Science 2015    Aman    9th     1stTerm     20
Science 2015    Ankit   9th     2nd Term    0

Answer 1

我现在无法检查，但类似这样的东西应该可以工作:

select *, coalesce(dt.Marks, 0) as Marks
from TeacherClass tc
join TeacherTerm tt on tc.Teacher = tt.Teacher
cross join (select 2014 as Year union select 2015 as Year) cj
left join DataTable dt on tc.Teacher = dt.Teacher and cj.Year = dt.Year



DECLARE @TeacherClass TABLE(Teacher VARCHAR(MAX), Class VARCHAR(MAX))
DECLARE @TeacherTerm TABLE(Teacher VARCHAR(MAX), Term VARCHAR(MAX))
DECLARE @DataTable TABLE(Subject VARCHAR(MAX), YEAR  INT,   Teacher VARCHAR(MAX), Marks INT)


INSERT INTO @TeacherClass VALUES
('Aman','9th'),
('Ankit','9th'),
('Abhinav','10th'),
('Bharat','10th')

INSERT INTO @TeacherTerm VALUES
('Aman','1stTerm'),
('Ankit','2nd Term'),
('Abhinav','2nd Term'),
('Bharat','1stTerm')

INSERT INTO @DataTable VALUES
('Maths',   2014    ,'Aman',    80),
('Maths',   2014    ,'Ankit',   85),
('Maths',   2015    ,'Abhinav', 69),
('Science', 2014    ,'Abhinav', 30),
('Science', 2015    ,'Aman',    20)


select Subject, cj.Year, tc.Teacher, tc.Class, tt.Term, coalesce(dt.Marks, 0) as Marks
from @TeacherClass tc
join @TeacherTerm tt on tc.Teacher = tt.Teacher
cross join (select 2014 as Year union select 2015 as Year) cj
left join @DataTable dt on tc.Teacher = dt.Teacher and cj.Year = dt.Year
ORDER BY Subject, cj.YEAR

输出:

Subject Year    Teacher Class   Term        Marks
NULL    2014    Bharat  10th    1stTerm     0
NULL    2015    Bharat  10th    1stTerm     0
NULL    2015    Ankit   9th     2nd Term    0
Maths   2014    Aman    9th     1stTerm     80
Maths   2014    Ankit   9th     2nd Term    85
Maths   2015    Abhinav 10th    2nd Term    69
Science 2014    Abhinav 10th    2nd Term    30
Science 2015    Aman    9th     1stTerm     20