Oracle常用sql

//Oracle查询一个月内所有天数（可以指定范围）

select to_date('2015-09-1', 'yyyy-mm-dd') + rownum - 1 as d
  from all_objects
 where (to_date('2015-09-1', 'yyyy-mm-dd') + rownum - 1) between
       to_date('2015-09-1', 'yyyy-mm-dd') and
       to_date('2015-09-21', 'yyyy-mm-dd')

//Oracl横列变竖列显示sql

 

select department,

sum(case when 员工种类=1 then 工资 else 0end) 员工种类为1的工资总额,

sum（case when 员工种类=2 then 工资 else 0end) 员工种类为2的工资总额

from 表 group by department

 

//Oracl     竖列变横列显示

selectdopb.perfor_year,dopb.user_id,

  sum(casewhendopb.perfor_quarter='一'thendopb.total_scoreelse0end ) total_score1,

  sum(casewhendopb.perfor_quarter='二'thendopb.total_scoreelse0end ) total_score2,

  sum(casewhendopb.perfor_quarter='三'thendopb.total_scoreelse0end ) total_score3,

  sum(casewhendopb.perfor_quarter='四'thendopb.total_scoreelse0end ) total_score4

  fromdata_oa_perfor_bonusdopbgroupbydopb.perfor_year,dopb.user_id

  

selectuser_id,perfor_year,

       max(caseperfor_quarterwhen'一'thenscore_factorelse''end) scoreFactor,

       max(caseperfor_quarterwhen'二'thenscore_factorelse''end) scoreFactor,

       max(caseperfor_quarterwhen'三'thenscore_factorelse''end) scoreFactor,

       max(caseperfor_quarterwhen'四'thenscore_factorelse''end) scoreFactor

fromdata_oa_perfor_bonus  whereuser_id=9groupbyperfor_year,user_id;

//查询日期

  //本周日

selecttrunc(sysdate,'iw')-1fromdual;

  //上周日期

selectto_char(trunc(sysdate, 'iw') - 7, 'yyyymmdd')  fromdual

//添加表字段

alter   table 表名 add（新字段）