Mapper.xml传入List返回List写法

原创已于 2023-08-28 13:48:16 修改 · 3.9k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#xml #数据库 #sql

于 2023-01-04 21:39:41 首次发布

该文章展示了一个使用Mapper接口和XML配置的SQL查询，用于从ims_pm_saleslist表中聚合销售数据。查询涉及到品牌和医院的统计，通过round函数进行数值四舍五入，cast转换数据类型，以及使用concat_ws进行字符串拼接。查询条件包括特定的月份列表和dm_position_code码。

Mapper

    List<SalesDataBoardResponse> selectList(@Param("list") List<String> month,String code);

XML


    <select id="selectList"  resultType="com.etl.appeal.application.response.SalesDataBoardResponse">
        select
            'DM' as dm_position_code,
            dm_name,
           <--round四舍五入函数，cast转换类型-->
            round(sum(cast("target_value" as numeric)),0) as target_value,
            round(sum(cast("actual_value" as numeric)),0) as actual_value ,
            round(sum(cast("ly_amount" as numeric)),0) as ly_amount,
            m.brand,
            n.hospital
        from
        ims_pm_saleslist,
        <--concat_ws拼接字符-->
        (select concat_ws('/',a,b) brand from (
            select count(*) a from (
                select distinct brand_name from ims_pm_saleslist group by brand_name,month,dm_position_code,actual_value
                having  month =  any ( array<foreach collection="list" item="month" open="[" close="]" separator=",">#{month}  </foreach>) and
                    dm_position_code = #{code} and actual_value &lt;&gt; '0') as o
            )c,
        (select count(*) b from (
            select distinct brand_name from ims_pm_saleslist group by brand_name,month,dm_position_code
            having month =  any ( array<foreach collection="list" item="month" open="[" close="]" separator=",">#{month}  </foreach>) and
                dm_position_code =#{code}) as o
            )d
        ) as m
        ,(select concat_ws('/',a,b) hospital from (
            select count(1) a from (
                select distinct customer_id from ims_pm_saleslist group by customer_id,month,dm_position_code,actual_value
                having month =  any ( array<foreach collection="list" item="month" open="[" close="]" separator=",">#{month}  </foreach>) and
                    dm_position_code =#{code} and actual_value &lt;&gt; '0') as o
            )c,
        (select count(1) b from (
            select distinct customer_id from ims_pm_saleslist group by customer_id,month,dm_position_code
            having month =  any ( array<foreach collection="list" item="month" open="[" close="]" separator=",">#{month}  </foreach>) and
                dm_position_code =#{code} ) as o
            )d
        )as n
        group by dm_position_code,dm_name,month,m.brand,n.hospital
        having
<!--        month = '${month}' -->
        month =  any (
            array<foreach collection="list" item="month" open="[" close="]" separator=",">#{month}  </foreach>
        )
<!--   可替换成 month in
        <foreach collection="list" item="month" open="(" close=")" separator=",">  #{month} </foreach> -->
        and dm_position_code =#{code}

    </select>