Mysql 视图创建语句中不能使用union吗？

Question

本帖最后由 onunix 于 2012-04-06 18:37:17 编辑

Answer 1

用union是有原则的，select * from InformalBudget_1 与select * from InformalBudget
列数是否相同数据类型是否一致，如果列数和数据类型都不一致的话，肯定会报错的。

Answer 2

mysql> select * from device;
+--------+------------+
| dev_id | power_stat |
+--------+------------+
|      1 |        319 |
|      3 |        415 |
|      4 |        415 |
|      5 |        255 |
|      7 |          0 |
|      8 |        415 |
|      9 |          0 |
|     10 |          0 |
|     11 |          0 |
+--------+------------+
9 rows in set (0.07 sec)mysql> create view InformalBudgetUnion as
    -> select * from device union all select * from device;
Query OK, 0 rows affected (0.07 sec)mysql> select * from InformalBudgetUnion;
+--------+------------+
| dev_id | power_stat |
+--------+------------+
|      1 |        319 |
|      3 |        415 |
|      4 |        415 |
|      5 |        255 |
|      7 |          0 |
|      8 |        415 |
|      9 |          0 |
|     10 |          0 |
|     11 |          0 |
|      1 |        319 |
|      3 |        415 |
|      4 |        415 |
|      5 |        255 |
|      7 |          0 |
|      8 |        415 |
|      9 |          0 |
|     10 |          0 |
|     11 |          0 |
+--------+------------+
18 rows in set (0.00 sec)mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.58-community |
+------------------+
1 row in set (0.00 sec)mysql>