UNION 规则:
- 每个结果集必须有相同的列数;
- 每个结果集的列必须类型相容。
初始化条件:
mysql> CREATE TABLE T_TempEmployee (FIdCardNumber VARCHAR(20),FName VARCHAR(20),FAge
INT ,PRIMARY KEY (FIdCardNumber))
-> ;
Query OK, 0 rows affected
mysql> INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890121','Sarani',33);
INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890122','Tom',26);
INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890123','Yalaha',38);
INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890124','Tina',26);
INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890125','Konkaya',29);
INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890126','Fo fa' ,46) ;
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
mysql> select * from t_tempemployee;
+---------------+---------+------+
| FIdCardNumber | FName | FAge |
+---------------+---------+------+
| 1234567890121 | Sarani | 33 |
| 1234567890122 | Tom | 26 |
| 1234567890123 | Yalaha | 38 |
| 1234567890124 | Tina | 26 |
| 1234567890125 | Konkaya | 29 |
| 1234567890126 | Fo fa | 46 |
+---------------+---------+------+
6 rows in set
简单结果集联合:
mysql> select * from t_tempemployee;
+---------------+---------+------+
| FIdCardNumber | FName | FAge |
+---------------+---------+------+
| 1234567890121 | Sarani | 33 |
| 1234567890122 | Tom | 26 |
| 1234567890123 | Yalaha | 38 |
| 1234567890124 | Tina | 26 |
| 1234567890125 | Konkaya | 29 |
| 1234567890126 | Fo fa | 46 |
+---------------+---------+------+
6 rows in set
mysql> select * from t_employee;
+----------+-------+------+---------+-------------+---------------+
| fnumber | fname | fage | fsalary | fsubcompany | fdepartment |
+----------+-------+------+---------+-------------+---------------+
| DEV001 | Tom | 25 | 8300 | Beijing | Development |
| DEV002 | Jerry | 28 | 2300.8 | ShenZhen | Development |
| HR001 | Jane | 23 | 2200.88 | Beijing | HumanResource |
| HR002 | Tina | 25 | 5200.36 | Beijing | HumanResource |
| IT001 | Smith | 28 | 3900 | Beijing | InfoTech |
| IT002 | NULL | 27 | 2800 | ShenZhen | InfoTech |
| SALES001 | John | 23 | 5000 | Beijing | Sales |
| SALES002 | Kerry | 28 | 6200 | Beijing | Sales |
| SALES003 | Stone | 22 | 1200 | ShenZhen | Sales |
+----------+-------+------+---------+-------------+---------------+
9 rows in set
mysql> select fnumber,fname,fage from t_employee
-> union
-> select fidcardnumber,fname,fage from t_tempemployee;
+---------------+---------+------+
| fnumber | fname | fage |
+---------------+---------+------+
| DEV001 | Tom | 25 |
| DEV002 | Jerry | 28 |
| HR001 | Jane | 23 |
| HR002 | Tina | 25 |
| IT001 | Smith | 28 |
| IT002 | NULL | 27 |
| SALES001 | John | 23 |
| SALES002 | Kerry | 28 |
| SALES003 | Stone | 22 |
| 1234567890121 | Sarani | 33 |
| 1234567890122 | Tom | 26 |
| 1234567890123 | Yalaha | 38 |
| 1234567890124 | Tina | 26 |
| 1234567890125 | Konkaya | 29 |
| 1234567890126 | Fo fa | 46 |
+---------------+---------+------+
15 rows in set
结果集按条件联合:
mysql> select fnumber,fname,fage from t_employee where fage<30
-> union
-> select fidcardnumber,fname,fage from t_tempemployee where fage>40
-> union
-> select fidcardnumber,fname,fage from t_tempemployee where fage<30;
+---------------+---------+------+
| fnumber | fname | fage |
+---------------+---------+------+
| DEV001 | Tom | 25 |
| DEV002 | Jerry | 28 |
| HR001 | Jane | 23 |
| HR002 | Tina | 25 |
| IT001 | Smith | 28 |
| IT002 | NULL | 27 |
| SALES001 | John | 23 |
| SALES002 | Kerry | 28 |
| SALES003 | Stone | 22 |
| 1234567890126 | Fo fa | 46 |
| 1234567890122 | Tom | 26 |
| 1234567890124 | Tina | 26 |
| 1234567890125 | Konkaya | 29 |
+---------------+---------+------+
13 rows in set
mysql> select fnumber,fname,fage,fdepartment from t_employee
-> union
-> select fidcardnumber,fname,fage from t_tempemployee;
1222 - The used SELECT statements have a different number of columns
mysql> select fnumber,fname,fage,fdepartment from t_employee
-> union
-> select fidcardnumber,fname,fage,'临时工,不属于任何一个部门' from t_tempemployee;
+---------------+---------+------+----------------------------+
| fnumber | fname | fage | fdepartment |
+---------------+---------+------+----------------------------+
| DEV001 | Tom | 25 | Development |
| DEV002 | Jerry | 28 | Development |
| HR001 | Jane | 23 | HumanResource |
| HR002 | Tina | 25 | HumanResource |
| IT001 | Smith | 28 | InfoTech |
| IT002 | NULL | 27 | InfoTech |
| SALES001 | John | 23 | Sales |
| SALES002 | Kerry | 28 | Sales |
| SALES003 | Stone | 22 | Sales |
| 1234567890121 | Sarani | 33 | 临时工,不属于任何一个部门 |
| 1234567890122 | Tom | 26 | 临时工,不属于任何一个部门 |
| 1234567890123 | Yalaha | 38 | 临时工,不属于任何一个部门 |
| 1234567890124 | Tina | 26 | 临时工,不属于任何一个部门 |
| 1234567890125 | Konkaya | 29 | 临时工,不属于任何一个部门 |
| 1234567890126 | Fo fa | 46 | 临时工,不属于任何一个部门 |
+---------------+---------+------+----------------------------+
15 rows in set
规则二实例:
mysql> select fnumber,fname,fage from t_employee
-> union
-> select fidcardnumber,fage,fname from t_tempemployee;
+---------------+-------+---------+
| fnumber | fname | fage |
+---------------+-------+---------+
| DEV001 | Tom | 25 |
| DEV002 | Jerry | 28 |
| HR001 | Jane | 23 |
| HR002 | Tina | 25 |
| IT001 | Smith | 28 |
| IT002 | NULL | 27 |
| SALES001 | John | 23 |
| SALES002 | Kerry | 28 |
| SALES003 | Stone | 22 |
| 1234567890121 | 33 | Sarani |
| 1234567890122 | 26 | Tom |
| 1234567890123 | 38 | Yalaha |
| 1234567890124 | 26 | Tina |
| 1234567890125 | 29 | Konkaya |
| 1234567890126 | 46 | Fo fa |
+---------------+-------+---------+
15 rows in set
注:MYSQL将FAGE转换为文本类型