mysql 【变量+leetcode常见题】

一、mysql变量声明与定义

有两种使用方式：

1. 使用set

set @varname=999
或者
set @varname:=999

2. 使用select

由于select语句中，’=’表示比较，不是赋值的意思，故此种变量赋值只可以使用‘：=’
select @varname:=999
或者
select @varname:=字段名 from tablename where …….

二、实现成绩排名

题目：编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

1. 排名并列

select a.Score,(select count(distinct b.Score) from Scores b where a.Score<b.Score)+1 as Rank from Scores a order by Rank 

效果如下：

2.排名不并列

select Score,(@row_num:=@row_num+1) as Rank from (
         select Score from Scores) t1,
        (select (@row_num := 0)) t2 order by Score DESC

效果如下：

三、实现中位数

在学习和笔试的时候遇到过求中位数的问题，下面就总结一下

1. 一个数据一行，分组求中位数

数据如下：

#定义两个变量
set @rownumber:=0;#计数，同一分组出现的个数
set @median_group:=' ';#辅助计数，判断是否为同一分组
#代码1，这里还未涉及中位数，只是实现同一分组计数
select @rownumber:=(case when @median_group=a.gender then @rownumber+1 else 1 end) as count_of_group, @median_group:=gender as median_group, gender, heights,(select count(*) from heights as b where b.gender=a.gender) as total_of_gender from  heights  as a order by gender,heights;

成绩计数

#实现分组求中位数，format(num,x)将最后的成绩控制在2个小数为
#total_of_gender/2.0+1 和total_of_gender/2.0 是保证总数在奇数或者偶数的时候均适用
select median_group,format(avg(heights),2) as '成绩中位数' from (select @rownumber:=(case when @median_group=a.gender then @rownumber+1 else 1 end) as count_of_group, @median_group:=gender as median_group, gender, heights,(select count(*) from heights as b where b.gender=a.gender) as total_of_gender from  heights  as a order by gender,heights) as c where c.count_of_group between total_of_gender/2.0 and total_of_gender/2.0+1 group by median_group;

成绩中位数：

取分组后的前几条记录

问题描述：leetcode185

select d.Name as Department,e.Name as Employee,e.Salary 
from Department d ,Employee e where 
(select count(distinct e2.Salary) 
from Employee e2 where e2.DepartmentId=e.DepartmentId and e2.Salary>e.Salary) <=2 
and d.Id=e.DepartmentId 
order by Department ASC,Salary desc