select (@i :=case when @dt= dt and @warehouseProv=warehouseProv
then @i + 1 else 1 end ) as rownum,p.dt,p.warehouseProv ,p.shopName ,(@dt:= dt),(@warehouseProv:= warehouseProv)
FROM cus_p_bi_qc_shop_cang_sum_m p,(select @i := 0 )as a
where dt=202211
order by p.dt,p.warehouseProv ,p.shopName desc
注意!
如果排序失效,可以尝试将(@dt:=dt) 分别写在rownum前后进行尝试一下
举例
select
p.dt,p.warehouseProv ,p.shopName ,(@dt:= dt),(@warehouseProv:= warehouseProv),
(@i :=case when @dt= dt and @warehouseProv=warehouseProv then @i + 1 else 1 end ) as rownum
FROM cus_p_bi_qc_shop_cang_sum_m p,(select @i := 0 )as a
where dt=202211
order by p.dt,p.warehouseProv ,p.shopName desc