1.union查询就是把2条或者多条sql语句的查询结果,合并成一个结果集。
如:sql1: N行,sql2: M行,sql1 union sql2 ---> N+M行
2.union满足什么条件就可以用了?
只要结果集中的列数一致就可以.(如都是2列或者N列)
3.如果Union后的结果有重复(即某2行,或N行,所有的列,值都一样),怎么办?
这种情况是比较常见的,默认会去重.不想去重可以使用union all.
4. 取自于2张表,通过"别名"让2个结果集的列一致。那么,如果取出的结果集,列名字不一样,还能否union.
可以,而且取出的最终列名,以第1条sql为准 .
参考实例代码:
CREATE TABLE num_a (
id VARCHAR( 3 ) NOT NULL,
num INT(3 ) UNSIGNED NOT NULL
)CHARSET utf8 ENGINE MYISAM;
CREATE TABLE num_b (
id VARCHAR( 3 ) NOT NULL,
num INT(3 ) UNSIGNED NOT NULL
)CHARSET utf8 ENGINE MYISAM;
INSERT INTO num_a VALUES ( 'a', 5 );
INSERT INTO num_a VALUES ( 'b', 10 );
INSERT INTO num_a VALUES ( 'c', 15 );
INSERT INTO num_a VALUES ( 'd', 10 );
INSERT INTO num_b VALUES ( 'b', 5 );
INSERT INTO num_b VALUES ( 'c', 15 );
INSERT INTO num_b VALUES ( 'd', 20 );
INSERT INTO num_b VALUES ( 'e', 99 );
1,union会去掉重复的行:
SELECT id,num FROM num_a UNION SELECT id, num FROM num_b
2、UNION ALL不会过滤重复的行
SELECT id,num FROM num_a UNION ALL SELECT id, num FROM num_b
3、order by对union后的结果集排序
SELECT id,num FROM num_a UNION SELECT id, num FROM num_b ORDER BY num DESC
4、把num_a和num_b不同的索引结果保留, 相同的索引结果相加 然后输出:
SELECT a.id, ( a.num + b.num ) AS num FROM num_a AS a INNER JOIN num_b AS b ON a.id = b.id
UNION ALL
SELECT * FROM num_a AS a WHERE NOT EXISTS( SELECT * FROM num_b AS b WHERE a.id = b.id )
UNION ALL
SELECT * FROM num_b AS b WHERE NOT EXISTS( SELECT * FROM num_a AS a WHERE a.id = b.id )
ORDER BY id ASC