mysql里使用union

[rainbowliuliang]

mysql里使用union对多个表进行联合
select语名1 union (select语句2 union select语句3)
使用括号改变执行顺序会报错 是不是不支持

还有一个问题
mysql哪个版本以后支持完全联接 （full）

多谢

[懒得去死]

1、必须为子表定义别名．
mysql> select rand(1) union select * from (select rand(2) union select rand(3)) T;
+-------------------+
| rand(1)           |
+-------------------+
| 0.405403537121977 | 
| 0.655586646549019 | 
|  0.90576975597606 | 
+-------------------+
3 rows in set (0.00 sec)

mysql> 

２、暂时不支持。
不过可以实现。
mysql> select * from t1;
+----+---------+
| id | game_id |
+----+---------+
|  1 |       1 | 
|  2 |       2 | 
|  3 |       3 | 
|  4 |       5 | 
|  5 |       6 | 
|  6 |       8 | 
+----+---------+
6 rows in set (0.00 sec)

mysql> select * from t2;
+----+---------+
| id | game_id |
+----+---------+
|  1 |       1 | 
|  2 |       2 | 
|  3 |       6 | 
|  4 |       8 | 
|  5 |      10 | 
|  6 |       4 | 
+----+---------+
6 rows in set (0.00 sec)

mysql> select * from t1 left join t2 using(id) union all select * from t1 right join t2 using(id);
+----+---------+---------+
| id | game_id | game_id |
+----+---------+---------+
|  1 |       1 |       1 | 
|  2 |       2 |       2 | 
|  3 |       3 |       6 | 
|  4 |       5 |       8 | 
|  5 |       6 |      10 | 
|  6 |       8 |       4 | 
|  1 |       1 |       1 | 
|  2 |       2 |       2 | 
|  3 |       6 |       3 | 
|  4 |       8 |       5 | 
|  5 |      10 |       6 | 
|  6 |       4 |       8 | 
+----+---------+---------+
12 rows in set (0.00 sec)

mysql> 

[懒得去死]

给你看一个明显的。上面的不明显

mysql> delete from t2 where id = 5;
Query OK, 1 row affected (0.00 sec)
mysql> delete from t1 where id = 4;
Query OK, 1 row affected (0.00 sec)
mysql> select t1.*,t2.* from t1 left join t2 using(id) union all select t1.*,t2.* from t1 right join t2 using(id);
+------+---------+------+---------+
| id   | game_id | id   | game_id |
+------+---------+------+---------+
|    1 |       1 |    1 |       1 | 
|    2 |       2 |    2 |       2 | 
|    3 |       3 |    3 |       6 | 
|    5 |       6 | NULL |    NULL | 
|    6 |       8 |    6 |       4 | 
|    1 |       1 |    1 |       1 | 
|    2 |       2 |    2 |       2 | 
|    3 |       3 |    3 |       6 | 
| NULL |    NULL |    4 |       8 | 
|    6 |       8 |    6 |       4 | 
+------+---------+------+---------+
10 rows in set (0.00 sec)

mysql> 

[yiyang1231]

看起来好复杂阿

[laijavatoo]

厉害

[wshcdr]

测试留名

mysql> show tables;
+------------------------------+
| Tables_in_104db              |
+------------------------------+
| t_meeting                    |
+------------------------------+