MySQL多个字段排序 mysql排序并排名

Mysql排序

• 简单排序

• 简单升序排序
• 简单降序排序
• 简单并列排序
• 复杂并列排序

• 分组排序

• 分组简单排序
• 分组并列排序

• 查找中位数
• 给定频率查找中位数
• 自定义变量

在Mysql中可以根据order by进行简单升序，降序(desc)排序。

今天，我们将来讨论排序后，新加入一列来记录排名的情况（比如说汇总成绩或工资，根据成绩或工资降序进行排列并记录）。

本文主要利用一般方法 以及 自定义变量的方法进行论述。

简单排序

我们使用如下所示的工资数据按工资从低到高进行并列升序排序。该表名为graduates。

简单升序排序

自定义变量进行工资从低到高的排序。这种排序是寻找中位数的基础。

select name,income,@rank:=@rank+1 count
from graduates,(select @rank:=0) temp
order by income;

简单降序排序

利用自定义变量进行工资从高到低的排序。与升序排序的区别在于对收入income进行降序排序。

select name,income,@rank:=@rank+1 count
from graduates,(select @rank:=0) temp
order by income desc;

简单并列排序

1. 按工资从高到低进行并列排序。

select g1.name,(count(distinct(g2.income))) count
from graduates g1,graduates g2
where 
g1.income<=g2.income
group by g1.name
order by count,g1.name;

2. 利用自定义变量进行并列排序

select *, @rank:=if(@pre=income,@rank,@rank+1) count,@pre:=income
from graduates ,(select @rank:=0,@pre:=null) temp
order by income desc;

复杂并列排序

select *, @rank:=if(@pre=income,@rank,@inc) count,@pre:=income,@inc:=@inc+1
from graduates ,(select @rank:=0,@pre:=null,@inc:=1) temp
order by income desc;

分组排序

利用如下工资数据进行分组排序，对各个公司的工资进行由高到低排序。表名为employee。

分组简单排序

1. 利用自定义变量进行分组简单排序。

select *, @rank:=if(@pre=company,@rank+1,1) count, @pre:=company
from employee,(select @pre:=null,@rank:=1) temp
order by company,salary;

分组并列排序

1. 分组并列排序。

select e1.id, count(distinct(e2.salary)) count
from employee e1,employee e2
where e1.company=e2.company
and e1.salary<=e2.salary
group by e1.id;

2. 利用自定义变量进行分组并列排序。

select * ,@rank:=if(@pre=company,if(@spre=salary,@rank,@rank+1),1) count,
@pre:=company,@spre:=salary
from employee,(select @pre:=null,@spre:=null,@rank:=1) temp
order by company,salary desc;

查找中位数

这里我们利用自定义变量查找graduates的中位数，以及分组查找employee的中位数。这里我们只返回相应的数据，可以根据avg来求中位数。
floor为不大于该数的最大整数，ceil为不小于该数的最小整数。
这里我们对数组的排序为(0,1,2,3,4,5,6,7,8,9) 是因为floor(9/2)=4,ceil(9/2)=5；而(0,1,2,3,4) floor(4/2)=2,ceil(4/2)=2

1. graduates表中位数

select g.name,g.income
from
(select name,income,@rank:=@rank+1 count
from graduates,(select @rank:=-1) temp
order by income) g
where g.count in(floor(@rank/2),ceil(@rank/2)) ;

2. employee表分组查找中位数

select e1.id,e1.company,e1.salary
from
(select *, @rank:=if(@pre=company,@rank+1,0) count, @pre:=company
from employee,(select @pre:=null,@rank:=-1) temp
order by company,salary) e1,
(select company,count(*) count1 from employee group by company  ) e2
where e1.company=e2.company
and e1.count in (floor((e2.count1-1)/2),ceil((e2.count1-1)/2));

给定频率查找中位数

我们利用numbers表查找中位数，其原理为查找出第6个和第7个数。也就是说应该找出比该数大的最小累加frequency。

select avg(n.number) median
from
(
(select min(n1.number) number
from
(select number,@cumsum1:=@cumsum1+frequency cumsum1
from numbers,(select @cumsum1:=0) temp order by number) n1
where
n1.cumsum1>=floor((@cumsum1+1)/2))

union all

(select min(n2.number) number
from
(select number,@cumsum2:=@cumsum2+frequency cumsum2
from numbers,(select @cumsum2:=0) temp order by number) n2
where
n2.cumsum2>=ceil((@cumsum2+1)/2))) n

自定义变量

1. 初始化自定义变量
   set @pre:=null; set @rank:=0;
   或者（seletc @pre:=null,@rank:=0）temp
2. 赋值与判断的区别
   @pre=company 为判断是否相等，@pre:=company为赋值；
   在set时两种都表示为赋值。