通知公告报错，原因是分页查询外部表字段不能有子查询，子查询需要在内部

最新推荐文章于 2022-08-20 14:45:54 发布

狼的独白

最新推荐文章于 2022-08-20 14:45:54 发布

阅读量511

点赞数

分类专栏： oa项目bug解决

本文链接：https://blog.csdn.net/lijunjie818/article/details/78914073

版权

oa项目bug解决专栏收录该内容

15 篇文章 0 订阅

订阅专栏

<select id="queryReceiveNoticeByPage_col">
			t.NOTICE_CONTENT,
            t.CREATE_USER,
			t.TYPE_NAME, 
            t.NOTICE_ID,
            t.NOTICE_TITLE,
            t.CREATE_DEPT,
            TO_CHAR(t.CREATETIME,'YYYY-MM-DD HH24:MI:SS') CREATETIME,
            TO_CHAR(t.ENDTIME,'YYYY-MM-DD HH24:MI:SS') ENDTIME,
            FUN_TR_USER(t.CREATE_USER,'ID_NAME') CREATE_USERNAME,
            FUN_TR_DEPT(t.CREATE_DEPT) CREATE_DEPTNAME,
	    错误写法： (select (case when count(notice_id)=0 then 'false' else 'true' end) from tbl_oa_notice_read where notice_id=n.NOTICE_ID and user_id=#{currentUser}) ISREAD

	   正确写法：t.ISREAD
	
</select>

<select id="queryReceiveNoticeByPage" resultMap="noticeMap" parameterType="map">
		select  distinct n.notice_id ,
				  n.notice_title,
				  n.notice_content,
				  n.endtime,
				  n.createtime,
				  n.create_user,
				  n.create_dept,
				  nt.type_name,
				 (select (case when count(notice_id)=0 then 'false' else 'true' end) from tbl_oa_notice_read where notice_id=n.NOTICE_ID and user_id=#{currentUser}) ISREAD
		  from tbl_oa_notice n
		  join tbl_oa_notice_type nt on n.notice_type=nt.type_id and nt.status='0'
		  <where>
		  	 n.create_dept = #{currentDeptTop}
		 		and n.notice_id in (select tg.notice_id
		                         from tbl_oa_notice_target tg
		                        where tg.target_id = #{currentUser}
		                          and tg.target_type = 'USER' AND tg.status = 0
		                        union all 
		                        select tg.notice_id
		                         from tbl_oa_notice_target tg
		                        where tg.target_id in (
		                        select dd.dept_id from tbl_base_dept dd WHERE dd.STATUS = '0' and ID_PATH like CONCAT(CONCAT('%,',#{currentDept}),',%')
		                          <!-- select dd.dept_id from tbl_base_dept dd start with dd.dept_id = #{currentDept} connect by prior dd.dept_parent_id = dd.dept_id  -->
		                        )
		                          and tg.target_type = 'DEPT' AND tg.status = 0
		                    )
		      and n.endtime >fun_base_sysdate()          
		      <if test="read!=null and read=='true'.toString()">
		        AND NOTICE_ID IN (select notice_id from tbl_oa_notice_read where user_id=#{currentUser})
		      </if>
		      <if test="read!=null and read=='false'.toString()">
		        AND NOTICE_ID NOT IN (select notice_id from tbl_oa_notice_read where user_id=#{currentUser})
		      </if>
		      <if test="noticeId!=null and noticeId!=''.toString()">
		      	AND NOTICE_ID = #{noticeId}
		      </if>
		      <if test="noticeTitle!=null and noticeTitle!=''.toString()">
		      	AND n.notice_title like CONCAT(CONCAT('%', #{noticeTitle}), '%')
		      </if>
		      <if test="endTimeStart!=null and endTimeStart!=''.toString()">
		      	AND n.endtime >=to_Date(#{endTimeStart},'YYYY-MM-DD HH24:MI:SS')
		      </if>
		      <if test="endTimeEnd!=null and endTimeEnd!=''.toString()">
		      	AND n.endtime <=to_Date(#{endTimeEnd},'YYYY-MM-DD HH24:MI:SS')
		      </if>
		      <if test="noticeType!=null and noticeType!=''.toString()">
		      	AND n.notice_type =#{noticeType}
		      </if>
		  </where>
		ORDER BY N.createtime DESC