如下先創建2個表，aa bb.

CREATE table aa(

uid int(20) not null,

name VARCHAR(30) not null

)engine=innodb default charset=utf8mb4 COLLATE utf8mb4_general_ci;

INSERT INTO `aa`(`uid`, `name`) VALUES (10, '張芳');

INSERT INTO `aa`(`uid`, `name`) VALUES (11, '王凱');

INSERT INTO `aa`(`uid`, `name`) VALUES (12, '張學友');

INSERT INTO `aa`(`uid`, `name`) VALUES (13, '陳真');

INSERT INTO `aa`(`uid`, `name`) VALUES (14, '向佳');

INSERT INTO `aa`(`uid`, `name`) VALUES (15, '徐州');

CREATE table bb(

uid int(20) not null,

name VARCHAR(30) not null

)engine=innodb default charset=utf8mb4 COLLATE utf8mb4_general_ci;

INSERT INTO `bb`(`uid`, `name`) VALUES (110, '王海川');

INSERT INTO `bb`(`uid`, `name`) VALUES (111, '任海波');

INSERT INTO `bb`(`uid`, `name`) VALUES (121, '陳海杰');

INSERT INTO `bb`(`uid`, `name`) VALUES (131, '陳勇');

INSERT INTO `bb`(`uid`, `name`) VALUES (141, '獨孤無敵');

INSERT INTO `bb`(`uid`, `name`) VALUES (151, '陳磊');

INSERT INTO `bb`(`uid`, `name`) VALUES (155, '采花大盜');

UNION 操作符用于合并兩個或多個 SELECT 語句的結果集。UNION 內部的 SELECT 語句必須擁有相同數量的列。列也必須擁有相似的數據類型。

union 是對數據進行并集操作，不包括重復行，同時進行默認排序

從效率上說，UNION ALL 要比UNION快很多,Union all 是對數據進行并集操作，包括重復行，不進行排序

select * from aa;

image.png

select * from bb;

image.png

aa表和bb表合并查詢結果

select * from aa union select * from bb;

image.png

如果想使用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都得加上()。

image.png

或者如下寫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 報錯

image.png

如果用到分組。

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;

image.png

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;

image.png

運行上面那個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 不報錯了

image.png

any_value(字段名)允許，非分組字段的出現