mysql - 如何使用 JOIN 在 mysql 中创建 View ？

Question

有两个表，table1 和 table2。

[表1]

+----+------+
| no | time |
+----+------+
| 1  | 1111 |
+----+------+
| 2  | 2222 |
+----+------+
| 3  | 3333 |
+----+------+
| 4  | 4444 |
+----+------+
| 5  | 5555 |
+----+------+

[表2]

+----+-----+----------+------+
| no | idx | name     | rank |
+----+-----+----------+------+
| 1  | 1   | Apple    | 1    |
+----+-----+----------+------+
| 2  | 1   | Banana   | 2    |
+----+-----+----------+------+
| 3  | 1   | Car      | 3    |
+----+-----+----------+------+
| 4  | 1   | Dragon   | 4    |
+----+-----+----------+------+
| 5  | 1   | Eagle    | 5    |
+----+-----+----------+------+
| 6  | 2   | Fire     | 2    |
+----+-----+----------+------+
| 7  | 2   | God      | 3    |
+----+-----+----------+------+
| 8  | 2   | Hippo    | 4    |
+----+-----+----------+------+
| 9  | 3   | Icecream | 1    |
+----+-----+----------+------+
| 10 | 3   | Juice    | 3    |
+----+-----+----------+------+
| 11 | 3   | Korea    | 4    |
+----+-----+----------+------+
| 12 | 3   | Low      | 5    |
+----+-----+----------+------+
| 13 | 4   | Mother   | 2    |
+----+-----+----------+------+
| 14 | 4   | News     | 3    |
+----+-----+----------+------+
| 15 | 5   | Object   | 1    |
+----+-----+----------+------+

而且，我想使用 mysql 制作一个类似如下图像的 View 。

+----+-----+----------+------+------+
| no | idx | name     | time | rank |
+----+-----+----------+------+------+
| 1  | 1   | Apple    | 1111 | 1    |
+----+-----+----------+------+------+
| 2  | 1   | Banana   | 1111 | 2    |
+----+-----+----------+------+------+
| 3  | 1   | Car      | 1111 | 3    |
+----+-----+----------+------+------+
| 4  | 1   | Dragon   | 1111 | 4    |
+----+-----+----------+------+------+
| 5  | 1   | Eagle    | 1111 | 5    |
+----+-----+----------+------+------+
| 1  | 2   | Apple    | 2222 | 1    |
+----+-----+----------+------+------+
| 6  | 2   | Fire     | 2222 | 2    |
+----+-----+----------+------+------+
| 7  | 2   | God      | 2222 | 3    |
+----+-----+----------+------+------+
| 8  | 2   | Hippo    | 2222 | 4    |
+----+-----+----------+------+------+
| 5  | 2   | Eagle    | 2222 | 5    |
+----+-----+----------+------+------+
| 9  | 3   | Icecream | 3333 | 1    |
+----+-----+----------+------+------+
| 6  | 3   | Fire     | 3333 | 2    |
+----+-----+----------+------+------+
| 10 | 3   | Juice    | 3333 | 3    |
+----+-----+----------+------+------+
| 11 | 3   | Korea    | 3333 | 4    |
+----+-----+----------+------+------+
| 12 | 3   | Low      | 3333 | 5    |
+----+-----+----------+------+------+
| 9  | 4   | Icecream | 4444 | 1    |
+----+-----+----------+------+------+
| 13 | 4   | Mother   | 4444 | 2    |
+----+-----+----------+------+------+
| 14 | 4   | NEws     | 4444 | 3    |
+----+-----+----------+------+------+
| 11 | 4   | Korea    | 4444 | 4    |
+----+-----+----------+------+------+
| 12 | 4   | Low      | 4444 | 5    |
+----+-----+----------+------+------+
| 15 | 5   | Object   | 5555 | 1    |
+----+-----+----------+------+------+
| 13 | 5   | Mother   | 5555 | 2    |
+----+-----+----------+------+------+
| 14 | 5   | News     | 5555 | 3    |
+----+-----+----------+------+------+
| 11 | 5   | Korea    | 5555 | 4    |
+----+-----+----------+------+------+
| 12 | 5   | Low      | 5555 | 5    |
+----+-----+----------+------+------+

rank列的范围是1到5。

并且，如果没有像(idx2＆rank5)这样的数据，那么我想使用具有相同rank和最大idx和较低的数据idx 比该数据。

我尝试使用以下查询。

select * from (`table1` JOIN (select 1 AS `rank` union select 2 union select 3 union select 4 union select 5) AS `x` )

但是，我不知道以后该怎么办。

Answer 1

试试这个

select ifnull(t2a.no, t2b.no) no, t.idx, ifnull(t2a.name, t2b.name) name, t.rank
from (
    select t3.idx, t1.no rank, (select max(idx) from table2 where rank = t1.no and idx<t3.idx) max_idx
    from (select distinct idx from table2 ) t3
    cross join table1 t1    
) t 
 left join table2 t2a on t2a.rank=t.rank and t.idx  = t2a.idx
 left join table2 t2b on t2b.rank=t.rank and t.max_idx = t2b.idx
order by 2,4