select t.adate from (select to_date('1999-01-01','yyyy-mm-dd')+level-1 adate,row_number() over(order by dbms_random.value) rn from dual where to_char(to_date('1999-01-01','yyyy-mm-dd')+level-1,'day')not in ('星期六','星期日') connect by level <= to_date('2013-07-01','yyyy-mm-dd')-to_date('1999-01-01','yyyy-mm-dd')+1 ) t where t.rn=1;
WITH T AS( /* NUMTODSINTERVAL函数: 产生日期间隔DAY TRUNC函数:切断随机数的小数部分 DBMS_RANDOM:产生从1 到两个日期间隔int值的随机数 DATE-DATE:计算结果是天数 */ SELECT NUMTODSINTERVAL(TRUNC(DBMS_RANDOM.VALUE(1, TO_DATE('2013-07-01', 'YYYY-MM-DD') - TO_DATE('1999-01-01', 'YYYY-MM-DD'))), 'DAY') AS R_INTERVAL FROM DUAL ) --日期+随机的时间间隔 --LEVEL构造伪劣,产生10个随机天数 SELECT TO_DATE('1999-01-01','YYYY-MM-DD') + R_INTERVAL FROM T CONNECT BY LEVEL<=10;
...周六和周日除外 WITH T AS( SELECT TO_DATE('1999-01-01','YYYY-MM-DD') + NUMTODSINTERVAL(TRUNC(DBMS_RANDOM.VALUE(1, TO_DATE('2013-07-01', 'YYYY-MM-DD') - TO_DATE('1999-01-01', 'YYYY-MM-DD'))), 'DAY') AS R_DATE FROM DUAL CONNECT BY LEVEL<=10 ) SELECT CASE WHEN TO_CHAR(R_DATE - 1) IN ('6', '7') THEN NEXT_DAY(R_DATE, '星期一') ELSE R_DATE END AS D FROM T
-- 不用递归可能会快一些,但上下边界值可能不是产生: WITH a1 AS (SELECT TRUNC((to_date('2013-07-01','YYYY-MM-DD')-to_date('1999-01-01','YYYY-MM-DD'))/7) c1 FROM dual) SELECT to_date('1999-01-01','YYYY-MM-DD')+TRUNC(DBMS_RANDOM.VALUE(0,a1.c1))*7+dbms_random.value(3,8) FROM DUAL, a1;---------------- CREATE TABLE t1(cdate date); BEGIN FOR i IN 1.. 100000 LOOP insert into t1(cdate) SELECT to_date('1999-01-01','YYYY-MM-DD')+TRUNC(DBMS_RANDOM.VALUE(0,TRUNC((to_date('2013-07-01','YYYY-MM-DD')-to_date('1999-01-01','YYYY-MM-DD'))/7)))*7+dbms_random.value(3,7.9999999) FROM DUAL; COMMIT; END LOOP; END; /SELECT * FROM t1 WHERE to_char(t1.cdate,'D') IN ('1','7');select to_char(sysdate,'D') FROM dual;SELECT MIN(CDATE) C1, MAX(CDATE) C2 FROM T1;
from (select to_date('1999-01-01','yyyy-mm-dd')+level-1 adate,row_number() over(order by dbms_random.value) rn
from dual
where to_char(to_date('1999-01-01','yyyy-mm-dd')+level-1,'day')not in ('星期六','星期日')
connect by level <= to_date('2013-07-01','yyyy-mm-dd')-to_date('1999-01-01','yyyy-mm-dd')+1
) t
where t.rn=1;
方法上可行,不过有点浪费cpu和内存了。
可以折衷下,例如
DECLARE
VN_A NUMBER;
vn_random number;
vt_day date;
BEGIN
VN_A:="2013-07-01"-"1900-01-01" ; --写个意思.
vn_random:=dbms_random.value(1,vn_a);
vt_day:="1900-01-01"+vn_random;
--然后如果vt_day是周六或者周日那么就切换为下一个周一或者上个周五。
END;这样应该能够满足需要。
/*
NUMTODSINTERVAL函数: 产生日期间隔DAY
TRUNC函数:切断随机数的小数部分
DBMS_RANDOM:产生从1 到两个日期间隔int值的随机数
DATE-DATE:计算结果是天数
*/
SELECT NUMTODSINTERVAL(TRUNC(DBMS_RANDOM.VALUE(1,
TO_DATE('2013-07-01',
'YYYY-MM-DD') -
TO_DATE('1999-01-01',
'YYYY-MM-DD'))),
'DAY') AS R_INTERVAL
FROM DUAL
)
--日期+随机的时间间隔
--LEVEL构造伪劣,产生10个随机天数
SELECT TO_DATE('1999-01-01','YYYY-MM-DD') + R_INTERVAL FROM T CONNECT BY LEVEL<=10;
WITH T AS(
SELECT TO_DATE('1999-01-01','YYYY-MM-DD') + NUMTODSINTERVAL(TRUNC(DBMS_RANDOM.VALUE(1,
TO_DATE('2013-07-01',
'YYYY-MM-DD') -
TO_DATE('1999-01-01',
'YYYY-MM-DD'))),
'DAY') AS R_DATE
FROM DUAL CONNECT BY LEVEL<=10
)
SELECT CASE
WHEN TO_CHAR(R_DATE - 1) IN ('6', '7') THEN
NEXT_DAY(R_DATE, '星期一')
ELSE
R_DATE
END AS D
FROM T
WITH a1 AS (SELECT TRUNC((to_date('2013-07-01','YYYY-MM-DD')-to_date('1999-01-01','YYYY-MM-DD'))/7) c1
FROM dual)
SELECT to_date('1999-01-01','YYYY-MM-DD')+TRUNC(DBMS_RANDOM.VALUE(0,a1.c1))*7+dbms_random.value(3,8)
FROM DUAL, a1;----------------
CREATE TABLE t1(cdate date);
BEGIN
FOR i IN 1.. 100000 LOOP
insert into t1(cdate)
SELECT to_date('1999-01-01','YYYY-MM-DD')+TRUNC(DBMS_RANDOM.VALUE(0,TRUNC((to_date('2013-07-01','YYYY-MM-DD')-to_date('1999-01-01','YYYY-MM-DD'))/7)))*7+dbms_random.value(3,7.9999999)
FROM DUAL;
COMMIT;
END LOOP;
END;
/SELECT * FROM t1
WHERE to_char(t1.cdate,'D') IN ('1','7');select to_char(sysdate,'D') FROM dual;SELECT MIN(CDATE) C1, MAX(CDATE) C2 FROM T1;