SELECT *,count(*) as num FROM `wms_stock` GROUP BY sku_id,batch_sn HAVING count(*)>1
售后退款 一个订单同一个商品有两个出口批次 、打多个包裹 、退款有多条记录导致 数据重复
SELECT
SaleRefund.created, SaleRefund.refund_sn, if(batched > 0, 1, 0) AS IsBatch, WmsBatchBase.batch_sn,
SaleRefund.cust_name,SaleOrder.sp_name,SaleOrder.sup_name,SellerBase.seller_name,SaleRefund.sku_id,
GoodsSku.sku_name, GoodsBase.tax_fee, GoodsTypeSpec1.spec_name AS specName1,SaleOrder.store_id,
GoodsTypeSpec2.spec_name AS specName2, GoodsUnit.sale_unit, SaleRefund.sku_num, SaleOrderItem.sku_price,
SaleOrderItem.sale_price, SaleOrderItem.tax_rate, SaleOrder.order_sn, SaleOrder.pay_name,
SaleRefund.refund_money, SaleOrder.real_total, SaleRepay.repay_status, SaleRepay.repay,
SaleRefund.order_id, SaleOrder.ship_name, SaleRefund.note,SellerStore.store_name,
GoodsCategory1.cat_name AS catName1, GoodsCategory2.cat_name AS catName2,AdminUser.user_name,
GoodsCategory3.cat_name AS catName3, GoodsBase.commission,
SaleOrder.refund_total,GoodsBase.sale_type,GoodsBase.note AS goods_note,SaleRepay.repay_id,
SaleOrderItem.sale_num, SaleRefund.refund_status,
(
SELECT
GROUP_CONCAT(exp_no SEPARATOR ';')
FROM
wms_package AS WmsPackage
LEFT JOIN wms_package_item AS WmsPackageItem ON WmsPackageItem.pack_id = WmsPackage.pack_id
WHERE
WmsPackage.order_id = WmsOrderBase.order_id
AND WmsPackageItem.sku_id = SaleOrderItem.sku_id
AND WmsPackage.pack_status <> 99
) AS exp_no,
(
SELECT
stock_price
FROM
wms_order_item AS WmsOrderItem
WHERE
WmsOrderItem.order_id = WmsOrderBase.order_id
AND WmsOrderItem.sku_id = SaleOrderItem.sku_id
ORDER BY
batch_sn desc
limit 1
) AS stockprice
FROM
sale_refund AS SaleRefund
LEFT JOIN goods_sku AS GoodsSku ON GoodsSku.sku_id = SaleRefund.sku_id
LEFT JOIN sale_order_item AS SaleOrderItem ON SaleOrderItem.order_id = SaleRefund.order_id AND SaleOrderItem.sku_id = SaleRefund.sku_id
LEFT JOIN sale_order AS SaleOrder ON SaleOrder.order_id = SaleRefund.order_id
LEFT JOIN goods_base AS GoodsBase ON GoodsBase.goods_id = GoodsSku.goods_id
LEFT JOIN seller_base AS SellerBase ON SellerBase.seller_id=SaleOrder.seller_id
LEFT JOIN goods_category AS GoodsCategory1 ON GoodsCategory1.cat_id = GoodsBase.cat_id1
LEFT JOIN goods_category AS GoodsCategory2 ON GoodsCategory2.cat_id = GoodsBase.cat_id2
LEFT JOIN goods_category AS GoodsCategory3 ON GoodsCategory3.cat_id = GoodsBase.cat_id3
LEFT JOIN goods_type_spec AS GoodsTypeSpec1 ON GoodsTypeSpec1.spec_id = GoodsSku.sku_spec1
LEFT JOIN goods_type_spec AS GoodsTypeSpec2 ON GoodsTypeSpec2.spec_id = GoodsSku.sku_spec2
LEFT JOIN goods_unit AS GoodsUnit ON GoodsUnit.unit_id = GoodsBase.unit_id
LEFT JOIN admin_user AS AdminUser ON AdminUser.user_id = SaleRefund.created_user
LEFT JOIN sale_repay AS SaleRepay ON SaleRepay.refund_id = SaleRefund.refund_id AND SaleRepay.repay_type=2 AND SaleRepay.repay_status <>99
LEFT JOIN wms_batch_base AS WmsBatchBase ON WmsBatchBase.batch_id = SaleRefund.batch_id AND WmsBatchBase.batch_status=1 AND WmsBatchBase.type_id = 03
LEFT JOIN seller_store AS SellerStore ON SellerStore.store_id = SaleOrder.store_id
LEFT JOIN wms_order_base AS WmsOrderBase ON WmsOrderBase.out_order_id = SaleOrder.order_id AND WmsOrderBase.order_status not in(98,99)
WHERE
1 = 1 %s
ORDER BY
SaleRefund.created desc
LIMIT %d,%d