有时候我们想要得到每个分组的前几条记录,这个时候oracle中row_number函数使用非常方便,可惜MYSQL从8.0版本开始才支持窗口函数。本文介绍一些通过sql实现窗口函数效果的方法。
1.利用用户变量实现数据自增
表flow_task有phaseno(序列号),objectno(编号)等几个字段,我们想实现根据编号字段分组,然后组内根据序列号排序功能
select @rownum:=@rownum+1 rownum,a.objectno,a.phaseno,
if(@objno=a.OBJECTNO or (@objno is null and a.objectno is null),
@rank:=@rank+1,
@rank:=1) as row_number,
@objno:=a.OBJECTNO
from(SELECT * from flow_task order by OBJECTNO,phaseno asc)a,
(select @rownum :=0,@objno:=null,@rank:=0)b
注意:order by OBJECTNO,phaseno asc 分组字段在前,排序字段在后
原理是,先 order by OBJECTNO,phaseno asc,这样后相同编号的记录会在一块儿,并且已经是phaseno有序asc的
select的字段一个一个的看:
@rownum:=@rownum+1,每一行在上行@rownum变量值的基础上+1
if(@objno=a.OBJECTNO or (@objno is null and a.objectno is null),@rank:=@rank+1,@rank:=1),每一行判断,当前行的objectno(编号)是否等于上一个@objno变量值,如果是在上一个@rank变量值基础上+1,否则@rank赋值1
@objno:=a.OBJECTNO,当前行objectno赋值给变量@objno
ps:如果想要分组后某个字段的几个值,也可以使用group_concat函数
select a.objectno,group_concat(ifnull(a.phaseno,'')) phaseno from(SELECT * from flow_task order by OBJECTNO,phaseno asc)a GROUP BY a.objectno
可以看到,group_concat函数把分组后某个字段的值用,拼接起来
要获取前3个值,使用substring_index函数
select a.objectno,group_concat(ifnull(a.phaseno,'')) phaseno,substring_index(group_concat(ifnull(a.phaseno,'')),',',3) sub_phaseno from(SELECT * from flow_task order by OBJECTNO,phaseno asc)a GROUP BY a.objectno
2.子查询
mysql> select a.*,
-> (select count(*) from tbl where col<=a.col) as rownum
-> from tbl a;
+----+------+--------+
| id | col | rownum |
+----+------+--------+
| 1 | 26 | 1 |
| 2 | 46 | 3 |
| 3 | 35 | 2 |
| 4 | 68 | 4 |
| 5 | 93 | 6 |
| 6 | 92 | 5 |
---------------------
具体实现方法:
SELECT a.* FROM student a WHERE (SELECT COUNT(*) FROM student
WHERE class = a.class AND id < a.id ) < 2 ORDER BY a.class,a.id;
通过SELECT COUNT(*) FROM student WHERE class = a.class AND id < a.id 这一句来查询当前记录在class相同的情况下的排序位置,然后判断 < 2的则是前两名的记录。
缺点,显然效率会差一些。
3.top N
查询每门课程分数最高的2个学生以及成绩
方法一:
select a.name,a.course,a.score
from test1 a left join test1 b on a.course=b.course and a.score<b.score
group by a.name,a.course,a.score
having count(b.id)<2
order by a.course,a.score desc;
方法二:
select *
from test1 a
where 2>(select count(*) from test1 where course=a.course and score>a.score)
order by a.course,a.score desc;