union:合并两条或多条语句的结果。
语法:sql1 union sql2
# 要求查出价格低于100元和价格高于4000元的商品,要求不能用or
# 先查低于100元的商品
select goods_id,goods_name,shop_price from goods
where shop_price < 100;
# 再取出大于4000的商品
select goods_id,goods_name,shop_price from goods
where shop_price > 4000;
# 现在将两个语句的结果拼接起来就可以了:用union
select goods_id,goods_name,shop_price from goods
where shop_price < 100
union
select goods_id,goods_name,shop_price from goods
where shop_price > 4000;
问:能否从两张表查询再union呢?
答:union只是合并结果集,不区分来自于哪一张表。
问:取自于两张表,通过别名让两个结果集的列一致,那么,如果取出的结果集,列名不一样,还能否合并?
答:可以,而且取出的最终列名以第一条sql语句的列名为准。
问:union满足什么条件就可以用了?
答:只要结果集中的列数一致就可以。
问:union后的结果集,能否再排序呢?
答:可以的,sql1 union sql2 order by。。。order by 针对的是最终结果集排序。
用union取出第四个栏目和第五个栏目的商品,并按价格升序排列。
注意;应该针对最终结果集排序。
select goods_id,cat_id,goods_name,shop_price
from goods where cat_id=4
union
select goods_id,cat_id,goods_name,shop_price
from goods where cat_id=5
order by shop_price; # asc 写不写无所谓。
使用order by 的注意事项:
如下:内层语句的desc 怎么没发挥作用呢?
(select goods_id,cat_id,goods_name,shop_price
from goods where cat_id=4 order by shop_price desc)
union
(select goods_id,cat_id,goods_name,shop_price
from goods where cat_id=5 order by shop_price desc)
order by shop_price;
外层语句还要对最终结果再次排序,因此,内层语句的排序就没有意义了。
因此:内层的order by 语句单独使用,仅排序,在执行期间就被MySQL的代码分析器给优化掉了;内层的order by 必须能够影响结果集时,才有意义。比如:配合limit 使用,如下例。
# 查出第三个栏目下,价格前三高的商品和第四个栏目下价格前两高的商品。
(select goods_id,cat_id,goods_name,shop_price
from goods
where cat_id=3 order by shop_price desc limit 3)
union
(select goods_id,cat_id,goods_name,shop_price
from goods
where cat_id=4 order by shop_price desc limit 2);
这次内层的order by 又好使了,因为有limit ,order by 能够实际影响结果集,有意义。
问:如果union后的结果有重复,即某两行或N行,所有的列,值都一样,怎么处理?
create table test1(
name varchar(20)
)engine myisam charset utf8;
create table test2(
name varchar(20)
)engine myisam charset utf8;
insert into test1 values ('a'),('b'),('c');
insert into test2 values ('b'),('c'),('d');
select * from test1
union select * from test2;
答:union默认去重了!
问:如果不想去重怎么办?
答:union all
面试例题:
两张表:
A表:
id | num |
---|---|
a | 5 |
b | 10 |
c | 15 |
d | 10 |
B表:
id | num |
---|---|
b | 5 |
c | 15 |
d | 20 |
e | 99 |
要求查询出以下效果:
id | num |
---|---|
a | 5 |
b | 15 |
c | 30 |
d | 30 |
e | 99 |
create table a(
id char(1),
num int
)engine myisam charset utf8;
create table b(
id char(1),
num int
)engine myisam charset utf8;
insert into a
values
('a',5),
('b',10),
('c',15),
('d',10);
insert into b
values
('b',5),
('c',15),
('d',20),
('e',99);
select * from a
union all
select * from b ;
# 因为当id为 c 时,两张表中所有的列都一样,去重了,必须用union all。
# 答案:
select id,sum(num) from
(select * from a union all select * from b)as tmp
group by id;