sql - mybatis的trim标签?

Question

写了一个select的检索，但是一直提示报错:错误主要是多了一个where
报错信息

ERROR c.a.d.f.s.StatFilter - [mergeSql,150] - merge sql error, dbType mysql, druid-1.2.16, sql : select count(0) from ( 
select ps.unit,ps.serialNumber,ps.batchCode,ps.pnCode,ps.materialCode,ps.pn_information,ps.specificationType,ps.assetClass,
        pu.storageType ,pu.name ,pu.department ,pu.create_time, pu.storageRoom ,pu.remark,sum(numbersRuKu) as numbersRuKu
        from pnassertrukudetails ps left join pnassertruku pu on ps.parent_pnAssertRuku_id = pu.child_pnAssertRukuDetails_id
         **where** group by materialCode
 ) tmp_count

但是我用了trim,还是报同样的错误:
写的sql

<select id="getKuCunInformation" parameterType="com.ruoyi.system.domain.PnModelInOutInventory" resultMap="result">
        select ps.unit,ps.serialNumber,ps.batchCode,ps.pnCode,ps.materialCode,ps.pn_information,ps.specificationType,ps.assetClass,
        pu.storageType ,pu.name ,pu.department ,pu.create_time, pu.storageRoom ,pu.remark,sum(numbersRuKu) as numbersRuKu
        from pnassertrukudetails ps left join pnassertruku pu on ps.parent_pnAssertRuku_id = pu.child_pnAssertRukuDetails_id
        <trim prefix="where" suffixOverrides="AND |OR">
            <if test="materialCode == null or materialCode == '' ">
                <if test="pnCode == null or pnCode == '' ">
                    <if test="storageType == null or storageType == '' ">
                        <if test="storageRoom == null or storageRoom == '' ">
                            <if test="createTime == null or createTime == '' ">
                                group by materialCode and
                            </if>
                        </if>
                    </if>
                </if>
            </if>

            <if test="materialCode != null and materialCode != '' ">
                materialCode = #{materialCode} and
            </if>
            <if test="pnCode != null and pnCode != '' ">
                pnCode = #{pnCode} and
            </if>
            <if test="storageType != null and storageType != '' ">
                storageType = #{storageType} and
            </if>
            <if test="storageRoom != null and storageRoom != '' ">
                storageRoom = #{storageRoom} and
            </if>
            <if test="assetClass != null and assetClass != '' ">
                assetClass = #{assetClass} and
            </if>
            <if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
                date_format(create_time,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d') and
            </if>
            <if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
                date_format(create_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d') and
            </if>
        </trim>
    </select>

Answer 1

有没有可能是你的逻辑错误,<trim></trim>的作用是，如果标签内有任意一个条件命中，则添加prefix，你的是where,另外去除多余的suffixOverrides,你是AND或者OR,现在是其它<if>没有命中，但是group by materialCode and命中了,命中后去除最后的and,在最前面加where
我没有看懂你要拼接什么样的SQL,你的if到底是where条件，还是追加到group by之后

猜测你的意图如下：

<select id="getKuCunInformation" parameterType="com.ruoyi.system.domain.PnModelInOutInventory" resultMap="result">
    select ps.unit, ps.serialNumber, ps.batchCode, ps.pnCode, ps.materialCode, ps.pn_information, 
           ps.specificationType, ps.assetClass, pu.storageType ,pu.name ,pu.department ,
           pu.create_time, pu.storageRoom ,pu.remark,sum(numbersRuKu) as numbersRuKu
    from pnassertrukudetails ps
    left join pnassertruku pu on ps.parent_pnAssertRuku_id = pu.child_pnAssertRukuDetails_id
    <where>
        </if test="materialCode != null and materialCode != '' ">
            materialCode = #{materialCode}
        </if>
        <if test="pnCode != null and pnCode != '' ">
            and pnCode = #{pnCode}
        </if>
        <if test="storageType != null and storageType != '' ">
            and storageType = #{storageType}
        </if>
        <if test="storageRoom != null and storageRoom != '' ">
            and storageRoom = #{storageRoom}
        </if>
        <if test="assetClass != null and assetClass != '' ">
            and assetClass = #{assetClass}
        </if>
        <if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
            and date_format(create_time,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
        </if>
        <if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
            and date_format(create_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
        </if>
    </where>
    <if test="materialCode == null or materialCode == '' ">
        <if test="pnCode == null or pnCode == '' ">
            <if test="storageType == null or storageType == '' ">
                <if test="storageRoom == null or storageRoom == '' ">
                    <if test="createTime == null or createTime == '' ">
                        group by materialCode
                    </if>
                </if>
            </if>
        </if>
    </if>
</select>