<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.oics.modules.jobplan.devref.dao.PlanAccountInfoDao">
<select id="getPlanAccountInfo" parameterType="String" resultType="com.oics.modules.jobplan.devref.entity.PlanAccountInfo">
-- 设备组
select
distinct g.id AS id,0 AS pId,g.dgrp_name AS NAME,0 AS LEVEL,TRUE AS isParent,NULL AS fullname
from yw_account a
left join yw_device d on a.device_id = d.id
left join yw_device_group_rel gr on gr.device_id = d.id
left join yw_device_group g on g.id = gr.dgrp_id
where (a.id,d.id,g.id) in (
select ur.account_id,ur.device_id,ur.dgrp_id from yw_device_user_ref ur
where ur.user_id = #{userCode}
)
union all
-- 设备
select
distinct d.id AS id,gr.dgrp_id AS pId,d.device_name AS NAME,1 AS LEVEL,TRUE AS isParent,CONCAT(d.device_name,'(',d.device_domain,')') AS fullname
from yw_account a
left join yw_device d on a.device_id = d.id
left join yw_device_group_rel gr on gr.device_id = d.id
left join yw_device_group g on g.id = gr.dgrp_id
where (a.id,d.id,g.id) in (
select ur.account_id,ur.device_id,ur.dgrp_id from yw_device_user_ref ur
where ur.user_id = #{userCode}
)union all
-- 账号
select CONCAT(gr.dgrp_id,';',g.dgrp_name,';',a.device_id,';',d.device_name,';',d.device_domain,';',a.id,';',a.acct_name,';',a.acct_type) AS id,
a.device_id AS pId, CONCAT('', '', a.acct_name) AS NAME,2 AS LEVEL, FALSE AS isParent,NULL AS fullname
from yw_account a
left join yw_device d on a.device_id = d.id
left join yw_device_group_rel gr on gr.device_id = d.id
left join yw_device_group g on g.id = gr.dgrp_id
where (a.id,d.id,g.id) in (
select ur.account_id,ur.device_id,ur.dgrp_id from yw_device_user_ref ur
where ur.user_id = #{userCode}
)</select>
</mapper>
经典sql
于 2019-08-06 15:59:21 首次发布