mysql - 如何在mysql中加入来自同一个表的两个选择

Question

Ï 需要连接同一表的两个选择(顶部和底部行)，但内部连接返回一个空集。我不明白这是怎么可能的，因为我正在对两个表都相同的新列进行连接。

下面是底行表和顶行表的选择子查询:

底行:

select @n := @n + 1 row_number, st.* from (select @n:=0) init, strmrs st where timestamp between 1514764860 and 1517443140 order by timestamp desc, username limit 10;

给出结果:

+------------+----------+------------+-------+---------+-----------+
| row_number | username | timestamp  | game  | viewers | followers |
+------------+----------+------------+-------+---------+-----------+
|          1 | user1    | 1517443140 | game1 |       3 |     44669 |
|          2 | user2    | 1517443081 | game2 |       5 |     44668 |
|          3 | user1    | 1517443080 | game1 |       3 |     44668 |
|          4 | user2    | 1517443021 | game2 |       5 |     44667 |
|          5 | user1    | 1517443020 | game1 |       3 |     44667 |
|          6 | user2    | 1517442961 | game2 |       5 |     44666 |
|          7 | user1    | 1517442960 | game1 |       3 |     44666 |
|          8 | user2    | 1517442901 | game2 |       5 |     44665 |
|          9 | user1    | 1517442900 | game1 |       3 |     44665 |
|         10 | user2    | 1517442841 | game2 |       5 |     44664 |
+------------+----------+------------+-------+---------+-----------+

和

顶行:

select @n := @n + 1 row_number, st.* from (select @n:=0) init, strmrs st where timestamp between 1514764860 and 1517443140 order by timestamp, username limit 10;

给出结果:

+------------+----------+------------+-------+---------+-----------+
| row_number | username | timestamp  | game  | viewers | followers |
+------------+----------+------------+-------+---------+-----------+
|          1 | user1    | 1514764860 | game1 |       3 |        31 |
|          2 | user2    | 1514764861 | game2 |       5 |        31 |
|          3 | user1    | 1514764920 | game1 |       3 |        32 |
|          4 | user2    | 1514764921 | game2 |       5 |        32 |
|          5 | user1    | 1514764980 | game1 |       3 |        33 |
|          6 | user2    | 1514764981 | game2 |       5 |        33 |
|          7 | user1    | 1514765040 | game1 |       3 |        34 |
|          8 | user2    | 1514765041 | game2 |       5 |        34 |
|          9 | user1    | 1514765100 | game1 |       3 |        35 |
|         10 | user2    | 1514765101 | game2 |       5 |        35 |
+------------+----------+------------+-------+---------+-----------+

我的问题是，两个表的内部连接给出了一个空集，而左连接或右连接在另一侧给出了空列。例如:

select late.row_number, early.row_number, early.username as early_users, late.username as late_users, early.followers as early_followers, late.followers as late_followers from 
(select @n := @n + 1 row_number, st.* from (select @n:=0) init, strmrs st where timestamp between 1514764860 and 1517443140 order by timestamp desc, username limit 10) late 
left join 
(select @n := @n + 1 row_number, st.* from (select @n:=0) init, strmrs st where timestamp between 1514764860 and 1517443140 order by timestamp, username limit 10) early 
on late.row_number = early.row_number;

返回:

+------------+------------+-------------+------------+-----------------+----------------+
| row_number | row_number | early_users | late_users | early_followers | late_followers |
+------------+------------+-------------+------------+-----------------+----------------+
|          1 |       NULL | NULL        | user1      |            NULL |          44669 |
|          2 |       NULL | NULL        | user2      |            NULL |          44668 |
|          3 |       NULL | NULL        | user1      |            NULL |          44668 |
|          4 |       NULL | NULL        | user2      |            NULL |          44667 |
|          5 |       NULL | NULL        | user1      |            NULL |          44667 |
|          6 |       NULL | NULL        | user2      |            NULL |          44666 |
|          7 |       NULL | NULL        | user1      |            NULL |          44666 |
|          8 |       NULL | NULL        | user2      |            NULL |          44665 |
|          9 |       NULL | NULL        | user1      |            NULL |          44665 |
|         10 |       NULL | NULL        | user2      |            NULL |          44664 |
+------------+------------+-------------+------------+-----------------+----------------+

什么时候，我想得到的是:

+------------+------------+-------------+------------+-----------------+----------------+
| row_number | row_number | early_users | late_users | early_followers | late_followers |
+------------+------------+-------------+------------+-----------------+----------------+
|          1 |          1 | user1       | user1      |              31 |          44669 |
|          2 |          2 | user2       | user2      |              31 |          44668 |
|          3 |          3 | user1       | user1      |              32 |          44668 |
|          4 |          4 | user2       | user2      |              32 |          44667 |
|          5 |          5 | user1       | user1      |              33 |          44667 |
|          6 |          6 | user2       | user2      |              33 |          44666 |
|          7 |          7 | user1       | user1      |              34 |          44666 |
|          8 |          8 | user2       | user2      |              34 |          44665 |
|          9 |          9 | user1       | user1      |              35 |          44665 |
|         10 |         10 | user2       | user2      |              35 |          44664 |
+------------+------------+-------------+------------+-----------------+----------------+

谁能告诉我我做错了什么/如何实现我想要的？

非常感谢您:)

Answer 1

首先，这与任何事情都没有关系，您不是在进行 INNER JOIN，而是在进行 OUTER JOIN。

我希望我有明确的解释，说明为什么计算的行号在执行外部连接时与实际数据库列的行为方式不同，但事实似乎确实如此。因此，您应该创建临时表，其中这些计算出的行号将成为实际列:

CREATE TEMPORARY TABLE late as
    select @n := @n + 1 row_number, st.* from (select @n:=0) init, strmrs st where timestamp between 1514764860 and 1517443140 order by timestamp desc, username limit 10;

CREATE TEMPORARY TABLE early as
    select @n := @n + 1 row_number, st.* from (select @n:=0) init, strmrs st where timestamp between 1514764860 and 1517443140 order by timestamp, username limit 10;

select late.row_number, early.row_number, early.username as early_users, late.username as late_users, early.followers as early_followers, late.followers as late_followers from
late l JOIN early on late.row_number = early.row_number;

既然行号在实际的列中，就不需要进行外连接；内部连接就足够了。如果您在没有临时表的情况下使用内部联接，则不会返回任何行，这可能就是您求助于外部联接的原因。

顺便说一句，如果您需要在程序中处理这些值，使用唯一列名的建议就变得至关重要。

如果您要连接很多行(这里每个表中只有 10 行)，您会考虑在临时表的 row_number 列上创建索引，可能只需将它们定义为主键即可。