问题描述:
假如今天是 2008-09-08 ,我要写一句SQL ,生成从当前月 1 号 到今天的日期字符串来作为一个内存表,然后跟别的表做关联
假如表结构如下,但不是真实存在的表,只是在每次运行时用一个 SQL 语句实现,改如何写啊。
DATEDESC
---------------------
2008-09-01
---------------------
2008-09-02
---------------------
2008-09-03
---------------------
2008-09-04
---------------------
2008-09-05
---------------------
2008-09-06
---------------------
2008-09-07
---------------------
2008-09-08
---------------------
假如今天是 2008-09-08 ,我要写一句SQL ,生成从当前月 1 号 到今天的日期字符串来作为一个内存表,然后跟别的表做关联
假如表结构如下,但不是真实存在的表,只是在每次运行时用一个 SQL 语句实现,改如何写啊。
DATEDESC
---------------------
2008-09-01
---------------------
2008-09-02
---------------------
2008-09-03
---------------------
2008-09-04
---------------------
2008-09-05
---------------------
2008-09-06
---------------------
2008-09-07
---------------------
2008-09-08
---------------------
在视图里用
select * from dual connect by rownum < 151 之类的方法
SELECT TRUNC (:in_date, 'mm') + ROWNUM - 1 days
FROM DUAL
CONNECT BY ROWNUM <= TRUNC (SYSDATE) - TRUNC (:in_date, 'mm') + 1
结果DAYS2008/9/1
2008/9/2
2008/9/3
2008/9/4
2008/9/5
2008/9/6
2008/9/7
2008/9/8
2008/9/9
2008/9/10
2008/9/11
2008/9/12
2008/9/13
2008/9/14
2008/9/15
2008/9/16
2008/9/17
2008/9/18
2008/9/19
2008/9/20
2008/9/21
2008/9/22
2008/9/23
2008/9/24
2008/9/25
2008/9/26
2008/9/27
SQL> SELECT TRUNC(&END_DATE,'MM') + ROWNUM - 1 "DAYS"
2 FROM ALL_OBJECTS
3 WHERE ROWNUM <= (SELECT TRUNC(&END_DATE) - TRUNC(&END_DATE,'MM') FROM DUAL) + 1;DAYS
-----------
2008-9-1
2008-9-2
2008-9-3
2008-9-4
2008-9-5
2008-9-6
2008-9-7
2008-9-8
2008-9-9
2008-9-10
2008-9-11
2008-9-12
2008-9-13
2008-9-14
2008-9-15
2008-9-1616 rows selected-- 2ND WAY:
SQL> SELECT TRUNC(&END_DATE,'MM') + ROWNUM - 1 "DAYS"
2 FROM DUAL
3 CONNECT BY ROWNUM <= (SELECT TRUNC(&END_DATE) - TRUNC(&END_DATE,'MM') FROM DUAL);DAYS
-----------
2008-9-1
2008-9-2
2008-9-3
2008-9-4
2008-9-5
2008-9-6
2008-9-7
2008-9-8
2008-9-9
2008-9-10
2008-9-11
2008-9-12
2008-9-13
2008-9-14
2008-9-15
2008-9-1616 rows selectedSQL>
谢谢各位了
FROM dddddd
where ROWNUM <= (select TRUNC (SYSDATE) - TRUNC (SYSDATE, 'mm') + 1 from dual)感觉这个语句挺好用的,只要ddddd这个表数据超过最大天数31,就肯定好用,呵呵,可以随便是哪个表明