MySQL实现ORALCE的row_number()over(partition by xx)功能_MySQL
Jun 01, 2016 pm 01:07 PM比如有个需求,要找出班级里每门课分数最高的学生(课程名称,分数,学生姓名),注意这里不是只取最高分,要把课程名称,分数,学生姓名都取出来!
在ORALCE里我们可以用ROW_NUMBER()OVER轻松实现,但是在mysql里就有点麻烦了,不过还是可以做的。
select course,score,name
from
(select
heyf_tmp.course,heyf_tmp.score,
heyf_tmp.name,
@rownum:=@rownum+1,
if(@pdept=heyf_tmp.course,@rank:=@rank+1,@rank:=1) as rank, -- 分组字段
@pdept:=heyf_tmp.course
from
(select course,name,score
from temp_student_score
order by course,score desc -- 分组字段,排序字段
) heyf_tmp ,
(select @rownum :=0,@pdept := null ,@rank:=0) a
) a where rank=1
最后我们只用取出RANK=1的部分就可以了

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Reduce the use of MySQL memory in Docker

How do you alter a table in MySQL using the ALTER TABLE statement?

How to solve the problem of mysql cannot open shared library

What is SQLite? Comprehensive overview

Run MySQl in Linux (with/without podman container with phpmyadmin)

Running multiple MySQL versions on MacOS: A step-by-step guide

How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)?

What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?
