如何交替排序多个查询的结果? [英] How to sort the result of multiple queries alternatively?

问题描述

我有一个由三个 select 子句组成的查询，如下所示:

I have a query made of three select clause like this:

select id, colors from table1
    union all
select id, numbers from table2 
    union all
select id, names from table3

还有表格结构:

// table1               // table2              //table3
+----+--------+        +----+---------+        +----+-------+
| id | colors |        | id | numbers |        | id | names |
+----+--------+        +----+---------+        +----+-------+
| 1  | red    |        | 1  | ten     |        | 1  | jack  |
| 2  | green  |        | 2  | two     |        | 2  | peter |
| 3  | blue   |        | 3  | one     |        +----+-------+
| 4  | yellow |        | 4  | three   |
+----+--------+        | 5  | six     |
                       | 6  | five    |
                       +----+---------+

现在我想要结果的顺序:

Now I want this order for the results:

+----+--------+
| id | colors |
+----+--------+
| 1  | red    |
| 2  | ten    |
| 3  | jack   |
| 4  | green  |
| 5  | two    |
| 6  | peter  |
| 7  | blue   |
| 8  | one    |
| 9  | yellow |
| 10 | three  |
| 11 | six    |
| 12 | five   |
+----+--------+

我该如何实现?(应该注意，order by 1,2,3 对我不起作用)

How can I implement that? (it should be noted, order by 1,2,3 does not work for me)

推荐答案

这就是你可以做到的

select @rn:=@rn+1 as id,colors from (
  (select @rn1:= @rn1+1 as rn,colors from table1,(select @rn1:=0)x order by id )
   union all 
  (select @rn2:= @rn2+1 as rn,numbers as colors from table2,(select @rn2:=0.5)x order by id)
   union all 
  (select @rn3:= @rn3+1 as rn,names as colors from table3,(select @rn3:=0.6)x order by id )
)x,(select @rn:=0)y order by rn ;

想法是为每个表项分配一个rn值，并且需要确保这些值总是按升序排列

The idea is to assign a rn value for each table item and need to make sure that these values are always in ascending order

因此，如果您为每个表运行查询，您将拥有

So if you run the query for each table you will have

mysql> select @rn1:= @rn1+1 as rn,colors from table1,(select @rn1:=0)x order by id;
+------+--------+
| rn   | colors |
+------+--------+
|    1 | red    |
|    2 | green  |
|    3 | blue   |
|    4 | yellow |
+------+--------+
4 rows in set (0.00 sec)

mysql> select @rn2:= @rn2+1 as rn,numbers as colors from table2,(select @rn2:=0.5)x order by id;
+------+--------+
| rn   | colors |
+------+--------+
|  1.5 | ten    |
|  2.5 | two    |
|  3.5 | one    |
|  4.5 | three  |
|  5.5 | six    |
|  6.5 | five   |
+------+--------+
6 rows in set (0.00 sec)

mysql> select @rn3:= @rn3+1 as rn,names as colors from table3,(select @rn3:=0.6)x order by id;
+------+--------+
| rn   | colors |
+------+--------+
|  1.6 | jack   |
|  2.6 | peter  |
+------+--------+
2 rows in set (0.00 sec)

这里可以看到 table1 rn 的值为 1,2,3,....table2 值为 1.5,2.5,3.5,....table3 值为 1.6,2.6,....

Here you can see table1 rn values are 1,2,3,.... table2 values are 1.5,2.5,3.5,.... table3 values are 1.6,2.6,....

所以最后当你用所有 rn 对结果进行排序时，它会变成

so finally when you order the result with all rn it will be as

1,1.5,1.6,2,2.5,2.6,....

这篇关于如何交替排序多个查询的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！