mysql - 将两个不相关的 SELECT-s 与不同的 ORDER-s 组合成一个结果

Question

TL;DR:如何将不相关表中的两个不同顺序SELECT组合成一个结果？

---

假设我有 2 个不相关的表 - cats 和 games。

表“猫”:

+----+----------------+-----+
| id |      name      | age |
+----+----------------+-----+
|  1 | Balthazar      |   3 |
|  2 | Milkman        |   7 |
|  3 | The Dark Angel |   4 |
+----+----------------+-----+

表“游戏”

+----+----------+-------+
| id |   name   | plays |
+----+----------+-------+
|  1 | Snake    |    18 |
|  2 | Lemmings |   234 |
|  3 | Ludo     |    33 |
+----+----------+-------+

对于每个表，我分别需要一个 SELECT 来对它们进行排序，并获得不同的列:

SELECT age FROM `cats` ORDER BY age DESC;

和

SELECT plays FROM `games` ORDER BY plays DESC;

问题是我想使用一个查询而不是两个来获得两个结果。预期结果:

+-----+-------+
| age | plays |
+-----+-------+
|   7 |   234 |
|   4 |    33 |
|   3 |    18 |
+-----+-------+

JOIN 没有意义，因为表没有共享关系。我正在考虑使用 UNION 但我无法弄清楚如何处理这两个查询的事实:

1. 选择不同的列
2. 按不同列对结果排序

有什么想法可以实现吗？

Answer 1

一种方法是计算行号，然后连接该值:

select rn, max(age) as age, max(plays) as plays
from ((select c.age, NULL as plays, (@rnc := @rnc + 1) as rn
       from cats c cross join (select @rnc := 0) params
      ) union all
      (select NULL as age, g.plays, (@rng := @rng + 1) as rn
       from games g cross join (select @rng := 0) params
      )
     ) cg
group by rn;