--员工表emp,如果为其它月份,请替换201005 with emp as( select 7369 empid, '张三' name from dual union all select 7499 empid, '李四' name from dual union all select 7521 empid, '王五' name from dual union all select 7566 empid, '刘六' name from dual union all select 7654 empid, '陈七' name from dual union all select 7698 empid, '刘一' name from dual union all select 7782 empid, '高四' name from dual union all select 7788 empid, '杜九' name from dual union all select 7839 empid, '李明' name from dual union all select 7844 empid, '赵二' name from dual union all select 7876 empid, '孙三' name from dual union all select 7900 empid, '红九' name from dual union all select 7902 empid, '马二' name from dual)SELECT n.pdate,n.rn,m.empid,m.name FROM (SELECT rownum tid, empid, NAME FROM (SELECT * FROM emp ORDER BY empid)) m, (SELECT MOD(rownum, (SELECT COUNT(1)+1 FROM emp)) tid, pdate, rn FROM (SELECT to_date('201005', 'yyyymm') + LEVEL - 1 pdate FROM dual CONNECT BY LEVEL <= last_day(to_date('201005', 'yyyymm')) - to_date('201005', 'yyyymm') + 1) a, (SELECT LEVEL rn FROM dual CONNECT BY LEVEL <= 2) b) n WHERE m.tid = n.tid order by n.pdate,n.rn
你好,又稍微改了一下 SELECT decode(MOD(rownum, (SELECT COUNT(1) FROM emp)), 0, (SELECT COUNT(1) FROM emp), MOD(rownum, (SELECT COUNT(1) FROM emp))) tid, pdate, rn FROM (SELECT to_date('201005', 'yyyymm') + LEVEL - 1 pdate FROM dual CONNECT BY LEVEL <= last_day(to_date('201005', 'yyyymm')) - to_date('201005', 'yyyymm') + 1) a, (SELECT LEVEL rn FROM dual CONNECT BY LEVEL <= 2) b谢谢您的帮忙。学习了
with emp as(
select 7369 empid, '张三' name from dual union all
select 7499 empid, '李四' name from dual union all
select 7521 empid, '王五' name from dual union all
select 7566 empid, '刘六' name from dual union all
select 7654 empid, '陈七' name from dual union all
select 7698 empid, '刘一' name from dual union all
select 7782 empid, '高四' name from dual union all
select 7788 empid, '杜九' name from dual union all
select 7839 empid, '李明' name from dual union all
select 7844 empid, '赵二' name from dual union all
select 7876 empid, '孙三' name from dual union all
select 7900 empid, '红九' name from dual union all
select 7902 empid, '马二' name from dual)SELECT n.pdate,n.rn,m.empid,m.name
FROM (SELECT rownum tid, empid, NAME
FROM (SELECT * FROM emp ORDER BY empid)) m,
(SELECT MOD(rownum, (SELECT COUNT(1)+1 FROM emp)) tid, pdate, rn
FROM (SELECT to_date('201005', 'yyyymm') + LEVEL - 1 pdate
FROM dual
CONNECT BY LEVEL <= last_day(to_date('201005', 'yyyymm')) -
to_date('201005', 'yyyymm') + 1) a,
(SELECT LEVEL rn FROM dual CONNECT BY LEVEL <= 2) b) n
WHERE m.tid = n.tid order by n.pdate,n.rn
你好,又稍微改了一下
SELECT decode(MOD(rownum, (SELECT COUNT(1) FROM emp)), 0, (SELECT COUNT(1) FROM emp), MOD(rownum, (SELECT COUNT(1) FROM emp))) tid,
pdate,
rn
FROM (SELECT to_date('201005', 'yyyymm') + LEVEL - 1 pdate
FROM dual
CONNECT BY LEVEL <= last_day(to_date('201005', 'yyyymm')) - to_date('201005', 'yyyymm') + 1) a,
(SELECT LEVEL rn FROM dual CONNECT BY LEVEL <= 2) b谢谢您的帮忙。学习了