1、行列转换:

        冰花男孩-我心头的非常英雄

  decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值);

       
如果问问最近最给人口痛惜的镜头什么?答案莫过于云南昭通那个头顶雪花的男孩子。回忆一下良画面,男孩因为于习途中因突遭降雪,到学府的时刻帽子上顶上了扳平圈冰费。两限的微脸上,冻红的比如有些苹果。尽管为班级同学笑话,但他眼神中倒充满着呆萌和一意孤行。孩子的有点手早给冻疮破,粗糙、发黑又红肿,但是红肿的手遮不鸣金收兵那张高分的试卷。

 

       
一瞬间,男孩成了“网红”流通网络。引发了一波并且同样波的刷屏,自媒体也是一波又平等波的章出现,很多权及了如高考改变命运、寒门难来贵子,社会阶层等等很抓人眼球的话题。

  select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; –取较小值

     
被自己而言,冰花男孩的励志故事让还多地为祥和感觉到惭愧,在这样冷漠艰苦条件下,冰花男孩还是具有一样发追求学问,渴望改变之中心

 

冰雪寒冷如斯,你可暖如春风。

  sign()函数根据某值是0、正数还是负数,分别返回0、1、-1

看你,才晓得经历的那些不堪和惨痛了无值得一提。

  例如:

        一、  冰花男孩是孰?

  变量1=10,变量2=20

       
冰花男孩给王福满,是云南鲁甸县新街镇转山包小学之学童。1月8日上午,他去家,走了即一个小时的山路,来参加期末考试。等客到教室时,头上业已结束满冰霜,远看去如长了同峰白发。只发三起外套的王福满说,由于家里装只能手洗,加上冬极端凉,厚衣服无涉及,只能通过少件薄衣服外出。这个爱好读书、喜欢数学的男生,最要命的意愿就是是,能考查到都就学。

  则sign(变量1-变量2)返回-1,decode解码结果吗“变量1”,达到了得到较小值的目的。

       
当给提问到底“结满”冰花冷无制冷之时节,男胎回答道:“当时专心走路,也无搜了头,没有感到,就是觉得冷。但是从未想最多,就想快点到全校,因为要未雨绸缪考试。

 

后来重案组37哀号对王福满进行了集

比方:查询emp表中之每个机构的食指?

重案组37号:平时喜爱学也?

王福满:喜欢念书,我最欣赏数学,觉得不行有趣。

重案组37号:想了热过去后,会怎样呢?

王福满:过去了即过去了,我要么想念要得看。

重案组37号:寒假赶快到了,有啊心愿?

王福满:我想去昆明游玩,然后便优质读书,将来考到北京夺上学。

SELECT sum(decode(deptno,10,1,0)) as 部门10,
            sum(decode(deptno,20,1,0)) as 部门20,
            sum(decode(deptno,30,1,0)) as 部门30 from emp

       
就是亲骨肉这样概括而与此同时一直的答问,深深的震撼到了和谐。曾发那说话,看正在小福满纯真而以聪慧的视力,鼻头莫名一酸。比从他所给之惨淡及劳动,我们所经历之委屈、辛苦而算得了什么呢?。

统计 1

          二、自己情何以可以!

      

       
科普一下,小福满的热土的云南昭通地区,是国家的深度贫困地区。根据数据统计,目前发出建档立卡的贫困人口113.37万人,其中小学生13.87万口、占在校生总数的46.79%。也就是说,整个昭通市近半在校小学生,属贫困人口。

      做个对比:select deptno,count(deptno) from emp group by deptno

     
正因为这样,所以学校至今从不设置任何取暖装备。由于妻子经济条件不好,孩子的妈妈早以少数年前离开了这小,之后就是又为尚未沟通。目前,靠子女大的面包车,3000左右之月份收入,独自支撑着漫天家。

      统计 2

     
家里的取暖装备,简单到被人口难以置信-烧木柴,据悉,家里的房屋是20几近年前修的,用泥巴糊起来的,再由死木头支撑住。由于几只房间没有通电,取暖只能靠炭盆。这无异于据此,就是20年。

 

王福满及姐姐

2、递归查询的优化

       
看到此,一道惭愧感油然而生。当我们当抱怨办公室空调温度最好胜,当我们吐糟单位之各种缩水福利,当我们感叹上下班高峰地铁不好挤的下,可都想象了小福满的活着环境?

   lpad/rpad( string, padded_length, [ pad_string ] )

       
炭盆取暖的法力如何,尝试过的口还清楚;价值800初一年的太阳午餐,不敌多口同圆的饭钱;4.5公里之山道来多麻烦走,看小福满一个学期就磨损了十几不行就是清楚了。

   说:字符不够的上向左或向右侧填充。

乃当下享受每一点便利,在别人看来还遥不可及。

   例如:SQL> select lpad(‘abcde’,10,’x’) from dual;

     
更可贵的是,小福满于采访被,始终不挂对数学,对上和首都的钟爱。多次应答着,都强调了和谐一旦漂亮读书,将来考到北京攻!

      LPAD(‘ABCDE’,10,’X’)

       
观这里,更是起少数抽泣。这不就是小时候底和睦吧?怀揣在简单的期望,眼里无惧一切,认定了对象,风里雨里还挡住不了提高的步子;而今日吗?成熟了,油腻了,清晰地希望吗易得模糊,坚定步伐似乎渐行渐远。

      xxxxxabcde

 

        小福满,在你眼前,我情何以得!

  1. SELECT ID, FATHER_ID, NAME, CONNECT_BY_ISLEAF LEAF   
  2. FROM T_TREE   
  3. START WITH FATHER_ID = 0   
  4. CONNECT BY PRIOR ID = FATHER_ID;  

        三、竭尽全力才会毫不费力

 

        网易槽值对冰花男孩事件的评价,十分中肯

解释:CONNECT_BY_ISLEAF
判断该行记录是否也叶子节点,如果是返回1,否则返回0

       
读书不苦,不读书之人生才苦。毕竟,一个连看之困苦犹吃不下去的食指,又怎么样能咽下活的委屈?

       
学生时是增高力与见闻的极度时期,如果以生时追求所谓的妄动、快活和欣喜,那如果适量才能够开努力?这样类似童年时喜欢了,那么到了黄金时代、中年以及晚年?这卖无忧无虑又能维持多久?少壮不奋力老大徒伤悲,是太好之警世通言!

          START WITH FATHER_ID = 0 规定啊一样长记下为根节点

       
初入职场的小白,跟小学在读的福满没有最好之出入。明明最好该选择奋斗和努力的年份,很多丁可选择了舒适和泰。尽管工作、生活被见面面临不少辛劳,职场上的碰撞和委屈,以及被许多人口诟病的职场潜规则和努力。但是,这本来就是职场的常态,不是若懈怠的理与借口。

          CONNECT BY PRIOR ID = FATHER_ID
判断是节点的父节点是啦一样漫长记下

       
比打些许福满的准,我们所有的无知情多了稍稍,幸福多少。可我们不少总人口连无亮堂珍惜,在绝该拼搏的年份选择了举行相同名佛系青年,在极端该奋起直追的时注意刷在爱人围。办事头五年,决定你的职场上限!在快捷转移的年代,每个人且使履薄冰,也许哪一样龙失业就成了具体。

 

       
最近为通讯之赌王儿子,是无与伦比好的例证。比你重新发出钱,比你再精彩,比你再拼!

例子:

       
但立刻并无表示没有机会。在互联网高度发达的今日,不对称性越来越成这时的显要特色。蚂蚁战胜大象并非异想天开,面对BAT三足鼎立的面360异军突起成为了互联网企业里之均等配合黑马;微信如火如荼,也从没能够阻挡今日头像的飞跃崛起,当年光是一个问答平台的知乎,已经变为了顶尖平台。无论是企业或者个人,这通的一切都在说明,这是个人崛起最好的时期。

 SELECT LPAD(MENU_NAME,

         
我们设召开的就是是开自己的稍目标,竭尽全力的朝向前方因。就比如微微福满那样,哪怕路上摔倒,哪怕销小手冻出疮,哪怕再挺的风和雨,都遮不了友好!吞下那些委屈、不满和忧虑,那些很不要命你的,只见面为您再次有力!

            LENGTHB(MENU_NAME) + LEVEL,

       
谢谢你,小福满。是您于自身重拾奋斗的动力!让我备感羞愧,但再也被自己起来!

            DECODE(CONNECT_BY_ISLEAF, 1, ‘ | ‘, ‘+’))

  FROM MENU

 START WITH PID = ‘0’

 CONNECT BY PRIOR ID = PID;

 

3、利用分析函数排序和去重

http://blog.csdn.net/haiross/article/details/15336313#comments

 

浅析函数是呀?
解析函数是Oracle特别用来缓解复杂报表统计需求的功能强大的函数,它好以数中开展分组然后计算基于组的某种统计值,并且每一样组的各国一行还好回来一个统计值。

          

解析函数和聚合函数的不同之处是什么?
万般的聚合函数用group
by分组,每个分组返回一个统计值,返回的字段名只能是分开组名。而分析函数采用partition
by分组,并且每组每行都足以回来一个统计值,返回的字段名好是每个字段,因为凡针对性诺到记录之,所以并未提到。

              

分析函数的样式
剖析函数带有一个开窗函数over(),包含三个分析子句:分组(partition by),
排序(order by), 窗口(rows) ,
他俩的下形式如下:over(partition by xxx
order by yyy rows between zzz)

 

 开窗函数over()富含三只分析子句:分组子句(partition by),
排序子句(order by), 窗口子句(rows)
      窗口就是分析函数分析时假如处理的数范围,就拿sum来说,它是sum窗口被之笔录而休是总体分组中的记录,因此我们于纪念得有栏位的累计值时,我们用拿窗口指定到拖欠分组中的首先实施数据到眼前推行,
如果你指定该窗口起该分组中之首先执到终极一尽,那么该组中的各国一个sum值都见面一如既往,即满组的总数。

     

OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):当前组第一行到当前行的汇总

OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING):当前行到最后一行的汇总

OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND CURRENT ROW):当前行的上一行(rownum-1)到当前行的汇总

OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING):当前行的上一行(rownum-1)到下两行(rownum+2)的汇总

 

苟随便是否省略分组子句,如下结论还是确立之:

 

 1、窗口子句不克独出现,必须产生order by子句时才能够出现。

 2、当省略窗口子句时:
    a) 如果存在order by则默认的窗口是unbounded preceding and current
row  –当前组的首先履行至眼前执行,即以当下组中,第一实施及当前实施,这里强调一下,如果partition
by字段和order by 字段一样的话,这个order by不见效,相当给简单了order by

    b) 如果又省略order by则默认的窗口是unbounded preceding and
unbounded following  –整个组

 

少个order by的推行时

剖析函数(以及与其配合的开窗函数over())是以漫天sql查询了晚(sql语句被的order
by的履于异常)再开展的操作, 也就是说sql语句被的order
by也会潜移默化分析函数的实行结果:

 

    a) 两者一致:如果sql语句被的order
by满足与分析函数配合的开窗函数over()分析时要求的排序,即sql语句被之order
by子句里的始末跟开窗函数over()中的order
by子句里的内容一致,那么sql语句被之排序将事先实行,分析函数在条分缕析时虽不必再度排序;
    b) 两者不一样:如果sql语句被之order
by不满足与分析函数配合的开窗函数over()分析时讲求的排序,即sql语句被的order
by子句里的情及开窗函数over()中之order
by子句里的始末未相同,那么sql语句被之排序将最终在分析函数分析了后实施排序。

 

 

常用之解析函数:

  1、row_number() over(partition by … order by …) 
为各一样修记下返回一个唯一的价。当遇相同数量常常,排名论记录集中记录之次第依次递增,现实状况吧:个人于分组内之行
  2、rank() over(partition by … order by …) 
得到各级条记下在多少中的行,排名不纵
  3、dense_rank() over(partition by … order by …)
得到各级条记下在数遭到的排名,排名跳跃
  4、count() over(partition by … order by …)
每个分组中,某个字段的统计
  5、max() over(partition by … order by …)
  6、min() over(partition by … order by …)
  7、sum() over(partition by … order by …)
  8、avg() over(partition by … order by …)
  9、first_value() over(partition by … order by …)
得到第一独记录值
  10、last_value() over(partition by … order by …)
得到最终一个记录值
  11、lag() over(partition by … order by …)
lag函数可以当同样不好查询中取出同一字段的前面n行的数码

  12、lead() over(partition by … order by …)
lead函数可以当平蹩脚询问中取出同一字段的后n行的价

 

 

lag(arg1,arg2,arg3)
率先只参数是列名,
次个参数是偏移的offset,
其三只参数是超乎记录窗口时的默认值。

select id,name,lag(name,1,0) over(order by id) from kkk; 

 

 例子:select ename,deptno,sal,sum(sal) over(partition by deptno order
by ename) as A from emp

统计 3

 

去重:

  1、利用rowid的唯一性查询或删除重复数据

  
select ROWNUM,ROWID,d1.* from dept2 d1 where d1.rowid=(select min(d2.rowid) from dept2 d2 where d2.deptno=d1.deptno);

  2、给定重复行序号并夺还

SELECT FWZL

    FROM (SELECT FWZL,

                 ROW_NUMBER() OVER(PARTITION BY FWZL ORDER BY ID DESC)
RN

            FROM T_FWXX) F

   WHERE F.RN = 1

 

 4、求占比较、小计和累计

      分析函数RATIO_TO_REPORT
用来计算时记录的指标expr占初始窗函数over中涵盖记录的有所同一指标的百分比.
这里而开窗函数的统计结果吗null或者为0,就是说占用率的于除数为0或者也null,
则得到的结果为也0. 开窗条件query_partition_clause决定为除数的值,
如果用户忽略了是原则, 则计算查询结果遭遇享有记录的汇总值.
用户不能够应用其它分析函数或者ratio_to_report作为分析函数ratio_to_report的参数expr,
也就是说这个函数

 百私分比(求之字段值占整组的百分比):select deptno,ename,empno,round(RATIO_TO_REPORT(sal) OVER(PARTITION BY deptno)*100,1) 百分比 from emp

统计 4

 

rollup()与cube():排列组合分组 

1)、group by rollup(a, b, c):
率先会针对(a、b、c)进行group by,
接下来又对(a、b)进行group by,
从此重针对(a)进行group by,
末对全表进行集中操作。

 

2)、group by cube(a, b, c):
尽管如此第一会针对(a、b、c)进行group by,
然后挨家挨户是(a、b),(a、c),(a),(b、c),(b),(c),
末了对全表进行汇总操作。

 

 

ROLLUP,是GROUP
BY子句的同栽扩大,可以为每个分组返回小计记录与为富有分组返回总计记录。

CUBE,也是GROUP
BY子句的一致种植扩大,可以回到各国一个排成的小计记录,同时以末加上总计记录。

 

呼吁每个分组的小计和合:select deptno,sal,sum(sal) from emp group by
rollup(deptno,sal)

统计 5

 

央诸一个列的咬合的小计记录:select deptno,sum(sal) from emp group by
cube(deptno,sal)

统计 6

 

聚合函数(within group语法):

 

select rank(1500) within group (order by sal desc) “rank of 1500” from
emp;

 

   
解释:如果存在同样长达记下,这漫漫记下之salary字段值为1500。那么以欠修记下插入emp表中后,按照sal字段降序排列后,该条记下之序号为多少?

   好用within
group关键字的函数有rank,dense_rank,PERCENT_RANK,PERCENTILE_CONT,PERCENTILE_DISC等

*   统计 7*

 

 

 5、单条记下插入多表

      原理:利用一个insert all 语法:insert all when .. then

     

INSERT ALL WHEN LOCALE = 1 THEN INTO EMPLOYEE1(ID, LOCALE, NAME, AGE,
GENDER, CODE) VALUES (ID, LOCALE, NAME, AGE, GENDER, CODE)

                  WHEN LOCALE = 32 THEN INTO EMPLOYEE2(ID, LOCALE, NAME,
AGE, GENDER, CODE) VALUES(ID, LOCALE, NAME, AGE, GENDER, CODE)

 

 

insert all into table values()

              into  table values()

 

 

 6、Merge的使用

 

 解释:DML语句,适用于批量拍卖

   MERGE INTO table_name alias1 
  USING (table|view|sub_query) alias2 
  ON (join condition) 
  WHEN MATCHED THEN 
  UPDATE table_name
  SET col1 = col_val1, 
  col2 = col2_val where 条件
  WHEN NOT MATCHED THEN 
  INSERT (column_list) VALUES (column_values) where 条件;

 

MERGE INTO EMPLOYEE E

USING (SELECT * FROM EMPLOYEE1) E1

ON (E.NAME = E1.NAME)

WHEN MATCHED THEN

    UPDATE SET E.CODE = E1.CODE, E.AGE = E1.AGE

WHEN NOT MATCHED THEN

   INSERT  (E.ID, E.LOCALE, E.NAME, E.AGE, E.GENDER, E.CODE)
VALUES(E1.ID, E1.LOCALE, E1.NAME, E1.AGE, E1.GENDER, E1.CODE);

 

备考:对有限布置表底少数个字段相配合,如果匹配上了不畏召开创新操作,否则即开插入操作。

 

 7、KEEP的使用

    
keep是Oracle下之其它一个分析函数,他的用法不同为经过over关键字指定的解析函数,可以用于这样同样种植场合下:取同一个分组下以某字段排序后,对点名字段取最小还是极可怜之死值。

   

  一般写法是 MIN [ MAX ] (A) KEEP(DENSE_RANK FIRST [ LAST ] ORDER
BY B),这里引用他人说的明的解释一下:

DENSE_RANK

成效描述:根据ORDER
BY子句被表达式的价值,从询问返回的各一样执,计算其同任何行的相对位置。组内的数码按ORDER
BY子句排序,然后为各级一样行赋一个号,从而形成一个列,该队从1起来,往后长。每次ORDER
BY表达式的价发生变化时,该队也跟着大增。有一致值的行得到同样的数字序号(认为null时当的)。密集的班返回的经常没距离的一再。

 

FIRST

作用描述:从DENSE_RANK返回的集合中取出排在极度前方的一个价值的实施(可能多行,因为价值可能相当于),因此整体的语法需要以初始处于加上一个集合函数以从中取出记录。

LAST

功用描述:从DENSE_RANK返回的联谊中取出排在最终对之一个价值的施行(可能大多尽,因为价值可能相当于),因此总体的语法需要在上马处于加上一个集合函数以从中取出记录。

故此默认排序下,FIRST可以知道是得到小值,LAST取大值。而眼前的MIN或者MAX则是在KEEP的结果集中取某一字段的太特别价值或极端小价。

 

 
keep和普通分析函数的界别:普通的辨析函数只是列出分组后底笔录,而针对性各个一个组的笔录进行统计分析。

                            keep对分组内之函数通过order
by和max(),min()选取某字段的价值。可以领略成这里的keep就是sum() groud by
deptno 前之sum()那样

 

  实例:取出各个部门薪资最高的职工编号

 
1、select deptno,empno,sal,max(empno) keep(dense_rank first order by sal desc) over(partition by deptno) from emp2

  2、select deptno,max(empno) keep(dense_rank first order by sal desc) from emp2 group by deptno 

        解释:按deptno分组,再指向分组中之sal降序,取出第一独sal的职工号

统计 8

 

 

8、SQL查询正则表达式的应用

 

  ORACLE中的支持正则表达式的函数主要有脚四独:
1,REGEXP_LIKE :与LIKE的功效相似

    select * from emp where regexp_like(empno,’7[0-9]{2}9′)
2,REGEXP_INSTR :与INSTR的功力相似

 

REGEXP_INSTR

6个参数

率先只是输入的字符串

第二独凡是正则表达式

其三个是标识由第几单字符开始正则表达式匹配。(默认为1)

季独凡是标识第几个匹配组。(默认为1)

第五个凡是乘定返回值的项目,如果该参数为0,则回回值为配合配位置的首先只字符,如果该值为非0则赶回匹配配值的最后一个职位。

第六独凡是凡取值范围:

i:大小写不灵活;

c:大小写敏感;

n:点号 . 不般配配换行符号;

m:多实行模式;

x:扩展模式,忽略正则表达式中之空白字符。

    SELECT REGEXP_INSTR(a,'[0-9]+’) AS A FROM test_reg_substr;

 

3,REGEXP_SUBSTR :与SUBSTR的效力相似

   

REGEXP_SUBSTR函数格式如下:

   function REGEXP_SUBSTR(String, pattern, position, occurrence,
modifier)
__srcstr     :需要进行正则处理的字符串
__pattern    :进行匹配的正则表达式
__position   :起始位置,从第几单字符开始正则表达式匹配(默认为1)
__occurrence :标识第几个匹配组,默认为1
__modifier  
:模式(’i’不区分轻重缓急写进行查找;’c’区分轻重缓急写进行搜索。默认为’c’。)

  1. –1、查询利用正则分后底率先单价值,也即是34  
  2. SELECT REGEXP_SUBSTR(‘34,56,-23′,'[^,]+’,1,1,’i’) AS STR FROM DUAL;  
  3. –结果是:34   
  4. –2、查询利用正则分后底末梢一个价,也不怕是-23  
  5. SELECT REGEXP_SUBSTR(‘34,56,-23′,'[^,]+’,1,3,’i’) AS STR FROM DUAL;  
  6. –结果是:-23  

4,REGEXP_REPLACE :与REPLACE的效力相似

 

 

 

 

    

9、常见函数

TRUNC:截取函数

EXTRACT:用于自一个date或者interval类型中截取到一定的片

NVL

DECODE

length:字符长度

lengthb:字节长度

ASCII

INITCAP:首字母大写

 

SOUNDEX:返回由四只字符组成的代码 (SOUNDEX) 以评估两独字符串的相似性

MONTHS_BETWEEN

ADD_MONTHS

NEXT_DAY

LAST_DAY

ROUND:函数用于把数值字段舍入为指定的粗数位数

 

10、分页函数

 

–普通写法

 

SELECT AA.FWZL, AA.FWTYBH

  FROM (SELECT A.FWZL, A.FWTYBH, ROWNUM RN

          FROM (SELECT F.FWZL, F.FWTYBH FROM FW F ORDER BY F.FWTYBH
DESC) A

         WHERE ROWNUM <= 120020) AA

 WHERE AA.RN > 120000;

 

–rowid写法

SELECT /*+ ROWID(FW) */ FW.FWZL, FW.FWTYBH

   FROM FW FW,

        (SELECT AA.RID, AA.RN

           FROM (SELECT A.RID, ROWNUM RN

                  FROM (SELECT /*+ index(F IDX_FW_FWTYBH) */

                         ROWID RID

                          FROM FW F

                          ORDER BY F.FWTYBH DESC) A

                 WHERE ROWNUM <= 120020) AA

          WHERE AA.RN > 120000) B

  WHERE FW.ROWID = B.RID;