SELECT
*
FROM
(
(
select a.ALARM_ID as id,
a.ALARM_ID as alarmId,
a.EBIKE_ID as ebikeId,
(select e.EBIKE_NO FROM at_ebike e where e.EBIKE_ID = a.EBIKE_ID) as ebikeNo,
a.STOLEN_ADDR as addr,
null as lat,--此表没有此字段,设为null
null as lng,
a.REMARK as remark,
null as ruleType,
a.CREATE_TIME as createTime,
'alarm' as alarmType --此表数据类型自定义为alarm
from at_alarm a where 1 = 1,
a.ORG_ID = '2018AeLCKojsjO', (a.ALARM_NAME like :keyword OR a.ALARM_PHONE like :keyword OR a.EBIKE_ID in(select EBIKE_ID from at_ebike where EBIKE_NO like :keyword) OR a.ALARM_USER_ID IN(SELECT USER_ID FROM at_user WHERE ID_NO LIKE :keyword))
)
UNION ALL
(
select l.ALARM_LOG_ID as id,
l.ALARM_ID as alarmId,
l.EBIKE_ID as ebikeId,
l.EBIKE_NO AS ebikeNo,
l.ADDR as addr,
l.LAT as lat,
l.LNG as lng,
null as remark,
null as ruleType,
l.CREATE_TIME as createTime,
'move' as alarmType--此表数据类型自定义为move
from at_alarm_log l where 1=1,
l.ORG_ID = '2018AeLCKojsjO'
)
UNION ALL
(
select e.ALARM_LOG_ID as id,
null as alarmId,
e.EBIKE_ID as ebikeId,
e.EBIKE_NO AS ebikeNo,
e.ADDR as addr,
e.LAT as lat,
e.LNG as lng,
null as remark,
e.RULE_TYPE as ruleType,
e.CREATE_TIME as createTime,
'enclosure' as alarmType
from at_enclosure_alarm_log e where 1=1,
e.ORG_ID = '2018AeLCKojsjO'
)
) AS alerm_and_exception_log ORDER BY createTime
MySql同时查询三个不关联的表,同时获取到三个表的数据,并按照某一字段排序
最新推荐文章于 2024-06-18 08:41:53 发布