窗口函数:
rank():跳跃排序; 比如11345558
dense_rank():连续排序; 比如:11123345
row_number():没有重复值的排序(记录相等也是不重复的),可以进行分页使用。
基本语法:
【<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)】
专用窗口函数:主要包括rank, dense_rank, row_number等专用窗口函数
聚合窗口函数:主要包括sum. avg, count, max, min等
特别注意:窗口函数要写在selectc子查询语句中,因为窗口函数的对Where语句或Group By语句处理后的结果进行查询。
对查询出class班级数据表中的每个班级的学生(学号)进行排名
【select *,rank() over (partition by 班级 order by 成绩 desc) as ranking from class;】
某表内连续出现3词的值:
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
±—±----+
| Id | Num |
±—±----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
±—±----+
例如,给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字。
±------------------------+
| ConsecutiveNums |
±------------------------+
| 1 |
±------------------------+
利用用户变量实现对连续出现的值进行计数:与自关联或自连接相比,这种方法的效率更高,不受Logs表中的Id是否连续的限制,而且可以任意设定某个值连续出现的次数。
select distinct Num as ConsecutiveNums
from (
select Num,
case
when @prev = Num then @count := @count + 1
when (@prev := Num) is not null then @count := 1
end as CNT
from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3
普通解法:
select distinct(a.Num) as "ConsecutiveNums"
from Logs as a, Logs as b, Logs as c
where a.Id = b.Id-1 and b.Id = c.Id-1 and a.num = b.Num and b.Num = c.Num #直接利用限制条件进行筛选。
having
having的存在是由于在sql中聚合函数和where不可以同时使用。
比如:
我们希望查找订单总金额少于 2000 的客户。
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
比如:
希望查找重复出现的用户
SELECT Email
FROM PERSON
GROUP BY Email
HAVING COUNT(Email)>1