简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
Home > Database > Mysql Tutorial > body text

MySQL实现ORALCE的row_number()over(partition by xx)功能_MySQL

WBOY
Release： 2016-06-01 13:07:59
Original
1277 people have browsed it

比如有个需求，要找出班级里每门课分数最高的学生（课程名称，分数，学生姓名），注意这里不是只取最高分，要把课程名称，分数，学生姓名都取出来！

在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的部分就可以了

source：php.cn
Previous article：Getting to know TokuDB for MySQL_MySQL Next article：mysql-5.5 for linux源码安装_MySQL
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
function_exists() cannot determine the custom function Function test () {return true;} if (function_exists ('test')) {echo "test is function...
From 2024-04-29 11:01:01
0
2
1453
How to display the mobile version of Google Chrome Hello teacher, how can I change Google Chrome into a mobile version?
From 2024-04-23 00:22:19
0
9
1618
The child window operates the parent window, but the output does not respond. The first two sentences are executable, but the last sentence cannot be implemented.
From 2024-04-19 15:37:47
0
1
1385
There is no output in the parent window document.onclick = function(){ window.opener.document.write('I am the output of the child ...
From 2024-04-18 23:52:34
0
1
1305
Where is the courseware about CSS mind mapping? Courseware
From 2024-04-16 10:10:18
0
0
1357
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn：Public welfare online PHP training，Help PHP learners grow quickly！