经典sql

<?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>