1.获得该类下商品的所有品牌

数据库及流程分析

要获得品牌就要有product_id ,而product_id 是product表中的数据

sql语句如下：

SELECT disticnt brand FROM product_params WHERE product_id IN(SELECT product_id FROM product)

distinct是为了去重，显然不同商品一个牌子也是存在的

剩下的mapper,service,controller层过于简单，不做赘述

2.获得该品牌下的所有商品

2.1 获得商品的id

sql语句实现(以分类id=13举例)
SELECT p.product_id, p.product_name,p.category_id,p.root_category_id,p.sold_num,p.product_status,p.content, pa.param_id,pa.product_id,pa.product_place,pa.foot_period,pa.brand
FROM product p INNER JOIN product_params pa ON pa.product_id = p.product_id WHERE p.category_id = 13
AND pa.brand = "酒鬼"

2.2获得商品id下的所有套餐中sellPrice最低的套餐

SELECT * FROM product_sku WHERE product_id =19 ORDER BY sell_price LIMIT 0,1

3.后端实现

productVo 新增了品牌这一项

productMapper

    //根据三级分类id分页商品的brand 和category_id获得商品id,
    //根据商品id获得商品的所有套餐，选择套餐中sellprice最低的那个 还需要当前页的起始索引，当前页的查询记录数
public List<ProductVo> selectProductInfoByBrandsAndCIds(@Param("cid")int cid,@Param("brand") String brand,@Param("start") int start, @Param("pagesize") int pagesize);

productMapper.xml 依然是根据categoryid和品牌得到productId,然后用productId进行子连接

    <id column="product_id" jdbcType="VARCHAR" property="productId" />
    <result column="product_name" jdbcType="VARCHAR" property="productName" />
    <result column="category_id" jdbcType="INTEGER" property="categoryId" />
    <result column="root_category_id" jdbcType="INTEGER" property="rootCategoryId" />
    <result column="sold_num" jdbcType="INTEGER" property="soldNum" />
    <result column="product_status" jdbcType="INTEGER" property="productStatus" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    <result column="content" jdbcType="LONGVARCHAR" property="content" />
    <collection property="paramsList" ofType="com.qfedu.fmmall.entity.ProductParams" >
      <id column="param_id" jdbcType="VARCHAR" property="paramId" />
      <result column="product_id" jdbcType="VARCHAR" property="productId" />
      <result column="product_place" jdbcType="VARCHAR" property="productPlace" />
      <result column="foot_period" jdbcType="VARCHAR" property="footPeriod" />
      <result column="brand" jdbcType="VARCHAR" property="brand" />
    </collection>
    <!--根据商品id查询sellprice最低的套餐-->
    <collection property="skuList"
     select="com.qfedu.fmmall.dao.ProductSkuMapper.getLowerPriceByProductId"
     column="product_id"/>
  </resultMap>
  <select id="selectProductInfoByBrandsAndCIds" resultMap="ProductMap3">
    SELECT
      p.product_id,
      p.product_name,
      p.category_id,
      p.root_category_id,
      p.sold_num,
      p.product_status,
      p.content,
      pa.param_id,
      pa.product_id,pa.product_place,pa.foot_period,pa.brand
    FROM product p
    INNER JOIN product_params pa
    ON pa.product_id=p.product_id
    WHERE p.category_id =#{cid}
      AND pa.brand=#{brand}
    limit #{start},#{pagesize}
  </select>

productService

    //传参数还是当前的页码，每页最多由多少项数据
    public ResultVo getProductInfoByCidAndBrands(int categoryid,String brand,
                                                 int pageNum,int limit);

productServiceImpl

    public ResultVo getProductInfoByCidAndBrands(int categoryid, String brand, int pageNum, int limit) {
        //1.获得当前页的起始索引
        int start=(pageNum-1)*limit;
        List<ProductVo> productVos1 = productMapper.
                selectProductInfoByBrandsAndCIds(categoryid, brand, start, limit);
        //2.获得该条件下categoryAndProductId的总记录数
        int count=productVos1.size();
        //3.计算总页数
        int pageCount1=0;
        if(count%limit==0) pageCount1=count/limit;
        else pageCount1=count/limit +1;
        //把数据放进pageHelper里
        PageHelper<ProductVo> productVoPageHelper = new PageHelper<>(count, pageCount1, productVos1);
        return new ResultVo(ResStatus.OK,"success",productVoPageHelper);
    }

productController

@ApiOperation("根据商品的分类id和品牌来查询商品信息的接口")
    @GetMapping("/listByCidAndBrand/{cid}")
    //前端要传给后端4个参数,分类id，品牌， 哪一页的评论 该页有多少评论
    @ApiImplicitParams({
            @ApiImplicitParam(dataType = "int", name="pageNum",value="当前的页码数",required = true),
            @ApiImplicitParam(dataType = "int", name = "limit", value="每页最多显示多少条数据",required =true)
    })
    public ResultVo getProductByCidSAndBrands(@PathVariable("cid") int cid,String brand,int pageNum,int limit){
        ResultVo resultVo = productService.getProductInfoByCidAndBrands(cid, brand,pageNum, limit);
        return resultVo;
    }

4.前端实现

![](https://www.icode9.com/i/l/?n=22&i=blog/1859559/202201/1859559-20220127223038990-1895270164.png)