SQL命令--合并查询union

时间：2020-02-18 11:50:44 收藏：0 阅读：60

union命令作用：连接两个以上的 SELECT 语句的结果组合到一个表中

一. 示例：

select id, url, auth_status as authStatus, enterprise_name as enterpriseName, establish_time as establishTime, ‘node‘ as  type, null as messageId, null as sender, created_time from node where node_role != ‘SELF‘
union
select id , null as url, null as authStatus, null as enterpriseName, null as establishTime, ‘message‘ as type, id as messageId, sender,created_time from message where `message_type`=‘INVITE‘ and handled=0  order by created_time desc

注意事项：

1. 两表字段长度需要一致

2. order by 排序的字段，需要在查询字段中包含

3. union 合并时默认去重，若不需要去重，可以使用union all

二.获取链表查询的数量总和

select sum(a.b) as num from (
select count() as b from table_1
union all
select count() as b from table_2
) as a（注意这里要取个别名）

原文：https://www.cnblogs.com/yishilin/p/12325429.html