注意事项:
- GROUP_CONCAT(DISTINCT ssav.attr_value)为分组拼接函数,DISTINCT去重
注意:linux下不区分大小写,所以这儿起别名最好是下划线隔开,映射到实体类中也是没问题的
参考 - resultType:返回集合里面元素的类型
我们的返回类型比较复杂,所以我们需要封装自定义结果集:需要指定封装的类型
只要有嵌套属性,一定要封装自定义结果集(比如SkuItemVo.SkuItemAttrGroupVo这些内部类)
实体类:
@Data
public static class SkuItemAttrGroupVo {
private String groupName;
private List<SpuBaseAttrVo> attrs;
}
@Data
public static class SpuBaseAttrVo {
private String attrName;
private String attrValue;
}
<!-- 自定义结果集
type中用"$"指明内部类就行(亲测可以),不需要用”.“,也不需要提取出来
-->
<resultMap id="SkuItemAttrGroupVo" type="com.atguigu.gulimall.product.vo.SkuItemVo$SkuItemAttrGroupVo">
<result property="groupName" column="attr_group_name"/>
<collection property="attrs" ofType="com.atguigu.gulimall.product.vo.SkuItemVo$SpuBaseAttrVo">
<result property="attrName" column="attr_name"/>
<result property="attrValue" column="attrValue"/>
</collection>
</resultMap>
<!-- resultType:返回集合里面元素的类型
我们的返回类型比较复杂,所以我们需要封装自定义结果集:需要指定封装的类型
只要有嵌套属性,一定要封装自定义结果集(比如SkuItemVo.SkuItemAttrGroupVo这些内部类)
-->
<select id="getAttrGroupWithAttrsBySpuId" resultMap="SkuItemAttrGroupVo">
SELECT
pav.spu_id,
ag.attr_group_id,
ag.attr_group_name groupName,
aar.attr_id,
a.attr_name attrName,
pav.attr_value attrValue
FROM
pms_attr_group ag
LEFT JOIN pms_attr_attrgroup_relation aar ON aar.attr_group_id = ag.attr_group_id
LEFT JOIN pms_attr a ON a.attr_id = aar.attr_id
LEFT JOIN pms_product_attr_value pav ON pav.attr_id = aar.attr_id
WHERE
ag.catelog_id = #{catalogId}
AND pav.spu_id = #{spuId}
</select>
时间段查询
方法一:
<if test="knowledge.strDate != null">
AND `create_time` >= #{knowledge.strDate}
</if>
<if test="knowledge.endDate != null">
AND `create_time` <= #{knowledge.endDate}
</if>
方法二:
<if test="knowledge.strDate != null">
<![CDATA[AND `create_time` >= #{knowledge.strDate}]]>
</if>
<if test="knowledge.endDate != null">
<![CDATA[AND `create_time` >= #{knowledge.endDate }]]>
</if>
模糊查询
<!-- 使用concat(str1,str2)函数将两个参数连接 -->
<if test="knowledge.knowledgeName != null">
AND `knowledge_name` LIKE concat(concat("%",#{knowledge.knowledgeName}),"%")
</if>
将某字段的字符串类型转换为数值类型
<!-- a.answer_rate + 0 字符类型加一个数值0即可 -->
<select id="getAfterThree" parameterType="string" resultType="string">
SELECT
b.dept_name
FROM
biz_data_counts_detail a
LEFT JOIN blade_dept b ON a.dept_id = b.id
WHERE
a.is_deleted = 0
<if test="parentId !=null and parentId !=''">
AND a.parent_id = #{parentId}
</if>
ORDER BY
(a.answer_rate + 0) ASC
LIMIT 3
</select>
随机取3条数据
SELECT *
FROM `biz_knowledge`
WHERE id >=
((SELECT MAX(id) FROM `biz_knowledge`) - (SELECT MIN(id) FROM `biz_knowledge`)) * RAND() +
(SELECT MIN(id) FROM `biz_knowledge`)
LIMIT 3
查询是否符合某个条件,返回id即可
不需要返回整个对象
<select id="judgeExist" resultType="long">
SELECT
id
FROM
biz_temporary_meeting
WHERE
is_deleted = 0
<if test="temporaryMeeting.id != null and temporaryMeeting.id != ''">
AND id = #{temporaryMeeting.id}
</if>
<if test="temporaryMeeting.selectDateTime != null and temporaryMeeting.selectDateTime != ''">
<![CDATA[AND start_time <= CONCAT(#{temporaryMeeting.selectDateTime})]]>
<![CDATA[AND end_time >= CONCAT(#{temporaryMeeting.selectDateTime})]]>
</if>
</select>