一。基础概念解释
定义:
在大多数开发中,使用一条SELECT查询就会返回一个结果集。如果,我们想一次性查询多条SQL语句,并将每一条SELECT查询的结果合并成一个结果集返回。就需要用到Union操作符,将多个SELECT语句组合起来,这种查询被称为并(Union)或者复合查询。
另外,在单表中使用Union比where多条件查询较为复杂。而从多张表中获取数据,使用Union会相对于简单些。
组合查询适用于下面两种情境中:
从多个表中查询出相似结构的数据,并且返回一个结果集
从单个表中多次SELECT查询,将结果合并成一个结果集返回。
Union使用规则
Union有他的强大之处,详细介绍之前,首先明确一下Union的使用注意规则。
Union必须由两条或者两条以上的SELECT语句组成,语句之间使用Union链接。
Union中的每个查询必须包含相同的列、表达式或者聚合函数,他们出现的顺序可以不一致(这里指查询字段相同,表不一定一样)
列的数据类型必须兼容,兼容的含义是必须是数据库可以隐含的转换他们的类型
包含重复、去除重复
union中mysql在查询结果集中帮我们自动去除了重复的行(重复的行是李四),把两条李四合并了。
select user_id,user_nickname,user_status from yy_user where user_status = 1
UNION
select user_id,user_nickname,user_status from yy_user where user_id > 3
一般情况下这样结果是好的,但是如果需要的情况下,我们可以使用Union All操作符来取消自动合并功能。
select user_id,user_nickname,user_status from yy_user where user_status = 1
UNION ALL
select user_id,user_nickname,user_status from yy_user where user_id > 3
结果排序
注意:由于在多表组合查询时候,可能表字段并不相同。所以,在对于结果集排序的时候需要使用检索出来的共同字段。
(select user_id,user_nickname,user_status from yy_user where user_status = 1)
UNION ALL
(select user_id,user_nickname,user_status from yy_user where user_id > 3)
order by user_id desc
**上面检索的字段user_id必须存在于结果集中。**
多表组合查询
大型项目中数据经常分布在不同的表,检索的时候需要组合查询出来。多表查询的时候,并不要求两个表完全相同,只需要你检索的字段结构相似就可以。
select posts_id,posts_name,posts_status from yy_posts
UNION
select user_id,user_nickname,user_status from yy_user
区分多表
上一个例子中,我们组合查询了user表和posts表。虽然结果混合在一起没有任何问题,但是当显示到页面的时候,我们需要给用户和文章不同的链接或者其他的区分。所以我们必须确定该条记录来自于哪张表,我们可以添加一个别名来作为表名。
select posts_id,posts_name,posts_status,'users' as table_name from yy_posts
UNION
select user_id,user_nickname,user_status,'posts' as table_name from yy_user
注意SQL语句中的'users' as table_name。对应的是图片里的table_name,就是我们刚刚添加用于区别表的字段。
二。项目中的实际应用
$db = $this->db;
$params = $request->getQueryParams();
$coupon = $db::table("coupon as c")
->select('c.id','c.coupon_head','c.min_money','c.max_money','c.coupon_type','c.allow_use_client',
'c.new_old_user','c.stock','c.start_time','c.end_time','c.created_time','c.coupon_type_text',
$db::raw("if(time_type='2',TIMESTAMPDIFF(day,date(c.created_time),c.start_time),'') as effective_time"),
$db::raw("if(time_type='2',TIMESTAMPDIFF(day,c.start_time,c.end_time),'') as finish_time"),
$db::raw("if(time_type='2','领取后n天有效,有效期x天',concat(c.start_time,'~',c.end_time))as time"),
$db::raw("concat(min_money,'~',max_money) as money"),
$db::raw("if(c.stock='0','不限制',stock) as stock_name"),
$db::raw("if(c.city_name='0','全国',city_name) as city"))
->where('c.is_delete','0');
$model = $db::table("delivery_coupon as cc")
->select('cc.id','cc.coupon_head','cc.min_money','cc.max_money','cc.coupon_type','cc.allow_use_client',
'cc.new_old_user','cc.stock','cc.start_time','cc.end_time','cc.created_time','cc.coupon_type_text',
$db::raw("if(cc.time_type='2',TIMESTAMPDIFF(day,date(cc.created_time),cc.start_time),'') as effective_time"),
$db::raw("if(cc.time_type='2',TIMESTAMPDIFF(day,cc.start_time,cc.end_time),'') as finish_time"),
$db::raw("if(cc.time_type='2','领取后n天有效,有效期x天',concat(cc.start_time,'~',cc.end_time))as time"),
$db::raw("concat(cc.min_money,'~',cc.max_money) as money"),
$db::raw("if(cc.stock='0','不限制',stock) as stock_name"),
$db::raw("if(cc.city_name='0','全国',city_name) as city"))
->where('cc.is_delete','0')
->unionAll($coupon)
->orderBy('created_time','desc');
$count = $db::selectOne("select count(*) as count_all from (" . $model->toSql() . ") as `count_all`",$model->getBindings())->count_all;
$coupon_all = $model
->skip(($params['page'] - 1) * $params['size'])->take($params['size'])
->get();
注意:
使用union all之后的sql语句类似于:
SELECT id FROM coupon UNION ALL SELECT id FROM delivery_coupon order by 'created_time','desc';
查询联查之后中的数据量:
select count(*) from (SELECT id FROM coupon UNION ALL SELECT id FROM delivery_coupon order by 'created_time','desc') as count;