Oracle常用(大小写不敏感)

日期和字符串（to_char(),todate(),sysdate）以及其他常用函数

1.字符串转日期：

select to_date('2021-07-27 22:07:27','YYYY-MM-DD hh24:mi:ss')as 日期 from dual;

2.日期转字符串

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')as 当前日期对应字符串 from dual;

3.查询日期对应的年月日：
select to_char(sysdate,'yyyy') as 年,to_char(sysdate,'mm') as 月，to_char(sysdate,'dd') as 日 from dual;

4.得到某字段的首字母

字符串截取函数：substr(String s,int start,int number)

select substr('teststring',0,1) from dual;

select substr('teststring',1,1) from dual;

5.字符ASCII获取,字符对应ASCII

ASCII（char ch）

chr(int number)

SELECT ascii('A'),chr(65),ascii('Z'),chr(90) FROM DUAL;

6.字符大小写 

upper(char ch)

lower(char ch)

select upper('a'),lower('A') from dual;

 7.平均数，总数，最大值，最小值，某个字段的条数

avg(String field)

sum(String field)

max(String field)

min(String field)

count(String field)

8.字符串中加入''：

比如：

'select * from student where name=''张三'''

''表示转义字符'

select 'select * from student where name=''张三''' as 查询张三语言 from dual;

9.获取10年前今天的数据：

add_months(Date date, int month)

例如：

select add_months(sysdate, -10*12) from dual;

分页（rowrum属性）

比如对student表： 

select student.* from student;

一共10条：

一共分4页：查看第2页，

起始位置：（2-1）*3

结束位置：(2*3)+1

即：

1.根据行号，先查出小于(2*3)+1部分：表的全部信息，外加行号（给起始位置坐判断）；

select student.*,rownum as 行数 from student where rownum<((2*3)+1) order by id desc；

2.表的全部信息，外加行号（给起始位置坐判断），查出大于起始位置的
select t.* from (select student.*,rownum as 行数 from student where rownum<((2*3)+1) order by id desc)t where 行数>((2-1)*3);

经典topN问题：每组最大的N条记录。

rank() over、dense_rank() over、row_number() over 用法

“成绩表”记录了学生的学号，学生选修的课程，以及对应课程的成绩。

为了对学生成绩进行考核，现需要查询每门课程的前3高成绩。

注意：如果出现并列第一的情况，则同为第一名。

select *,   rank() over (order by 成绩 desc) as ranking,   dense_rank() over (order by 成绩 desc) as dese_rank,   row_number() over (order by 成绩 desc) as row_numfrom 班级;

 

 

rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。

dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。

row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

这三个函数的区别如下：

 

题目要求“如果出现并列第一的情况，则同为第一名”。所以，我们使用窗口函数dense_rank。

步骤一：按课程分组(partiotion by 课程号)，并按成绩降序排列(order by 成绩 desc)，套入窗口函数的语法，就是下面的sql语句：

select *,     dense_rank() over(partition by 课程号                       order by 成绩 desc) as排名from 成绩表;

 

步骤二：筛选出前3高的成绩，所以我们在上一步基础上加入一个where字句来筛选出符合条件的数据。（where 排名 <=3）

select 课程号,学号,成绩,排名 from(select *,     dense_rank() over (partition by 课程号                  order by 成绩 desc) as 排名from 成绩表) as aawhere 排名 <=3;

经典topN问题：每组最大的N条记录。这类问题涉及到“既要分组，又要排序”的情况，要能想到用窗口函数来实现。​​​​​​​

# topN问题 sql模板select *from (   select *,           row_number() over (partition by 要分组的列名                       order by 要排序的列名 desc) as 排名   from 表名) as awhere 排名 <= N;

Employee 表包含所有员工信息，每个员工有其对应的工号( Id)，姓名 (Name)，工资 (Salary) 和部门编号( DepartmentId) 。

查找每个部门前三高工资的员工。例如，根据上述给定的表，查询结果应返回：

select DepartmentId,Name,Salaryfrom (   select *,           dense_rank() over (partition by DepartmentId                       order by Salary desc) as ranking   from Employee) as awhere ranking <= 3;