Question

mysql union和union all

Answer 1

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 不报错了