最终效果

查看数据库表涉及字段

编写SQL查询

SELECT
    i.id AS itemId,
    i.item_name AS itemName,
    i.sell_counts AS sellCounts,
    ii.url AS imgUrl,
    tempSpec.price_discount AS price
FROM
    items    i
LEFT JOIN
    items_img ii
    ON
    i.id = ii.item_id
LEFT JOIN
    (
        SELECT
            item_id,MIN(price_discount) AS price_discount
        FROM
            items_spec
        GROUP BY
            item_id
    ) tempSpec
ON
    i.id = tempSpec.item_id
WHERE
    ii.is_main = 1
AND
    i.on_off_status = 1
=======================================================================================
# 查询商品及图片
SELECT
    i.id AS itemId,
    i.item_name AS itemName,
    i.sell_counts AS sellCounts,
    ii.url AS imgUrl
FROM
    items    i
LEFT JOIN
    items_img ii
    ON
    i.id = ii.item_id
WHERE
    ii.is_main = 1
AND
    i.on_off_status = 1
# 查询商品最低价
SELECT
    item_id,MIN(price_discount) AS price_discount
FROM
    items_spec
GROUP BY
    item_id

编写mapper

<select id="searchItems" parameterType="Map" resultType="com.shiers.pojo.vo.SearchItemsVO">
        SELECT
            i.id AS itemId,
            i.item_name AS itemName,
            i.sell_counts AS sellCounts,
            ii.url AS imgUrl,
            tempSpec.price_discount AS price
        FROM
            items    i
        LEFT JOIN
            items_img ii
            ON
                i.id = ii.item_id
        LEFT JOIN
            (
                SELECT
                    item_id,MIN(price_discount) AS price_discount
                FROM
                    items_spec
                GROUP BY
                    item_id
            ) tempSpec
            ON
                i.id = tempSpec.item_id
        WHERE
            ii.is_main = 1
          AND
            i.on_off_status = 1
            <if test="paramsMap.keywords != null and paramsMap.keywords != '' ">
                🔥🔥🔥这里使用模糊查询，要对%进行拼接所以使用 ${} 而不是#🔥🔥🔥
                AND i.item_name LIKE '%${paramsMap.keywords}%'
            </if>
        ORDER BY
            <choose>
                🔥🔥🔥
                k:默认，代表默认排序，根据name
                c:根据销量排序
                p:根据价格排序
                🚨🚨🚨   这里的单引号需要转义为： &quot;   🚨🚨🚨
                🔥🔥🔥
                <when test=" paramsMap.sort == &quot;c&quot; ">
                    i.sell_counts desc
                </when>
                <when test=" paramsMap.sort == &quot;p&quot; ">
                    tempSpec.price_discount asc
                </when>
                <otherwise>
                    i.item_name asc
                </otherwise>
            </choose>
    </select>

编写service

@Transactional(propagation = Propagation.SUPPORTS)
@Override
public PagedGridResult searchItems(String keywords, String sort, Integer page, Integer pageSize) {
    Map<String, Object> map = new HashMap<>();
    map.put("keywords", keywords);
    map.put("sort", sort);
    /**
     * page:第几页
     * pageSize:每页显示条数
     */
    PageHelper.startPage(page, pageSize);
    List<SearchItemsVO> list = itemsMapperCustom.searchItems(map);
    return setterPagedGrid(list,page);
}

编写Controller

@ApiOperation(value = "搜索商品列表", notes = "搜索商品列表", httpMethod = "GET")
@GetMapping("/search")
public MyJSONResult search(
    @ApiParam(name = "keywords", value = "关键字", required = true)
    @RequestParam String keywords,
    @ApiParam(name = "sort", value = "排序", required = false)
    @RequestParam String sort,
    @ApiParam(name = "page", value = "查询下一页的第几页", required = false)
    @RequestParam Integer page,
    @ApiParam(name = "pageSize", value = "分页的每一页显示的条数", required = false)
    @RequestParam Integer pageSize) {
    if (StringUtils.isBlank(keywords)) {
        return MyJSONResult.errorMsg(null);
    }
    if (page == null) {
        page = 1;
    }
    if (pageSize == null) {
        pageSize = PAGE_SIZE;
    }
    PagedGridResult grid = itemService.searchItems(keywords, sort, page, pageSize);
    return MyJSONResult.ok(grid);
}