Learn PHP-28th-union usage with Yan 18 In depth/** & nbsp; Yan 18 public welfare PHP Training & nbsp; classroom address: YY channel 88354001 & nbsp; learning community: www. zixue. it & nbsp; **/mysql & gt; createtablea (& nbsp; & nbs and Yan 18 learn PHP-28th-union usage in depth
/**
Yan 18 public welfare PHP Training
Classroom address: YY channel 88354001
Learning Community: www. zixue. it
**/
Mysql> create table (
-> 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', 10), ('C', 15), ('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', 20), ('e', 99 );
'> \ C
'>' \ C
Mysql> insert into B values ('B', 5), ('C', 15), ('D', 20), ('e', 99 );
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
Mysql> select * from;
+ ------ +
| 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> # use a left-side connection
Mysql> select a. *, B. * from
-> A left join B on a. id = B. id;
+ ------ +
| Id | num |
+ ------ +
| A | 5 | NULL |
| B | 10 | B | 5 |
| C | 15 | c | 15 |
| D | 10 | d | 20 |
+ ------ +
4 rows in set (0.00 sec)
Mysql> # think of the above result as a temporary table, and then use the from subquery to calculate the sum of a. num + B. num
Mysql> # In this case, the students will try it by themselves. if you encounter any problems, check the ifnull function.
Mysql> # and e is missing, so we have to connect to union on the left and then subquery
Mysql>
Mysql> # in another way, first combine the data of the two tables into one, and then use the sum () function to add the data.
Mysql> select * from;
+ ------ +
| 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
-> 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 again,
Mysql> select id, sum (num) from (
->
-> Select * from
-> 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 is wrong. do not repeat it
Mysql> select id, sum (num) from (
->
-> Select * from
-> 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
Instructor Yan Shiba is too humorous. yesterday's video is as follows:
Http://www.tudou.com/programs/view/ahOl2jS2cYY/
