ORACLE版本
获取一年的序号、日期、星期
SELECT
ROWNUM AS ID,
TO_DATE ( '20180101', 'YYYYMMDD') + ROWNUM - 1 RQ,
TO_CHAR (
TO_DATE ('20180101', 'YYYYMMDD') + ROWNUM - 1,
'D'
) WEEK
FROM
(
SELECT
ROWNUM
FROM
DUAL CONNECT BY ROWNUM <= TO_DATE ('20181231', 'YYYYMMDD') - TO_DATE ('20180101', 'YYYYMMDD') + 1
)
结果如图,365条结果行
嵌套一层,获取工作日,可自行筛选掉节假日(示例去除了元旦、五一、十一)
SELECT
*
FROM
(
SELECT
ROWNUM AS ID,
TO_DATE ('20180101', 'YYYYMMDD') + ROWNUM - 1 RQ,
TO_CHAR (
TO_DATE ('20180101', 'YYYYMMDD') + ROWNUM - 1,
'D'
) WEEK
FROM
(
SELECT
ROWNUM
FROM
DUAL CONNECT BY ROWNUM <= TO_DATE ('20181231', 'YYYYMMDD') - TO_DATE ('20180101', 'YYYYMMDD') + 1
)
)
WHERE
WEEK N