关联查询一对多，可以一个实体有多个对多的属性，sql记录会随着关联增多，但是用了mybatis集合会自动合并

关联查询一对多，可以一个实体有多个对多的属性，sql记录会随着关联增多，但是用了mybatis集合会自动合并

left join是线性扩张还是星型扩张看，表之间的join关系，上述一个实体包含多个对多的属性实体，需用星型关连（后续关联项是一对一即可线性）

<resultMap id="templateCategoryProductVOResult" type="com.mcd.cn.rdc.pcm.product.business.vo.menu.MenuForNewVO">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="code" jdbcType="VARCHAR" property="productCode" />
    <result column="name" jdbcType="VARCHAR" property="productNameCn" />
    <result column="type" jdbcType="INTEGER" property="productType" />
    <result column="sales_flag" jdbcType="TINYINT" property="saleFlag" />
    <result column="ui_type" jdbcType="TINYINT" property="uiType" />
    <collection property="productTagList" resultMap="templateCategoryTagVOResult"></collection>
    <collection property="productPeriodList" resultMap="templateCategoryPeriodVOResult"></collection>
  </resultMap>
  <resultMap id="templateCategoryTagVOResult" type="com.mcd.cn.rdc.pcm.product.business.vo.menu.TagVO">
    <result property="tagCode" column="tab_code" />
    <result property="tagName" column="tab_name" />
    <result property="tagImage" column="tab_image" />
  </resultMap>
  <resultMap id="templateCategoryPeriodVOResult" type="com.mcd.cn.rdc.pcm.product.business.vo.product.ProductSalesPeriodVO">
    <result property="startDate" column="start_date" />
    <result property="endDate" column="end_date" />
  </resultMap>

  <select id="getTemplateCategoryProductListForNew" parameterType="com.mcd.cn.rdc.pcm.product.business.vo.template.TemplateParam"
          resultMap="templateCategoryProductVOResult">
    SELECT
    p.*, mst.code tab_code,mst.name tab_name,mst.image tab_image,tsp.start_date,tsp.end_date
    FROM
    t_template_category_product tcp
    LEFT JOIN t_product p ON tcp.data_version = p.data_version
    AND tcp.product_code = p. CODE
    LEFT JOIN t_template_product_tag tpt ON tcp.data_version = tpt.data_version
    AND tcp.template_id = tpt.template_id
    AND tcp.dimension_id = tpt.dimension_id
    AND tcp.category_id = tpt.category_id
    AND tcp.product_code = tpt.product_code
    LEFT JOIN t_master_sales_tag mst ON tpt.sales_tag_code = mst.code
    and mst.type = 2
        LEFT JOIN t_product_sales_period tsp ON  p. CODE=tsp.product_code AND  p.data_version=tsp.data_version
    WHERE
    tcp.data_version = #{dataVersion}
    AND tcp.template_id = #{templateId}
    AND tcp.dimension_id = #{dimensionId}
    AND tcp.category_id = #{categoryId}
    ORDER BY
    tcp.sort,
    mst.sort
  </select>

@Data
@ApiModel(description = "菜单信息根据类别")
@Accessors(chain = true)
public class MenuForNewVO {

    @ApiModelProperty(value = "商品id", required = true, example = "", position = 0)
    private Long id;

    @ApiModelProperty(value = "商品编码", required = true, example = "", position = 0)
    private String productCode;
    @ApiModelProperty(value = "菜单商品显示名称中文", required = true, notes = "如：麦麦脆汁鸡2块", position = 1)
    private String productNameCn;
    @ApiModelProperty(value = "菜单商品显示名称英文", notes = "如：McCrispy Chicken-2pcs", position = 2)
    private String productNameEn;
    @ApiModelProperty(value = "价格组redis存储key", required = true, notes = "redis存储key", example = "", position = 3)
    private String priceGroupInfo;

    @ApiModelProperty(value = "商品图片相对路径redis存储key", required = true, notes = "只有图片名称，图片路径需要前台配置redis存储key", example = "", position = 4)
    private String productImage;

    @ApiModelProperty(value = "菜品类别", required = true, notes = "1：单品 2：套餐 3：产品组", position = 11,allowableValues = "1,2,3")
    private String productType;
    @ApiModelProperty(value = "售卖标识", required = true, notes = "1:可卖,0:不可买,2:隐藏", position = 12,allowableValues = "0,1,2")
    private String saleFlag;
    @ApiModelProperty(value = "商品组的前端交互方式", required = true, notes = "1：单品到详情页；2：产品组到详情页", position = 13,allowableValues = "1,2")
    private String  uiType;
    @ApiModelProperty(value = "商品组对象", required = true, notes = "如果单品中存在特制属性，也需要在产品组对象中有值", example = "", position = 14)
    private List<ProductGroupVO> productGroupList;
    private List<TagVO> productTagList;

    private List<ProductSalesPeriodVO> productPeriodList;

    @JsonIgnore
    private String daypart;
    @JsonIgnore
    private String salesPeriod;
    @JsonIgnore
    private String salesTime;
}

 

 

 

参考：

https://blog.****.net/qq87034246/article/details/79878850