主要還是自己對sql語句的掌握程度太低了,還需要多多加強sql語句編寫,最初我的語句是這樣寫的:
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
limit #{startIndex},#{pageSize}
//sql片段
and orders.uid = #{ouid}
and orders.state = #{ostate}
也是無意間發現資料不準确,起初還沒發現問題所在。。。。。
後來改了下sql:
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
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
//sql片段
and orders.uid = #{ouid}
and orders.state = #{ostate}
想實作的功能是訂單的分頁,每頁顯示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
可以看到查詢出的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
可以看到正确的查詢結果,結果一共28條訂單項資料,完美!加油!!!!