php 分支排序,php – 基于类别和分支的Mysql排名

试试这个查询

SELECT

@rn:=if(@prv=branch_code_id, @rn+1, 1) as rId,

@prv:= branch_code_id as branch_code_id,

val,

id,

date,

category

FROM

(SELECT

a.id,

a.date,

a.branch_code_id,

SUM(b.amount) as val,

c.category

FROM

sales_add_h AS a

INNER JOIN

sales_add_i AS b ON a.id = b.sales_h_id

INNER JOIN

control_panel_item_create AS c ON b.item_code_id = c.id

GROUP BY

c.category, a.branch_code_id, b.amount

ORDER BY

a.branch_code_id, SUM(b.amount) DESC)tmp

JOIN

(SELECT @rn:=0, @prv:=0)t

SQLFIDDLE了解排名如何运作.

我已经为你提到的每个branch_id做了排名,如果你想为特定分支中的每个类别排名,而不是你需要添加另一个存储类别的变量并在if子句中进行比较,还需要在内部排序数据通过c.category,a.branch_code_id,SUM(b.amount)DESC查询相应的顺序