嵌套查询中子查询使用Mapper上使用@Param注解传递的参数

最新推荐文章于 2024-03-01 15:10:20 发布

无聊啊无聊啊啊啊啊啊啊

最新推荐文章于 2024-03-01 15:10:20 发布

阅读量954

点赞数 2

分类专栏：开发遇到的问题文章标签： java mysql

本文链接：https://blog.csdn.net/i_have_a_girlfriend/article/details/117081006

版权

开发遇到的问题专栏收录该内容

1 篇文章 0 订阅

订阅专栏

先说解决办法：在xml中只能传递是cloumn的值，所以把值变成虚拟列

下面是我的代码

mapper接口，这个查询数据库的接口有两个参数，第一个在最外面的查询用，第二个在子查询当中用

List<RequisitionsHistoryVO> requisitionsByDepartment(@Param("companyId") String companyId, @Param("period") Integer period);

下面是xml

映射，column="{department_id=department_id,period=period}这儿在传参等号右边是外层查询传递的列，左边是子查询接收变量的名，分不清的话都弄成一样的就行

<resultMap id="historyVo" type="com.xm.assets.vo.manage.RequisitionsHistoryVO">
        <result property="departmentId" column="department_id"/>
        <result property="title" column="title"/>
        <collection property="requisitionsMonthHistoryList" select="monthDetail"
                    column="{department_id=department_id,period=period}"
                    ofType="com.xm.assets.vo.manage.RequisitionsMonthHistoryVO">
            <result property="monthNum" column="month_num"/>
            <result property="requisitionsCount" column="requisitions_count"/>
        </collection>
    </resultMap>

最外层查询，使用case when把requisitionsByDepartment()接口传递的参数当成一列
或者

SELECT
	id AS department_id,
	title,
	#{period}  AS period
	
FROM
	sys_department 
WHERE
	company_id = #{companyId} AND del_flag = 0

<select id="requisitionsByDepartment" resultMap="historyVo">
        SELECT
            id AS department_id,
            title,
            CASE WHEN (#{period} IS NULL) THEN 1 else #{period} END AS period
        FROM
            sys_department
        WHERE
            company_id = #{companyId} AND del_flag = 0
    </select>

子查询

子查询中就可使用period这个参数了

    <select id="monthDetail" resultType="com.xm.assets.vo.manage.RequisitionsMonthHistoryVO">
        SELECT
            sum( requisitions_count ) AS requisitions_count,
            MONTH( create_time) AS month_num,
            DATE_FORMAT( create_time,'%Y-%m') AS year_month_num
        FROM
        ( SELECT requisitions_count, create_time, CURRENT_DATE ( ) AS cur FROM requisitions_history WHERE department_id = #{department_id} AND del_flag = 0 ) AS p
        WHERE
          <if test="period == 1">
              <![CDATA[create_time >= concat(DATE_FORMAT(DATE_SUB(cur,INTERVAL 11 MONTH),'%Y-%m'),'-01')]]>
              AND <![CDATA[create_time <= LAST_DAY(cur)]]>
          </if>
          <if test="period == 2">
              <![CDATA[create_time >= concat(DATE_FORMAT(DATE_SUB(cur,INTERVAL 5 MONTH),'%Y-%m'),'-01')]]>
              AND <![CDATA[create_time <= LAST_DAY(cur)]]>
          </if>
          <if test="period == 3">
              <![CDATA[create_time >= concat(DATE_FORMAT(DATE_SUB(cur,INTERVAL 2 MONTH),'%Y-%m'),'-01')]]>
              AND <![CDATA[create_time <= LAST_DAY(cur)]]>
          </if>
        GROUP BY
            year_month_num
        ORDER BY
            year_month_num
    </select>