目的:当pp表有两组不同数值,求综合排名是不能用求和汇总排名
解释:id,shu1,shu2 是数据源PP表
/*shu1,shu2越大越好,排名越前*/
shu1no是排名值,shu2no是排名值 , sum12no是排名值相加,
Ranking 是综合排名:当sum12no最小值是NO.1
//sql 的排名代码:
select `pp`.*,@shu1No:=@shu1No+1 as shu1No from `pp`,(select @shu1No :=0) init ORDER BY shu1 deSC
///全部代码
/*shu1,shu2越大越好,排名越前*/
select KK.*,@sum12no:=@sum12no+1 as Ranking from
(
select A.*,B.shu2No,(a.shu1No+b.shu2No) as sum12no from
(
select `pp`.*,@shu1No:=@shu1No+1 as shu1No from `pp`,(select @shu1No :=0) init ORDER BY shu1 deSC
)a
left join
(
select `pp`.*,@shu2No:=@shu2No+1 as shu2No from `pp`,(select @shu2No :=0) init ORDER BY shu2 deSC
)B
on a.id=b.id
)KK
,(select @sum12no :=0) init ORDER BY sum12no ASC
/变形
当要求shu1越小越好,shu2越大越好时,改成select `pp`.*,@shu1No:=@shu1No+1 as shu1No from `pp`,(select @shu1No :=0) init ORDER BY shu1 ASC