union的用法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 | 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分组,求和,达到最终的目的 |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步