# Mysql排序、组内排序

Mysql 排序、组内排序

---

显示排序编号

1.根据降序排序给查询的结果增加一列显示排序的编号：排序编号的参数需要用:=进行赋值，不能使用=因为在select语句中等号是比较运算符。

SELECT x.*,@rank :=@rank + 1 AS rank_no
FROM
	(
		SELECT * FROM jobs ORDER BY max_salary DESC
) x,(SELECT @rank := 0) b

查询结果如下：

2.对统计的结果进行降序排序并且显示相应的排序编号

SELECT
	x.*,@rank :=@rank + 1 AS rank_no
FROM
	(
		SELECT z.* FROM
		(SELECT count(*) cnt,y.salary,y.job_id FROM employee y GROUP BY y.job_id) z
		ORDER BY z.cnt DESC
	)x,(SELECT @rank:=0)a

---

分组内排序

1.对相同的科目内进行排序如下：对Math、English、pe的分数进行排序显示

SELECT
	x.*,
    IF (@g = x.item ,@rank :=@rank + 1 ,@rank := 1) AS rank,
    @g := x.item AS 'group'
FROM
	(SELECT z.id,z.item,z.score,z.time FROM test1 z
	ORDER BY z.item,z.score DESC) x,
	(SELECT @rank := 0 ,@g := NULL) y

实现的组内排序结果如下：

2.分组排序的结果限制如下：

select a.* from(
SELECT
	x.*,
  if(@g=x.item,@rank:=@rank+1,@rank:=1) as rank,
	@g:=x.item as 'group'
FROM
	(select z.id,z.item,z.score,z.time from test1 z order by z.item,z.score desc) x,
	(SELECT @rank := 0,@g:=NULL) y
)a WHERE a.rank<=2