sql 相加_利用sql 进行综合排名

---

目的：当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

参考网站MySql语句实现排名_奔跑8蜗牛-CSDN博客_mysql排名语句怎么写

/变形

当要求shu1越小越好，shu2越大越好时，改成select `pp`.*,@shu1No:=@shu1No+1 as shu1No from `pp`,(select @shu1No :=0) init ORDER BY shu1 ASC