union的用法
union的用法 union用来连接两个查询语句,把两个查询语句的查询结果合并起来,两个查询语句的查询字段个数必须一样,否则会出错,查询的字段可以不一样,类型也可以不一样,但是这样查询的意义不大,如果查询的字段不一样,最终的结果集以前者查询的字段为准。如果用union进行连接,碰到所有字段值一样的列,就会合并,去掉重复的行,如果用union all进行连接,则不会去掉重复的内容,所有的内容都被取出。 mysql> (select goods_id,goods_name,cat_id from goods where cat_id=2 order by goo ds_id asc) -> union (select goods_id,goods_name,cat_id from goods where cat_id=4 order by goods_id desc); +----------+--------------+--------+ | goods_id | goods_name | cat_id | +----------+--------------+--------+ | 16 | 恒基伟业G101 | 2 | | 1 | KD876 | 4 | | 14 | 诺基亚5800XM | 4 | | 18 | 夏新T5 | 4 | +----------+--------------+--------+ 4 rows in set (0.00 sec) #在每个查询语句中进行排序,再进行连接,其中的排序就没有意义,所以mysql回把排序的语句进行优化掉 mysql> select goods_id,cat_id,goods_name from goods where cat_id=2 -> union -> select goods_id,cat_id,goods_name from goods where cat_id=4 -> order by goods_id; +----------+--------+--------------+ | goods_id | cat_id | goods_name | +----------+--------+--------------+ | 1 | 4 | KD876 | | 14 | 4 | 诺基亚5800XM | | 16 | 2 | 恒基伟业G101 | | 18 | 4 | 夏新T5 | +----------+--------+--------------+ 4 rows in set (0.00 sec) mysql> #排序放在最后是针对最后的结果集进行排序,有意义, mysql> (select goods_id,cat_id,goods_name from goods where cat_id=3 order by goo ds_id limit 3) -> union -> (select goods_id,cat_id,goods_name from goods where cat_id=4 order by goo ds_id limit 2) -> ; +----------+--------+--------------+ | goods_id | cat_id | goods_name | +----------+--------+--------------+ | 8 | 3 | 飞利浦9@9v | | 9 | 3 | 诺基亚E66 | | 10 | 3 | 索爱C702c | | 1 | 4 | KD876 | | 14 | 4 | 诺基亚5800XM | +----------+--------+--------------+ 5 rows in set (0.05 sec) #这次排序影响的最终的显示结果有意义,所以order by 起了作用 一道面试题 mysql> create table a ( -> id char(1), -> num int -> )engine myisam charset utf8; Query OK, 0 rows affected (0.12 sec) mysql> insert into a values ('a',5),('b',10),('c',15),('d',10); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> create table b ( -> id char(1), -> num int -> )engine myisam charset utf8; Query OK, 0 rows affected (0.04 sec) mysql> mysql> insert into b values ('b',5),('c',15),('d',20),('e',99); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 #将两张表合并,相同id的num相加 mysql> select id,sum(num) from (select * from a union all select * from b) as temp group by id; +------+----------+ | id | sum(num) | +------+----------+ | a | 5 | | b | 15 | | c | 30 | | d | 30 | | e | 99 | +------+----------+ 5 rows in set (0.05 sec) #将两张表用union all合并,然后再按照id分组,求和,达到最终的目的