mysql - SQL 从每 2 个或更多不同的表中选择 1 列

Question

我在 mysql 中有这个表:

students    time    class_student
someone     1   something01
someone     2   something02
someone     3   something03
someone     4   something04
someone     5   something05


theachers   time    class_teachers
someone     1   something11
someone     3   something12
someone     5   something13
someone     7   something14
someone     9   something15
someone     11  something16

而且我需要在两个表中始终按每个表顺序获取类字段，就像这样

    time    class_student class_teachers
    1   something01         something11
    2   something02
    3   something03         something12
    4   something04
    5   something05         something13
    7                       something14
    9                       something15
    11                      something16

首先我测试这个查询结果总是在两个表中

SELECT time FROM table1
UNION
SELECT time FROM table2
ORDER BY time

我有这个返回数据但在同一个字段中的查询

SELECT class_student FROM table1 WHERE time IN (
                                                    SELECT time FROM table2
                                                    UNION
                                                    SELECT time FROM table1
                                                    ORDER BY time
                                                    )
UNION
SELECT class_teachers FROM table2 WHERE time IN (
                                            SELECT time FROM table2
                                            UNION
                                            SELECT time FROM table1
                                            ORDER BY time
                                            );

我尝试了这个，但是 sql 没有执行它

SELECT
    class_student,
    class_teachers
FROM 
    table1
    inner join
    table2 on time IN (
                SELECT time FROM table1
                UNION
                SELECT time FROM table2
                ORDER BY time
                );

Answer 1

试试这个。

SELECT CASE WHEN c.time IS NULL THEN a.time  ELSE c.time END AS time, c.class_student, a.class_teachers
FROM table1 c
FULL JOIN table2 a ON c.time = a.time