今天遇见了一个需求就是将两个相同的数据表中的记录合并在一起,现在有一个message表和read_message表,两张表的结构是一样的,开始的消息会存入message表,如果用户查阅了该消息,这条消息就将从message中删除,插入到read_message表中,最后要拿到用户所有的message就必须把这两张表结合起来处理:
应用场景:1.最常见的是过程表与历史表UNION
2.相同数据表,来至不同数据源的UNION数据统计。
1.UNION (UNION ALL)操作符用于合并两个或多个 SELECT 语句的结果集.
使用条件:
1).子表中的column的数量必须相同,
例如:table1:(id, name, age,sex)
table2:(id, name)
SELECT * FROM table1 UNION SELECT * FROM table2 是会报错的。
解决方法可以空字符串来代替SELECT id, name , age FROM table1 UNION SELECT id, name ,"" FROM table2
2).UNION运算要求查询具有相同数目的字段.但是,字段数据类型不必相同
3).UNION的结果集以第一个SELECT的类名为准
4).子查询中有ORDER BY ,LIMIT等关键字,需要用()
例如 (SELECT * FROM table1 ORDER BY id) UNION (SELECT * FROM table2 ORDER BY name)
2.UNION 组合多个表(或结果集)并将其作为单个结果集返回;(将重复记录删除)
UNION ALL 在结果中包含所有的行,包括重复行。(包括重复行)
3.如何使用
1)UNION 使用语法:
<![CDATA[
SELECT columns FROM table1
UNION
SELECT columns FROM table2
]]>
这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。
2)UNION ALL 使用语法:
<![CDATA[
SELECT columns FROM table1
UNION ALL
SELECT columns FROM table2
]]>
UNION ALL只是简单的将两个结果合并后就返回。
4. 效率,UNION ALL要比UNION快很多。
原因:UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。UNION ALL只是简单的将两个结果合并后就返回。
5.我的应用场景的解决:
两张表的结构一样,一个是message,另一个表名是read_message
CREATE TABLE `message` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`to_user_id` int(11) DEFAULT NULL,
`from_user_id` int(11) DEFAULT NULL,
`title` varchar(100) DEFAULT NULL,
`body` text,
`entity_id` int(11) DEFAULT NULL,
`entity_type` varchar(45) DEFAULT NULL,
`created_time` datetime DEFAULT NULL,
`updated_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
);
一个老师给班上所有学上发送消息,现在是查找老师的所发送的所有信息:
<select id="findSentMessages" parameterType="map" resultMap="messageDTOMap">
<![CDATA[
SELECT
result.from_user_id AS from_user_id,
result.title AS title,
result.body AS body,
result.entity_id AS entity_id,
result.entity_type AS entity_type,
result.created_time AS created_time,
result.updated_time AS updated_time,
COUNT(DISTINCT result.created_time) AS count
FROM
(SELECT *
FROM message
UNION ALL
SELECT *
FROM read_message
) result
INNER JOIN class_section cs
ON
result.entity_id = cs.id
AND cs.is_deleted = 0
WHERE
result.entity_type = 'customMessage'
AND result.from_user_id = #{userId}
AND result.entity_id = #{classId}
GROUP BY
result.created_time ASC
]]>
</select>