oracle关于取数的小知识
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()提取数据速度要快,一般用前者;
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;