数据库sql

1.当前交易日的前一天

with current_day as (select t.day from t_day t),

last_day as(select max(day) last_day from t_his_day t, current_day t1 where t.day<t1.day)

select * from last_day

2.row_number()over rank()over,dense_rank()over都是排序

百度查看具体区别

row_number() over(partition by id1,id2 order by id1,id2) rn

3.case when 用法

select sum（case type1 when '2' then vol1  

                                       when '5' then vol2   else 0 end） as total_vol  from table

4.lag()over/lead()over显示上一行或下一行的数据

lag()over（）显示上一行的数据，lead()over显示下一行的数据

lag(str)over(partition by id1 order by id1) 

5.listagg用法，相当于一个id，多个姓名，然后多个姓名逗号展示在行内6

listagg(姓名，‘，’) within group （order by id）相当于group_concat(id order by rn separator ',')

 6.round的用法

round(vol,0)  0代表无精度   四舍五入

round(160/3,3)  53.333    round（160/3,0） 53  round（160/3，-2）100

7.exists用于检查子查询是否会返回数据，子查询返回的是true或者false

select 1 from dual where exists (select 2 fron dual) 返回结果为1

select 1 from 锻炼where exists(select 2 from dual 1=2) 返回null

8.substr,instr  substr返回的是字符，instr返回的是下标  <!CDATA[<=]]>

lpad填充字符串的用法 ；lpad(string,padded_length,pad_string)  string准备被填充的字符串，padded_length 被填充的长度，pad_string 填充的字符串

nvl() nvl2()

9.first_value与last_value的用法，取首尾记录值

例如：查询部门最早发生销售记录日期和最近发生的销售记录的日期

first_value(id) over(partition by day,productid  order by id)

10.mybatis中foreach的用法

<foreach collection="list" item="element" index="index" open="(" close=")" separator="union all">

select #{element.id} as id from dual

</foreach>

11.having后可以跟多个条件

having 1=1 and sum(t)>2 and sum(s)>3

12.case when 作为条件判断

select id from table where  type=case when '1'=${type} then 'IF'  else 'T' end

13.level connect by level的用法，递归用法

select substr('201312',1,4) || level  day from dual connect by level<16

select substr('201312',1,4) || lpad(level,2,s) day from dual connect by level<=12

14.行转列可以借助case when，也可以借助pivot

15.count()over()求总数的用法

count(1) over(partition by id)as cnt

16.greatest(1,2,3,5,4) 返回5，从变量中返回出最大的一个数值

17.pivot行转列的用法

select type ,total1,total2,total3,all_total from tmp pivot  (sum(cnt) for product_type in ('11' as total1,'12' as total2,'21' as total2,'小计'  as_total))