<?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.changhu.module.policeManagement.mapper.PoliceEnterprisesUnitMapper">
    <select id="listSp" resultType="com.changhu.module.policeManagement.pojo.vo.ServiceProjectListVo">
        select sp.*,
               JSON_OBJECT('snowFlakeId', psu.snow_flake_id, 'name', psu.name, 'type', psu.type, 'unitId', psu.unitId,
                           'unitName', psu.unitName) as 'createUserInfo'
        from enterprises_unit eu
                 join service_project sp on eu.snow_flake_id = sp.enterprises_unit_id and sp.delete_flag = 0
                 left join mini_program_user mpu on sp.project_manager_mini_program_user_id = mpu.snow_flake_id
                 left join police_security_users psu on sp.create_by = psu.snow_flake_id
        where eu.delete_flag = 0
          and eu.snow_flake_id = #{enterprisesUnitId}
          and eu.police_unit_id = #{unitId}
        order by sp.create_time desc
    </select>
</mapper>