oracle关于取数的小知识

oracle关于取数的小知识

• Oracle随机取数据的基本方法

--随机取100条数据从pucentp表中；
  SELECT * FROM (SELECT * FROM pucentp ORDER BY trunc(DBMS_RANDOM.RANDOM())) WHERE 1=1 and rownum<= 100;
  SELECT * FROM (SELECT * FROM pucentp ORDER BY trunc(DBMS_RANDOM.VALUE())) WHERE 1=1 and rownum<= 100;
  DBMS_RANDOM.VALUE()比DBMS_RANDOM.RANDOM()提取数据速度要快，一般用前者；
--从pucentp表中抽取20%的数据然后从20%的数据中随机抽取5条数据；
SELECT * FROM  pucentp SAMPLE (20) WHERE   ROWNUM <= 5;
SELECT * FROM  pucentp SAMPLE block (20) WHERE   ROWNUM <= 5;
SELECT * FROM  pucentp SAMPLE (20) seed(3) WHERE   ROWNUM <= 5;
SELECT * FROM  pucentp SAMPLE block (20) seed(11) WHERE   ROWNUM <= 5;

• oracle统计从当前年开始取前5年数据表中数据的总数的基本方法

select to_char(currentDate,'yyyy') currentYear,count(id) sss from  pucship where ( to_number(to_char(sysdate,'yyyy')) - to_number(to_char(currentDate,'yyyy')) )  <= 4 group by  to_char(currentDate,'yyyy') order by to_number(to_char(currentDate,'yyyy')) asc;
--currentDate为表中已经存在的日期字段，然后根据当前年份从你表中（匹配）近5年的数据