首先先把表格式和数据贴出来
第一张表的结构和数据
#第一张表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for test_tmptable_01
-- ----------------------------
DROP TABLE IF EXISTS `test_tmptable_01`;
CREATE TABLE `test_tmptable_01` (
`id_01` int(11) NOT NULL,
`name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id_01`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of test_tmptable_01
-- ----------------------------
INSERT INTO `test_tmptable_01` VALUES (1, '名字1');
INSERT INTO `test_tmptable_01` VALUES (2, '名字2');
INSERT INTO `test_tmptable_01` VALUES (3, '名字3');
INSERT INTO `test_tmptable_01` VALUES (4, '名字4');
SET FOREIGN_KEY_CHECKS = 1;
第二张表的结构和数据
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for test_tmptable_02
-- ----------------------------
DROP TABLE IF EXISTS `test_tmptable_02`;
CREATE TABLE `test_tmptable_02` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`age` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of test_tmptable_02
-- ----------------------------
INSERT INTO `test_tmptable_02` VALUES (3, '名字3_2', 23);
INSERT INTO `test_tmptable_02` VALUES (4, '名字4', 24);
INSERT INTO `test_tmptable_02` VALUES (5, '名字5', 25);
SET FOREIGN_KEY_CHECKS = 1;
数据图示:
我们先来看看正常的UNION ALL语句出来的结果:
select * from(
select id_01,name from test_tmptable_01 UNION ALL SELECT id,name from test_tmptable_02
) a
可以看到UNION ALL把两张子表的数据全部联合在了一起,不管是否有完全相同的数据数据行,还是只有其中部分列相同的数据行。
需要注意的是UNION ALL产生的子表必须有别名
全联合的个表筛选出来的字段数量必须一致否则报错
示例:
select id_01,name from ((select id_01,name from test_tmptable_01) UNION ALL (select id from test_tmptable_02)) a
报错信息:select id_01,name from ((select id_01,name from test_tmptable_01) UNION ALL (select id from test_tmptable_02)) a
> 1222 - The used SELECT statements have a different number of columns
> 时间: 0.047s
01表的id_01,name字段和01表的id字段进行全联合是不允许的。
在字段一致的基础上,字段的定义最好一致,但是可以不一致,但是不一致的情况会选择其中一种类型进行一致性转化,所有有些类型强转为其他类型会出现乱码,但是结果会出来
示例:
select id_01,name from ((select id_01,name from test_tmptable_01) UNION ALL (select id,age from test_tmptable_02)) a
以上SQL是能正常运行的,但是出来的结果会有所出入
我们是使用01表的id_01 int,name varchar和02表的id int,age int进行全联合。
我们发现01表的name列中的中文出现了乱码,也就是意味着整个终表最后的name子段,其实是有01表的name字段和02表的age字段组合而成,但是最终从结果来看,整个格式是以整形int优先转化,笔者也尝试过吧01,02表的联合顺序进行调换,但是结果还是01表的中文乱码,所以我们可以认为在全联合时,不同类型字段的联合,在varcahr和int两个类型的转换,会优先进行int的转换
全联合后的别名表的字段名字,以顺位第一张表的子段名字为准
select **id_01**,name from ((select **id_01**,name from test_tmptable_01) UNION ALL (select id,age from test_tmptable_02)) a
也就是我们最后筛选的子段名字,必须要和全联合表中的第一张子表(也就是01表)的字段名字相同;
同样的,加入我们将01表和01表的位置调换,那么最后select出来的字段就必须为 id,age