Oracle的sql常用技巧_oracle sql解题技巧总结

1.Oracle产生随机数     oracle数据库中有产生随机数的函数  DBMS_RANDOM

一）生成0到1之间的小数，包括0不包括1

SELECT DBMS_RANDOM.VALUE FROM DUAL;

二）生成随机正态分布数

SELECT DBMS_RANDOM.VALUE(0，100) FROM DUAL;    //生成0-100范围内的小数，包括0不包括100

三）生成指定范围内的整数

SELECT TRUNC(DBMS_RANDOM.VALUE(0，100)) FROM DUAL;  //生成0-100范围内的整数，包括0不包括100  

四)   生成正态分布的随机数                           

 SELECT DBMS_RANDOM.NORMAL FROM DUAL; 

五）生成随机字符串                                       

SELECT DBMS_RANDOM.STRING('参数1'  ,  '参数2') FROM DUAL;  

必须要指定两个参数，参数1用于指定生成随机字符串的类型，参数2用于指定生成随机字符串的长度。 其中参数1 的取值有：‘U’ 指生成大写的字符       ‘L’  指生成小写的字符        ‘X’ 数字和大写的字母       ‘A’  大小写混合的字符        ‘P’ 可打印的字符

例如：    SELECT DBMS_RANDOM.STRING('X', 3) FROM DUAL;     //生成长度为3的  类型是大写字母和数字的字符串                                                                                                    

六）生成预期范围内的日期，比如我想要从2016年1月1日到2016年年底内任意的日期类型的数据，注意没有时分秒的具体数值。

第一步：    获得一个2016年1月1日的基数  SELECT TO_CHAR(TO_DATE('01/01/2016','MM/DD/YYYY','J'))                //得到一个2457389基数 

第二步：    SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2457389,2457389+364)), 'J' ) FROM DUAL;        //得到2016-01-01 至 2016-12-30的随机日期

2.常用的处理函数

3.   case   when 函数

比如可以用case函数统计 分数在一些区间段的 人数的总数                                 
select                                                                                                                         
        case                                                                                                                               
                when tb.score >= 80     then   '优秀'及格                           
                when tb.score <80 and tb.score >= 60    then   '及格'
                else '不及格'
        end   区间,
        concat(count(1),'人')   人数
from tablename tb
group by 
        case
               when tb.score >= 80   then   '优秀'
               when tb.score <80 and tb.score >= 60   then   '及格'
               else   '不及格'
        end;

查询结果如下显示：

可以对一个表中的不同类型的数据做一个统计
select 
          country,
          sum(case when tb.sex=1 then '1' else '0' end)         男性人口,
          sum(case when tb.sex=2 then '2' else '0' end)         女性人口
from tablename tb
group by country

可以和update结合
update table 
set salary = 
     case  when sarlay >= 8000 then salary*0.90
                when salary <4000 then salary*1015
                else salary 
      end
这样可以一次就对整张表进行了更新，并且不会有数据前后产生的错误性

4. ORACLE的MERGE INTO 用法

 /*語法：
MERGE [INTO [schema .] table [t_alias] 
USING [schema .] { table | view | subquery } [t_alias] 
ON ( condition ) 
WHEN MATCHED THEN merge_update_clause 
WHEN NOT MATCHED THEN merge_insert_clause;
*/


/*语法
MERGE INTO [your table-name] [rename your table here]
USING ( [write your query here] )[rename your query-sql and using just like a table]
ON ([conditional expression here] AND [...]...)
WHEN MATHED THEN [here you can execute some update sql or something else ]
WHEN NOT MATHED THEN [execute something else here ! ]
*/

5.  如何把一个子查询的多个选择排成一列显示

Oracle中提供了两个函数
一、WMSYS.WM_CONCAT(table_name) 这个函数默认是把查出来的数据用逗号连接  数据类型是<CLOB>类型
例如 表名  table A

SELECT WMSYS.WM_CONCAT(NAME) FROM A      

查询出来的结果是 AA,BB,CC

SELECT REPLACE(WMSYS.WM_CONCAT(NAME),',','|') FROM A  

查询出来的结果是AA|BB|CC
 
二、可以使用LISTAGG函数表名B       

                                                                                                                                                                                                                         
SELECT    DEPT_NO,    LISTAGG(NAME,'|')      WITHIN      GROUP     (ORDER BY NAME)     AS     NAMES      FROM      B      GROUP     BY      DEPT_NO

查询出来的结果是 dept_no names

注意：WITHIN GROUP (ORDER BY ***) 字段不能少  这是聚合分析类函数