oracle 学习笔记

常用语句备忘：

 

# 日期转为字符串

select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual

 

# 查询昨天记录，trunc负责日期按天取整

select * from xxx t where t.day >= trunc(sysdate - 1)

 

# limit记录

select * from xxx where where rownum < 10

 

# 分组后TOP1 。下面两个字段年份和温度，找到每年最热的温度

select year, max(temp) from TEST t group by t.year

 

# TOP N，下面2为为N值

select distinct *
  from TEST outer_t
 where 2 >= (select count(distinct inner_t.temp)
               from TEST inner_t
              where inner_t.year = outer_t.year
                and inner_t.temp >= outer_t.temp)