SQL 查询合并来自两个表的数据

Question

我有以下查询从 2 个表( Students 和 Subjects )中收集一些信息

SELECT 
    'StudentName' As Student,
    'Class'   As Class,
    'Subject' As Subject
    DATEDIFF( HOUR, hoursSpent.ClassStartTime, hoursSpent.ClassEndTime ) As HoursSpent
  FROM (
        SELECT stu.StudentName from Students s

        INNER JOIN Subjects  sub
        WHERE stu.StudentId = sub.StudentId
       ) hoursSpent

我有另一个与 Students 完全相同的表布局中的表格称为 OldStudents .

我的问题是我想合并来自 Students 的数据和 OldStudents并显示为一个，但不知道我该怎么做。

我试过这个，但这不起作用:

SELECT 
    'StudentName' As Student,
    'Class'   As Class,
    'Subject' As Subject
    DATEDIFF( HOUR, hoursSpent.ClassStartTime, hoursSpent.ClassEndTime ) As HoursSpent
  SELECT(
            FROM (
                SELECT stu.StudentName from Students s
                INNER JOIN Subjects  sub
                WHERE stu.StudentId = sub.StudentId
               ) hoursSpent,
          FROM (
                SELECT stu.StudentName from Students s
                INNER JOIN Subjects  sub
                WHERE stu.StudentId = sub.StudentId
               ) hoursSpent )

Answer 1

您可以尝试组合(联合)Students和 OldStudents然后将结果加入查询的其余部分:

WITH AllStudents
AS (
  SELECT * FROM Students
  UNION
  SELECT * FROM OldStudents
)
SELECT 
    'StudentName' As Student,
    'Class'   As Class,
    'Subject' As Subject
    DATEDIFF( HOUR, hoursSpent.ClassStartTime, hoursSpent.ClassEndTime ) As HoursSpent
  FROM (
        SELECT stu.StudentName from AllStudents stu
        INNER JOIN Subjects  sub
        WHERE stu.StudentId = sub.StudentId
       ) hoursSpent