Heel Yan 18 Study php-28th Day-union usage in-depth explanation
/**
Yan 18 public benefit PHP Training
Classroom Address: YY Channel 88354001
Learning Community: www.zixue.it
**/
Mysql> CREATE Table A (
ID char (1),
-Num INT
) engine MyISAM charset UTF8;
Query OK, 0 rows affected (0.17 sec)
Mysql>
Mysql> INSERT into a values (' a ', 5), (' B ', ten), (' C ', +), (' d ', 10);
Query OK, 4 rows Affected (0.00 sec)
Records:4 duplicates:0 warnings:0
Mysql>
Mysql> CREATE TABLE B (
ID char (1),
-Num INT
) engine MyISAM charset UTF8;
Query OK, 0 rows affected (0.17 sec)
Mysql>
Mysql> INSERT into a values (' B ', 5), (' c,15 '), (' d ', +), (' E ', 99);
' > \c
' > ' \c
mysql> INSERT into B values (' B ', 5), (' C ', +), (' d ', +), (' E ', 99);
Query OK, 4 rows Affected (0.00 sec)
Records:4 duplicates:0 warnings:0
Mysql> select * from A;
+------+------+
| ID | num |
+------+------+
| A | 5 |
| B | 10 |
| C | 15 |
| D | 10 |
+------+------+
4 rows in Set (0.00 sec)
Mysql> SELECT * from B;
+------+------+
| ID | num |
+------+------+
| B | 5 |
| C | 15 |
| D | 20 |
| e | 99 |
+------+------+
4 rows in Set (0.00 sec)
Mysql> #可用用左连接来做
Mysql> Select a.*,b.* from
-A LEFT join B on a.id=b.id;
+------+------+------+------+
| ID | num | ID | num |
+------+------+------+------+
| A | 5 | NULL | NULL |
| B | 10 | B | 5 |
| C | 15 | C | 15 |
| D | 10 | D | 20 |
+------+------+------+------+
4 rows in Set (0.00 sec)
Mysql> #再把上面的结果看成一张临时表, again from sub-query, calculate a.num+b.num and
Mysql> #这个思路, students to try their own class. If you encounter a pit, check ifnull function
Mysql> #而且少了e, had to left join Union right, and then subquery
Mysql>
Mysql> #换个思路, the data of 2 tables is first union to one piece, then the sum () function is used to add
Mysql> select * from A;
+------+------+
| ID | num |
+------+------+
| A | 5 |
| B | 10 |
| C | 15 |
| D | 10 |
+------+------+
4 rows in Set (0.00 sec)
Mysql> SELECT * from B;
+------+------+
| ID | num |
+------+------+
| B | 5 |
| C | 15 |
| D | 20 |
| e | 99 |
+------+------+
4 rows in Set (0.00 sec)
Mysql> SELECT * from a
Union
select * from B;
+------+------+
| ID | num |
+------+------+
| A | 5 |
| B | 10 |
| C | 15 |
| D | 10 |
| B | 5 |
| D | 20 |
| e | 99 |
+------+------+
7 Rows in Set (0.00 sec)
Mysql> #再sum一下,
mysql> select Id,sum (num) from (
-
SELECT * from a
Union
SELECT * from b
) as TMP
-
Group by ID;
+------+----------+
| ID | SUM (num) |
+------+----------+
| A | 5 |
| B | 15 |
| C | 15 |
| D | 30 |
| e | 99 |
+------+----------+
5 rows in Set (0.03 sec)
Mysql> #c错了, don't repeat.
mysql> select Id,sum (num) from (
-
SELECT * from a
UNION ALL
SELECT * from b
) as TMP
-
Group by ID;
+------+----------+
| ID | SUM (num) |
+------+----------+
| A | 5 |
| B | 15 |
| C | 30 |
| D | 30 |
| e | 99 |
+------+----------+
5 rows in Set (0.00 sec)
Mysql> exit
Yan 18 teacher is too humorous, yesterday's video is as follows:
http://www.tudou.com/programs/view/ahOl2jS2cYY/
