写sql时几个比较常用的函数row_number,case when,limit等

写sql时几个比较常用的函数

持续更新…

一、row_number

ROW_NUMBER()函数将针对SELECT语句返回的每一行，从1开始编号，赋予其连续的编号 。

可用于编号、排序、找出第几个数据、分页

例1–对分数进行降序排序：

select row_number() over(order by score desc) number,*
from sc

例2–获取成绩第二高的同学的成绩

select sname,score from(
    select row_number() over(order by score desc) number,*
    from score
)t
where t.number = 2

补充：获取成绩前十的学生成绩信息
where t.number >=1 and t.number<=10

1.rank函数

对某一字段进行排序

和row_number区别：

• row_number排序时出现并列情况时，序号不相同；

• rank出现并列情况时，序号相同，且序号连续

select rank() over(order by score desc) number,*
from sc

2.dense_rank函数

和rank区别：

• rank出现并列情况时，序号相同，且序号连续（1，1，2）
• dense_rank出现并列情况时，序号相同，且序号跳跃（1，1，3）

select dense_rank() over(order by score desc) number,*
from sc

二、case when then end

Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略 (每个条件最好不重叠)

1.简单case函数

case sex
when '1' then 'man'
when '2' then 'woman'
else 'other' end

2.case搜索函数

case when sex='1' then 'man'
     when sex='2' then 'woman'
     else 'other' end

例–统计每个国家男性和女性人口总数

select country,
	   sum(case when sex = '1' then population else 0 end) as men,
       sum(case when sex = '2' then population else 0 end) as women
from tableA
group by country

三、All, Any, Some, Exists, in函数

子查询中使用

All函数例–查询平均分最高的课程

select cno,avg(score)
from sc
group by cno
having avg(score) >= 
all(select cno,avg(score)
    from sc
    group by cno)
    
    
补充解法：
select top 1 with ties cno,avg(score)  -- with ties解决并列问题，取出所有并列记录，没有with ties的话只会取出一条数据
from sc
group by cno
order by avg(score) desc

四、limit

limit index,num  -- 查询从第index+1条开始的num条数据（index从0开始，默认为0）

limit 1 查询第一条数据
limit 1,2 查询第二、三条数据