众所周知Mysql8.0之前是不支持窗口函数的,所以在用到老版本MySQL的时候,如果遇到想分组排序排名之类的需求,可以采用如下方式进行:
设有表employee,数据如下(数据来自LeetCode):
我们想在A,B,C中分别对Salary这个字段进行排序,可以考虑引入变量来解决这个问题。
之前没有接触过MySQL的变量,后来发现跟PL/SQL有一点不同,变量是需要加@的来赋值。
SELECT
em.Id,
em.Company,
em.Salary,
CASE
@com
WHEN em.Company THEN
@rk := @rk + 1 ELSE @rk := 1
END rank1,
@com := em.Company
FROM
employee em,
( SELECT @com := '', @rk := 0 ) b
ORDER BY
em.Company,
em.Salary
其中rk变量是排名,com变量是对应Company切换的。
select里面的case语句意思就是把每一行和com变量比较,如果相同(代表是同一个组别的),那么排名+1,如果不相同(切换了组别),那么排名重置为1。
当然,这么做的前提是,MySQL中实际上order by是在select之前执行的。先执行关系语句,最后再进行投影,这样的话,我们先排好序,语句就能按照设想的顺序(按照Company和Salary进行排序)来执行了。
需要注意的是一点,当语句中有超过两个关联表时,order by的执行顺序会被放在select之后,这样达不到排序的效果了,所以一定要先把关联表联合查询为一个临时表,再进行变量的排序处理!
分步来看,先把CASE语句去掉,直接排序,更容易理解CASE里面的内容。
SELECT
em.Id,
em.Company,
em.Salary
FROM
employee em,
( SELECT @com := '', @rk := 0 ) b
ORDER BY
em.Company,
em.Salary
这个就是单纯的排序语句,其中的b表是对变量进行初始化(这也是跟PL/SQL不一样的地方,需要用select初始化),而且不影响结果,因为跟em表没有关联。
得到的结果如下:
然后按照CASE语句走一遍,就很容易理解了。
需要注意的是, @com := em.Company这句话一定要在CASE执行完毕之后,这个是把当前处理的行的Company赋值给com变量,用来判断是否切换了组别。
select中的字段,执行是按照顺序的。
这样就能得到最终的结果了:
最后看一下SQL语句关键字中的执行顺序(SELECT的顺序会随着SQL的组成调整进行优化,并不固定,我个人理解应该是在ORDER BY后面):
FROM, including JOINs
WHERE
GROUP BY
HAVING
WINDOW functions
DISTINCT
UNION
ORDER BY
SELECT
LIMIT and OFFSET