高级难度sql语句（联表查询）

最新推荐文章于 2024-08-22 22:24:57 发布

原创最新推荐文章于 2024-08-22 22:24:57 发布 · 1.0k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #mysql #sql

本文探讨了如何使用SQL的innerjoin和leftjoin进行商品销售属性的精确匹配，包括等值连接和左联接的应用实例。重点介绍了Mybatis中如何处理多表联查，特别是处理返回双层集合的情况，以及如何通过resultMap进行数据映射。通过实例展示了如何根据用户选择标记商品属性，并在前端展示相关提示。

一、inner join(等值连接)

1、inner join(等值连接) 只返回两个表中联结字段相等的行。
单个条件，spuId为67（前端传过来），并且两表字段相等，返回符合条件的10行结果。
在这里插入图片描述
加多一个条件，销售属性sale_attr_id相等，过滤掉5行：

返回符合条件的5行结果：

2、下两张表，返回主外键246和247相等的，并且sku_id为122的数据（前端传过来）
在这里插入图片描述

二、left join(左联接)

3、left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
在符合上边两个条件的同时，再加符合以下两个条件：
（pms_product_sale_attr_value销售属性值表的主键id=pms_sku_sale_attr_value的sku商品的销售属性值外键）
（并且sku_id=122）
就可以过滤出来具体的sku数据了（颜色+版本）
在这里插入图片描述
4、a.*,b.*, 查a表和b表的所有*东西。

if(c.sku_id,1,0) as isChecked

加一个判断语句，如果c表的sku_id非空为1，空为0，列名：isChecked
去掉了其他不需要的列，保留想要的列即可。
为的是，用户点到了哪个商品的颜色、哪个商品的版本，只需连接一次数据库查询并返回结果显示给用户，提示用户点到的是哪个商品（作为一个提示作用！）
在这里插入图片描述

三、补全sql语句

5、sql语句代码（高级了啊！涉及的知识很多！！！）自己查资料，琢磨出来的！

select a.*,b.*,if(c.sku_id,1,0) as isChecked from pms_product_sale_attr a 
inner join pms_product_sale_attr_value b
on a.product_id=b.product_id and a.sale_attr_id=b.sale_attr_id and a.product_id=67
left join pms_sku_sale_attr_value c
on c.sale_attr_value_id=b.id and c.sku_id=119

（1）
美化sql语句：

SELECT
	a.id AS a_id,
	b.id AS b_id,
	a.*, b.*,
IF (c.sku_id, 1, 0) AS isChecked
FROM
	pms_product_sale_attr a
INNER JOIN pms_product_sale_attr_value b ON a.product_id = b.product_id
AND a.sale_attr_id = b.sale_attr_id
AND a.product_id = 67
LEFT JOIN pms_sku_sale_attr_value c ON b.id = c.sale_attr_value_id
AND c.sku_id = 121

这里不加排序，查到的数据是这样的：
在这里插入图片描述

（2）
这里加排序

SELECT
	a.id AS a_id,
	b.id AS b_id,
	a.*, b.*,
IF (c.sku_id, 1, 0) AS isChecked
FROM
	pms_product_sale_attr a
INNER JOIN pms_product_sale_attr_value b ON a.product_id = b.product_id
AND a.sale_attr_id = b.sale_attr_id
AND a.product_id = 67
LEFT JOIN pms_sku_sale_attr_value c ON b.id = c.sale_attr_value_id
AND c.sku_id = 121
ORDER BY b_id

查到的数据是这样的：
在这里插入图片描述
前端页面（颜色，版本先排序好。再根据用户点中哪个就选哪个，这样就比较人性化）：

四、mybatis的多表联查操作

5、写resource下的mapper包的mybatis文件
（1）正常的单层集合是这样写，但是这个sql语句返回的是双层集合，需要加映射关系！！！

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.atguigu.gmall.manage.service.mapper.PmsProductSaleAttrMapper">

    <select id="selectSpuSaleAttrListCheckBySku" resultType="com.atguigu.gmall.bean.PmsProductSaleAttr">
    SELECT a.*, b.*,
        IF (c.sku_id, 1, 0) AS isChecked
    FROM
        pms_product_sale_attr a
    INNER JOIN pms_product_sale_attr_value b ON a.product_id = b.product_id
        AND a.sale_attr_id = b.sale_attr_id
        AND a.product_id = #{spuId}
    LEFT JOIN pms_sku_sale_attr_value c ON c.sale_attr_value_id = b.id
        AND c.sku_id = #{skuId}
    ORDER BY b_id
    </select>
</mapper>

（2）这个是双层集合查询，需要添加映射关系resultMap，最终的xml文件代码：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--命名空间绑定mapper接口-->
<mapper namespace="com.atguigu.gmall.manage.service.mapper.PmsProductSaleAttrMapper">

    <!--查询语句的id为：mapper接口里的方法-->
    <!--结果是双层集合，需要使用标签resultMap，值为：mapper接口里的方法+Map-->
    <select id="selectSpuSaleAttrListCheckBySku" resultMap="selectSpuSaleAttrListCheckBySkuMap">
          <!--查询a表的id命名为a_id，b表的id命名为b_id-->
    SELECT a.id as a_id, b.id as b_id, a.*, b.*,
        IF (c.sku_id, 1, 0) AS isChecked
    FROM
        pms_product_sale_attr a
    INNER JOIN pms_product_sale_attr_value b ON a.product_id = b.product_id
        AND a.sale_attr_id = b.sale_attr_id
        AND a.product_id = #{spuId}
    LEFT JOIN pms_sku_sale_attr_value c ON b.id = c.sale_attr_value_id
        AND c.sku_id = #{skuId}
    ORDER BY b_id
    </select>

    <!--映射关系，id值为：resultMap的值-->
    <!--映射关系，type类型值为：外层集合对象的路径-->
    <!--加上autoMapping="true"，其他字段mybatis一 一对应，自动封装-->
    <resultMap id="selectSpuSaleAttrListCheckBySkuMap" type="com.atguigu.gmall.bean.PmsProductSaleAttr" autoMapping="true">
        <!--column是数据库映射主键a_id，对应property是外层实体类映射主键id-->
        <result column="a_id" property="id"></result>


        <!--property的值：内层集合（即是外层对象的列表）-->
        <!--oftype类型值：内层集合对象的路径-->
        <!--加上autoMapping="true"，其他字段mybatis一 一对应，自动封装-->
        <collection property="spuSaleAttrValueList" ofType="com.atguigu.gmall.bean.PmsProductSaleAttrValue" autoMapping="true">
            <!--column是数据库映射主键b_id，对应property是内层实体类映射主键id-->
            <result column="b_id" property="id"></result>
        </collection>

    </resultMap>

</mapper>

6、选中的红色和128G，这里的isChecked值为1就是你所选中的属性！
在这里插入图片描述

五、高级sql语句和简单的比较

正常查询销售属性列表（先点击sku，就可以获取到spuId，并且根据这个spuId查询所有该系列下的销售属性）：

SELECT
	*
FROM
	pms_product_sale_attr sa,
	pms_product_sale_attr_value sav
WHERE
	sa.product_id = sav.product_id
AND sa.sale_attr_id = sav.sale_attr_id
AND sa.product_id = 67

在这里插入图片描述
进阶版的查询销售属性（先点击sku，并且取得spuId，并且根据这个spuId查询所有该系列下的销售属性，并且定位到该sku的方框标红）：

SELECT
	a.id AS a_id,
	b.id AS b_id,
	a.*, b.*,
IF (c.sku_id, 1, 0) AS isChecked
FROM
	pms_product_sale_attr a
INNER JOIN pms_product_sale_attr_value b ON a.product_id = b.product_id
AND a.sale_attr_id = b.sale_attr_id
AND a.product_id = 67
LEFT JOIN pms_sku_sale_attr_value c ON b.id = c.sale_attr_value_id
AND c.sku_id = 122
ORDER BY b_id