A表1对1关联C表，表1对1关联C表 C表根据字段type、fId关联A\B表 查询C表及关联的A\B表信息
<select id="queryByPage" resultType="com.mdsapp.ecs.module.manage.entity.FlowCardEntity">
SELECT fc.*, temp.deviceName, temp.deviceNo
FROM flow_card fc
LEFT JOIN (
SELECT fc.id, d.name AS deviceName, d.sip_username AS deviceNo FROM flow_card fc INNER JOIN device d ON d.id = fc.device_id AND fc.device_type = 0
UNION
SELECT fc.id, b.name AS deviceName, b.`code` AS deviceNo FROM flow_card fc INNER JOIN beacon b ON b.id = fc.device_id AND fc.device_type = 1
UNION
SELECT fc.id, t.name AS deviceName, t.mac AS deviceNo FROM flow_card fc INNER JOIN terminal t ON t.id = fc.device_id AND fc.device_type = 2
UNION
SELECT fc.id, wd.name AS deviceName, wd.device_no AS deviceNo FROM flow_card fc INNER JOIN wait_device wd ON wd.id = fc.device_id AND fc.device_type = 3
) temp ON temp.id = fc.id
<where>
<if test="companyId != null and companyId !=''">
AND fc.company_id = #{companyId}
</if>
<if test="deviceType != null and deviceType !=''">
AND fc.device_type = #{deviceType}
</if>
<if test="status != null and status !=''">
AND fc.status = #{status}
</if>
<if test="keyword != null and keyword !=''">
AND (temp.deviceName like CONCAT( #{keyword}, '%') or
fc.phone like CONCAT('%', #{keyword}, '%') or
fc.imsi like CONCAT('%', #{keyword}, '%')
)
</if>
</where>
ORDER BY fc.id
</select>
<select id="queryById" resultType="com.mdsapp.ecs.module.manage.entity.FlowCardEntity">
SELECT fc.*,
CASE device_type
WHEN 0 THEN
(SELECT name FROM device WHERE fc.device_id = id)
WHEN 1 THEN
(SELECT name FROM beacon WHERE fc.device_id = id)
WHEN 2 THEN
(SELECT name FROM terminal WHERE fc.device_id = id)
WHEN 3 THEN
(SELECT name FROM wait_device WHERE fc.device_id = id)
END AS deviceName,
CASE device_type
WHEN 0 THEN
(SELECT sip_username FROM device WHERE fc.device_id = id)
WHEN 1 THEN
(SELECT `code` FROM beacon WHERE fc.device_id = id)
WHEN 2 THEN
(SELECT mac FROM terminal WHERE fc.device_id = id)
WHEN 3 THEN
(SELECT device_no FROM wait_device WHERE fc.device_id = id)
END AS deviceNo
FROM flow_card fc where fc.id = #{id};
</select>
