mysql中的union用法

mysql中的union用法,第1张

UNION在mysql中被称为集合 *** 作, *** 作类型分为两种:UNION DISTINCT 和  UNION ALL;注意:UNION和UNION DISTINCT是一样的功能。UNION功能为合并多个查询的结果并去重,UNION ALL的功能为合并多个查询的结果不去重。

集合 *** 作时,两边的输入必须拥有相同的列数,如果数据类型不同的话,mysql会自动进行隐式转化 ;同时,结果列的名称由第一个查询的列的名称决定。下面看一下例子:

sql语句为:SELECT 'abc' as o,'haha' as p,4 as q FROM DUAL UNION SELECT 'abc' as a,'haha',4 FROM DUAL结果是:

如果是:SELECT 'abc' as o,'haha' as p,4 as q FROM DUAL UNION ALL SELECT 'abc' as a,'haha',4 FROM DUAL

如果两次查询的列个数不一致,如:SELECT 'abc' as o,'haha' as p,4 as q FROM DUAL UNION ALL SELECT 'abc' as a,'haha' FROM DUAL

在多个列查询时,只要有一个列的数据不一致时,都是无法去重的;也就是去重是每个列的数据完全一致,比如:SELECT 'abc' as o,'haha' as p,4 as q FROM DUAL UNION DISTINCT SELECT 'abc' as a,'haha',5 FROM DUAL

这几个例子可以看出UNION (DISTINCT) 与 UNION ALL的用法了吧,下面讲一下UNION (DISTINCT)的实现方式:

一:创建一张虚拟表;

二:对这张虚拟表的列添加唯一索引,即UNIQUE INDEX

三:将结果插入虚拟表

四:返回虚拟表 

如何判断是否创建一个虚拟表,我们可以通过一下语句判断:

SHOW STATUS LIKE 'Created_tmp_tables' 

SELECT 'abc' as o,'haha' as p,4 as q FROM DUAL UNION SELECT 'abc' as a,'haha',4 FROM DUAL

SHOW STATUS LIKE 'Created_tmp_tables'                                                                                                            结果是:

可见结果3比结果1多1,在 *** 作第二个 UNION语句时创建了一个虚拟表;如果UNION创建了索引,插入会相对变慢

select * from aa

select * from bb

select * from aa union select * from bb

如果想使用ORDER BY或LIMIT子句来对全部UNION结果进行分类或限制,则应对单个地SELECT语句加圆括号,并把ORDER BY或LIMIT放到最后一个的后面

(select uid,name from aa) union (select uid,name from bb)ORDER BY uid desc limit 10两个SQL都得加上()。

select uid,name from (select uid,name from aa union select uid,name from bb) t order by uid desc LIMIT 10记得给 括号合并的表取上别名,否则报错 如下图

select uid,name from (select uid,name from aa union select uid,name from bb) order by uid desc LIMIT 10没取别名 t 报错

select uid,group_concat(name )from (select uid,name from aa union select uid,name from bb) t group by uid order by uid desc LIMIT 10

select uid,name from (select uid,name from aa union select uid,name from bb) t group by uid order by uid desc LIMIT 10

运行上面那个SQL会报错,因为5.7版本之后的MYSQL不在group by里的字段 跟在select会报错解决办法是,用函数 any_value(字段名)

select any_value(name),max(uid)from (select uid,name from aa union select uid,name from bb) t group by uid order by uid desc LIMIT 10

运行上面的SQL 不报错了

最近在做报表统计的时候碰到一个诡异的bug,union左边查出来有4条数据,右边是0条,理论上最后的结果有4条,但是执行结果很意外,只有三条。最后的解决办法是在查询数据列加上了各自的报表时间。

原始sql:

改正后:

union在做一些数据合并统计的时候很有用,但稍不注意就会踩坑导致数据丢失统计出错。

使用union时一定要注意union自带了去重功能,而且机制类似于 把左右两边的数据完全做完合并再来一个distinct,所以一旦有两行的数据一模一样时,union会去掉这些重复行,即使这些重复行只是存在于其中一个结果集的

union all的机制类似于把左右两边的数据完全做完合并,并且不会做去重。虽然可以使用union all就不会做去重,但是试想一下这个需求:如果我们需要把左结果集和右结果集的数据做合并,但是左结果集和右结果集存在一些重复,这种重复数据是应该去掉的,而左结果集和右结果集自身存在的重复行是应该保留的,因为我们的目的并不是在每个结果集做去重,而是保证union的左边数据不和右边数据重复。建议认真考虑使用场景再决定是否使用union all。

union去重时去掉的重复数据如果是我们需要保留的,因为他们并不是来自于同一行,只是因为值完全一致而被去掉了,那么应该把这些数据的唯一标志也放在查询列,这样就不是重复数据了。


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/8536064.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-04-17
下一篇 2023-04-17

发表评论

登录后才能评论

评论列表(0条)

保存