考试分数(四) 求中位数

select job,(max(seq)+min(seq))/2 as start,printf("%d",round((max(seq)+min(seq))*1.0/2)) as end 
from 
(select id,job,score,count() over(partition by job order by score asc) as seq from grade) as t 
group by job;

先用窗口函数count给每个job生成一个升序的序号，然后计算中间的序号，这里分数升序，降序也可以

3|C++ |9000|1
2|C++ |10000|2
1|C++ |11001|3
4|JAVA|12000|1
5|JAVA|13000|2
8|前端|999|1
7|前端|11000|2
6|前端|12000|3

将每个job最大序号和最小序号相加除以2得到中间一个小数，向下取整为start,向上取整为end,但是sqlite3没有取整函数，round可以将.5向上取整，再查了下可用的有个printf函数可以将小数变成整数。