Fuzzy Search, MySql MAX, GROUP BY and ORDER BY funtion

When you need a fuzzy seach funciton which only shares one search bar:

<select id="selectAllProducts" resultMap="ProductResult">
    select id, product_no, name, type, unit, brand, remark
    from product_info
    where 1=1
    <if test="(appSearch != null) and (appSearch neq '')">
        and (
        product_no like concat('%\\',#{appSearch},'%')
        or name like concat('%\\',#{appSearch},'%')
        or type like concat('%\\',#{appSearch},'%')
        )
    </if>
</select>

As far as the project goes, there are more requriements needed: join query with other tables and filter the latest data by create_time, return createTime to front end. Group by product number.

    <select id="selectAllProducts" resultMap="ProductResult">
        select * from
        (select a.id, a.product_no, a.name, a.type, a.unit, a.brand, a.remark, pi.product_sign as productSign,pl.name as locationName, max(pi.create_time) as createTime
        from product_info a
        left join product_inbound pi on a.product_no = pi.product_no
        left join product_location pl on pi.product_loc = pl.location
        where 1=1
        <if test="(appSearch != null) and (appSearch neq '')">
            and (
            a.product_no like concat('%\\',#{appSearch},'%')
            or a.name like concat('%\\',#{appSearch},'%')
            or a.type like concat('%\\',#{appSearch},'%')
            )
        </if>
        <if test="(productSign != null) and (productSign neq '')">
            and pi.product_sign = #{productSign}
        </if>
        GROUP BY pi.product_no
        )
        as temp
        ORDER BY temp.createTime desc

    </select>