mysql关联查询出现的数据不准确问题

ゞ浴缸里的玫瑰 2022-03-25 11:40 602阅读 0赞

主要还是自己对sql语句的掌握程度太低了，还需要多多加强sql语句编写，最初我的语句是这样写的：

<select id="selOrdersByOid" parameterType="String" resultMap="selOrders">
             select     orders.aid as oaid ,orders.date as odate,orders.oid,orders.order_number,orders.state as ostate,
                        orders.total, orders.uid as ouid,
    					address.addr,address.aid,address.code,address.date as addrdate ,address.`name` as addrname,
    					address.province,address.region,address.state as addrstate,address.tel,address.uid as addruid,
    					order_item.order_item_id,order_item.pid as oipid,
                        order_item.count,order_item.sub_total,order_item.oid as oioid,order_item.date as oidate,
                        product.pid,product.state as pstate,product.pname,product.pdesc,product.date as pdate,
                        product.csid,product.market_price,product.shop_price,product.sales,product.stock,
                        image.image_id,image.name as imgname,image.old_name,image.path,
                        image.date as imgdate,image.pid as imgpid,image.state as imgstate
                    from    orders
    				INNER  JOIN address
    				on orders.aid = address.aid
    				LEFT JOIN order_item
    				on orders.oid = order_item.oid
                    left join product
                    on order_item.pid = product.pid
                    left join image
                    on image.pid = product.pid
                   <where>
                    <include refid="orderSql"/>
                    </where>
                 limit #{startIndex},#{pageSize}
        </select>
    
    //sql片段
     <sql id="orderSql">
            and orders.uid = #{ouid}
            <if test="ostate !=null and ostate != '' and ostate!= 0">
                and orders.state = #{ostate}
            </if>
        </sql>

也是无意间发现数据不准确，起初还没发现问题所在。。。。。

后来改了下sql：

<select id="selAllOrdersbByUid" parameterType="PageBean" resultMap="selOrdersByUser">
            SELECT * from
                (select  orders.aid as oaid ,orders.date as odate,orders.oid,orders.order_number,orders.state as ostate,
                orders.total, orders.uid as ouid
                from  orders
                <where>
                    <include refid="orderSql"/>
                </where>
                 limit #{startIndex},#{pageSize}) as ord
            LEFT JOIN
                (select   order_item.order_item_id,order_item.pid as oipid,
                order_item.count,order_item.sub_total,order_item.oid as oioid,order_item.date as oidate,
                product.pid,product.state as pstate,product.pname,product.pdesc,product.date as pdate,
                product.csid,product.market_price,product.shop_price,product.sales,product.stock,
                image.image_id,image.name as imgname,image.old_name,image.path,
                image.date as imgdate,image.pid as imgpid,image.state as imgstate
                from  order_item
                left join product
                on order_item.pid = product.pid
                left join image
                on image.pid = product.pid) as orderExt
    
                on ord.oid = orderExt.oioid
        </select>
    
    
    //sql片段
     <sql id="orderSql">
            and orders.uid = #{ouid}
            <if test="ostate !=null and ostate != '' and ostate!= 0">
                and orders.state = #{ostate}
            </if>
        </sql>

\#\#\#想实现的功能是订单的分页，每页显示12条订单数据

最初的写法导致了我每页显示的数据个数都不统一，多少个都有可能，但是我的分页算法是指明了的每页显示12条数据，这也是我发现问题的原因。。。  
分析：  
1，因为最初的写法是将所有的表进行关联了，关联查询后我进行了limit ，这时的关联查询会查出很多结果，毕竟订单下有订单项，订单项下有商品，商品下还要对应多张图片，。。。此时我进行的截取肯定也是查询出了这么多的结果集之后进行的，这时候在取12条数据也就是几个订单的结果，肯定不会是12个订单的结果了,x下面展示的是将最初的语句查询结果  
\#\#最初的查询语句：

select     orders.aid as oaid ,orders.date as odate,orders.oid,orders.order_number,orders.state as ostate,
                        orders.total, orders.uid as ouid,
    					address.addr,address.aid,address.code,address.date as addrdate ,address.`name` as addrname,
    					address.province,address.region,address.state as addrstate,address.tel,address.uid as addruid,
    					order_item.order_item_id,order_item.pid as oipid,
                        order_item.count,order_item.sub_total,order_item.oid as oioid,order_item.date as oidate,
                        product.pid,product.state as pstate,product.pname,product.pdesc,product.date as pdate,
                        product.csid,product.market_price,product.shop_price,product.sales,product.stock,
                        image.image_id,image.name as imgname,image.old_name,image.path,
                        image.date as imgdate,image.pid as imgpid,image.state as imgstate
                    from    orders
    				INNER  JOIN address
    				on orders.aid = address.aid
    				INNER  JOIN order_item
    				on orders.oid = order_item.oid
                    left join product
                    on order_item.pid = product.pid
                    INNER  join image
                    on image.pid = product.pid
                    WHERE orders.uid = 'ffef0a7cd0484739b06c8c6dd83f0c13'  and orders.state =1 limit 0 ,10

![QQ截图20190104201402.png][QQ_20190104201402.png]  
可以看到查询出的10条结果中前三条就出现了同一个订单的情况，这时候在去封装也就是8个订单了，并不是10个订单，也就解释通了为什么前台每页最初展示的订单个数都是不同的了，如果恰巧有一个订单正好10个订单项，而且就在数据截取的第一个时，那么这页就只有这一个订单了。

--------------------

下面展示一下正确的做法，因为我要查询订单，所以查询要以订单为主，先去查订单，然后在去和订单项，商品，图片什么的去关联，这时候在去查询就对了，而且这时查询出的数据不止十条，因为每个订单项中可能不仅只有一个商品。

SELECT * from
                (select  orders.aid as oaid ,orders.date as odate,orders.oid,orders.order_number,orders.state as ostate,
                orders.total, orders.uid as ouid
                from  orders
               WHERE orders.uid = 'ffef0a7cd0484739b06c8c6dd83f0c13'  and orders.state =1 
                 limit 0,10) as ord
            LEFT JOIN
                (select   order_item.order_item_id,order_item.pid as oipid,
                order_item.count,order_item.sub_total,order_item.oid as oioid,order_item.date as oidate,
                product.pid,product.state as pstate,product.pname,product.pdesc,product.date as pdate,
                product.csid,product.market_price,product.shop_price,product.sales,product.stock,
                image.image_id,image.name as imgname,image.old_name,image.path,
                image.date as imgdate,image.pid as imgpid,image.state as imgstate
                from  order_item
                left join product
                on order_item.pid = product.pid
                left join image
                on image.pid = product.pid) as orderExt
    
                on ord.oid = orderExt.oioid

![QQ截图20190104202546.png][QQ_20190104202546.png]  
可以看到正确的查询结果，结果一共28条订单项数据，完美！加油！！！！

[QQ_20190104201402.png]: /images/20220325/78885e3ae9704b74bd216c1f89a87dc5.png
[QQ_20190104202546.png]: /images/20220325/7c057ed832fb4ac7adb717937917fc76.png