row_number dense_rank排名函数,ifnull,date_sub,三个字段空值判断(nvl),concat函数
row_number() 给每一条select查询到的数据进行排序加一个序号,从1开始
员工 | 部门 | 工资 |
empid | deptid | salary |
1 | 10 | 5500 |
2 | 10 | 4500 |
3 | 20 | 1900 |
4 | 20 | 4800 |
5 | 40 | 6500 |
6 | 40 | 14500 |
7 | 40 | 44500 |
8 | 50 | 6500 |
9 | 50 | 7500 |
SQL脚本:
select * , row_number() over (partition by deptid order by salary desc) rank from employee
预期结果
empid | deptid | salary | rank |
1 | 10 | 5500 | 1 |
2 | 10 | 4500 | 2 |
4 | 20 | 4800 | 1 |
3 | 20 | 1900 | 2 |
7 | 40 | 44500 | 1 |
6 | 40 | 14500 | 2 |
5 | 40 | 6500 | 3 |
9 | 50 | 7500 | 1 |
8 | 50 | 6500 | 2 |
------------------------------------------------------------------------------------------------------------------
dense_rank() 窗口函数:连续可重复
栗子:
sales_employee | fiscal_year | fiscal_year |
Alice | 2016 | 150.00 |
Alice | 2017 | 100.00 |
Alice | 2018 | 200.00 |
Bob | 2016 | 100.00 |
Bob | 2017 | 150.00 |
Bob | 2018 | 200.00 |
John | 2016 | 200.00 |
John | 2017 | 150.00 |
John | 2018 | 250.00 |
SQL:
SELECT
sales_employee,fiscal_year,sale,
DENSE_RANK() OVER (PARTITION BY fiscal_year ORDER BY sale DESC) AS sales_rank
FROM
sales;
输出如下:
sales_employee | fiscal_year | sale | sales_rank |
John | 2016 | 200.00 | 1 |
Alice | 2016 | 150.00 | 2 |
Bob | 2016 | 100.00 | 3 |
Bob | 2017 | 150.00 | 1 |
John | 2017 | 150.00 | 1 |
Alice | 2017 | 100.00 | 2 |
John | 2018 | 250.00 | 1 |
Alice | 2018 | 200.00 | 2 |
Bob | 2018 | 200.00 | 2 |
DENSE_RANK()连续不跳跃
1.ifnull函数
ifnull(x,y) 如果x有值则返回x
如果x为空就返回y
2 date_sub
date_sub(date,interval expr unit)
date:表示要处理的时间字段
interval:固定的标志
expr:表示数量,如果为正则表示加上,如果为负则表示减去
unit:表示加上或减去的时间类型,如年,月,日,周
type值 | 含义 |
second | 秒 |
minute | 分钟 |
hour | 小时 |
day | 天 |
week | 周 |
month | 月 |
quarter | 季 |
year | 年 |
microsecond | 微秒 |
3 nvl函数:判断为Null的
三个字段空值判断
nvl(NVL(I.REGISTRATIONNUMBER,I.CORPORATIONDOCNO),p.cprdm)
字符串替换
java栗子
把字符串 'foothebar' 中的th替换为 ‘22’
代码实现
public class StringReplaceEmp{
public static void main(String args[]){
String str = "foothebar";
System.out.println(str.replace('th','22'));
}
}
concat函数:将多个字符串连接成一个字符串
t1表
id | name |
10001 | 沙 |
10001 | 石 |
10001 | 煤 |
10002 | 水 |
10002 | 盐 |
10002 | 盐 |
10002 | 盐2 |
SQL
select concat(id,name) as info from t1;
info
10001沙
10001石
10001煤
10002水
10002盐
10002盐
10002盐2