mysql union和union all

mysql union和union all

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

2022-06-27