-- 此处select是为了分页
SELECT
*
FROM
(
-- 此处select是为了增加排行 且 当前一个与当前出库数量相同则不增加排行
SELECT
bb.*,
( IF ( @pre_out_stock_num <> bb.out_stock_num, @rank := @rank + 1, @rank ) ) shipping_rank,
@pre_out_stock_num := bb.out_stock_num AS temp
FROM
(
(
-- 此处select是查出具体出库数量,出库金额,累计进出库数量
SELECT
SUM( op.exchange_out_stock_num ) AS out_stock_num,
SUM( op.out_stock_price ) AS out_stock_price,
SUM( op.exchange_show_num ) AS exchange_show_num,
gw.wine_name
FROM
-- 订单拆分表 拆出入库 出库信息
u_order_split AS op
-- 关联查到商品信息
LEFT JOIN u_goods_support gs ON gs.prod_id = op.prod_id
-- 关联查到酒体信息
LEFT JOIN u_goods_wine AS gw ON gw.wine_code = gs.wine_code
WHERE
-- 业务判断
op.product_type = 1
AND op.ddate >= "2022-03-01"
AND op.ddate <= "2022-03-31"
-- 根据酒体分组
GROUP BY gs.wine_code
HAVING gs.wine_code IS NOT NULL AND out_stock_num > 0
ORDER BY
out_stock_num DESC
) AS bb,
( SELECT @rank := 0 ) AS a,
( SELECT @pre_out_stock_num := 0 ) AS b
)
) AS cc
LIMIT 0,3