个人技术分享

mysql UNION 联合查询

业务需要拉数据，这里需要对查询不同格式的数据进行组装，此处采用联合查询
注意1：null as '设备关爱' ，结果为null，表头为设备关爱
注意2： UNION 或者 UNION ALL 联合查询自行选用
注意3： id as '主用户ID' 所以ORDER BY 主用户ID ，不可以是ORDER BY '主用户ID'

sql代码图片

sql代码

-- 8-云关爱设备信息
(select tu.id as '主用户ID',ts.col_device_id as '账号关爱设备号', null as '设备关爱'  
from tb_user tu
INNER JOIN tb_track as tt on tt.col_user_id=tu.id and tt.col_track_type=1 and tt.col_is_delete=0
left JOIN tb_select as ts on ts.col_user_id = tt.col_tracked_user_id and ts.end_date is NULL
where tu.col_is_delete=0 and tu.col_app_id=33 and ts.col_device_id is not null    
) 
UNION  
(
select a.id as '主用户ID',NULL as '账号关爱设备号', b.col_device_id as '设备关爱' 
from tb_user a
INNER JOIN tb_device_care  b on a.id  = b.col_host_user_id and b.col_is_delete=0 and b.col_account_side_flag=0 
WHERE  a.col_is_delete=0 and a.col_app_id=33 
) 
ORDER BY 主用户ID

结果集