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

I have following query which gathers some info from 2 tables (Students and Subjects)

我有以下查询从2个表(学生和科目)收集一些信息

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

I have another table which is exactly same as Students table in layout is called OldStudents.

我有另一个表格,与布局中的学生表格完全相同,称为OldStudents。

My problem is I want to combine data from both Students and OldStudents and show as one but have no clue how I can do that.

我的问题是我希望将学生和OldStudents的数据结合起来并显示为一个,但我不知道如何做到这一点。

I tried this but this does not work:

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

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 )

3 个解决方案

#1

You could try combining (unioning) Students and OldStudents and then joining the result to the rest of your query:

您可以尝试组合(联合)学生和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

#2

Essentially, you need to have two identical queries, one for each table and use UNION (or UNION ALL to concatenate them:

基本上,您需要有两个相同的查询,每个表一个并使用UNION(或UNION ALL来连接它们:

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
UNION ALL
SELECT 
    'StudentName' As Student,
    'Class'   As Class,
    'Subject' As Subject
    DATEDIFF( HOUR, hoursSpent.ClassStartTime, hoursSpent.ClassEndTime ) As HoursSpent
  FROM (
        SELECT stu.StudentName from OldStudents s

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

UNION will make the results distinct, UNION ALL will not.

UNION将使结果明显,UNION ALL不会。

#3

If you just want to add one set of data to the other use the UNION operator:

如果您只想将一组数据添加到另一组,请使用UNION运算符:

SELECT * FROM TableA
UNION
SELECT * FROM TableB

Each SELECT statement must have the same number of columns

每个SELECT语句必须具有相同的列数

#1