如何在MS SQL中为具有相同数据的两列编写MS SQL代码

Question

这里是两个表。

Table1
SchoolID   Teacher_name   comm_yr
01          John            1990
02          Heley           1995
03         George          1994
04          Chris           1998
05          Mary            1993
Table2
School_ID   Student_name
01        Heley
02        Chris
03        Chris
04        Mary
05        Heley

我想列出每个学生作为学生的年份

和每个学生作为老师的年份。

    SELECT Teacher_name, comm_yr As Teacher_comm_yr, comm_yr As Student_comm_yr
FROM Table1 INNER JOIN Table2
WHERE comm_yr (SELECT comm_yr As Teacher_comm_yr From Table1
Where teacher_name=student_name);

表格显示

Teacher_name    Teacher_comm_yr  Student_comm_yr
John                   1990 1990
Heley                 1995  1995
George                 1994 1994
Chris                  1998 1998
Mary                    1993    1993

但我想要的是

Teacher_name    Teacher_comm_yr Student_comm_yr
John               1990 
Heley               1995     1990
George              1994    
Chris               1998      1995
Chris               1998     1994
Mary                1993     1998

但我只能在同一年执行你能帮忙吗..

谢谢！

Answer 1

一些注意事项。

SELECT 
  t1.Teacher_name, 
  t2.Student_name, 
  t1.comm_yr AS Teacher_comm_yr, 
  t1.comm_yr AS Student_comm_yr
FROM Table1 AS t1 
LEFT JOIN Table2 AS t2 
ON t2.student_name = t1.teacher_name;

根据您的数据，我不明白克里斯从哪里得到 1995 年。

那么呢：

SELECT t.SchoolID,
       t.Teacher_name,
       t.comm_yr,
       q.SchoolID,
       q.Teacher_name,
       q.Student_name,
       q.comm_yr
FROM   table11 t
       LEFT JOIN (SELECT a.SchoolID,
                         a.Teacher_name,
                         b.Student_name,
                         a.comm_yr
                  FROM   table11 AS a
                         INNER JOIN table22 AS b
                           ON a.SchoolID = b.School_ID) AS q
         ON t.Teacher_name = q.Student_name; 

我包含了比必要的更多的字段（列）来测试这个想法。