利用伪列算出每个月那几天是星期天(ORACLE)

1.首先我们要用到LEVEL .... CONNECT BY
level是一个伪列,类似于rownum,而后面加connect by则可以构造出一个循环来;

例如:
SQL>select level from dual connect by level<=5;
结果
-----------------------------------------------
LEVEL
  1
  2
  3
  4
  5

一个伪列并循环显示就这样产生了.

2.接着我要构造出一个日期的伪列出来,需要用到日期函数,比如现在是12月,我需要构造出来2007年12月1日----2007年12月31日的伪列.

SQL>select last_day(sysdate) from dual;

结果
-----------------------------------------------
1       2007-12-31 1:22:24

SQL>select to_char(last_day(sysdate),'DD') from dual;

结果
-----------------------------------------------
1       31

SQL>select level from dual connect by level<=to_char(last_day(sysdate),'DD');

结果
-----------------------------------------------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
20 20
21 21
22 22
23 23
24 24
25 25
26 26
27 27
28 28
29 29
30 30
31 31

SQL>select TRUNC(SYSDATE,'MM') from dual;

结果
-----------------------------------------------
1       2007-12-1

接着我们要做的事情就是把上面的函数结合起来实现2007年12月1日----2007年12月31日的伪列

SQL>select TRUNC(SYSDATE,'MM')-1+level from dual connect by level<=to_char(last_day(sysdate),'DD');

结果
-----------------------------------------------
1 2007-12-1
2 2007-12-2
3 2007-12-3
4 2007-12-4
5 2007-12-5
6 2007-12-6
7 2007-12-7
8 2007-12-8
9 2007-12-9
10 2007-12-10
11 2007-12-11
12 2007-12-12
13 2007-12-13
14 2007-12-14
15 2007-12-15
16 2007-12-16
17 2007-12-17
18 2007-12-18
19 2007-12-19
20 2007-12-20
21 2007-12-21
22 2007-12-22
23 2007-12-23
24 2007-12-24
25 2007-12-25
26 2007-12-26
27 2007-12-27
28 2007-12-28
29 2007-12-29
30 2007-12-30
31 2007-12-31

3.构造出日期伪列后，我们只需要加上一个where条件既可以查询出这个月的星期日都是几号了

SQL>select to_char(sysdate,'DAY') from dual;

结果
-----------------------------------------------
1 星期六

SQL>select TRUNC(SYSDATE,'MM')-1+level from dual
SQL>where to_char(TRUNC(SYSDATE,'MM')-1+level,'DAY') = '星期日'
SQL>connect by level<=to_char(last_day(sysdate),'DD');

结果
-----------------------------------------------
1 2007-12-2
2 2007-12-9
3 2007-12-16
4 2007-12-23
5 2007-12-30

也可以把sql语句换成

SQL>select TRUNC(SYSDATE,'MM')-1+level from dual
SQL>where to_char(TRUNC(SYSDATE,'MM')-1+level,'D') = 1
SQL>connect by level<=to_char(last_day(sysdate),'DD');

1.首先我们要用到LEVEL .... CONNECT BY
level是一个伪列,类似于rownum,而后面加connect by则可以构造出一个循环来;

例如:
SQL>select level from dual connect by level<=5;
结果
-----------------------------------------------
LEVEL
  1
  2
  3
  4
  5

一个伪列并循环显示就这样产生了.

2.接着我要构造出一个日期的伪列出来,需要用到日期函数,比如现在是12月,我需要构造出来2007年12月1日----2007年12月31日的伪列.

SQL>select last_day(sysdate) from dual;

结果
-----------------------------------------------
1       2007-12-31 1:22:24

SQL>select to_char(last_day(sysdate),'DD') from dual;

结果
-----------------------------------------------
1       31

SQL>select level from dual connect by level<=to_char(last_day(sysdate),'DD');

结果
-----------------------------------------------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
20 20
21 21
22 22
23 23
24 24
25 25
26 26
27 27
28 28
29 29
30 30
31 31

SQL>select TRUNC(SYSDATE,'MM') from dual;

结果
-----------------------------------------------
1       2007-12-1

接着我们要做的事情就是把上面的函数结合起来实现2007年12月1日----2007年12月31日的伪列

SQL>select TRUNC(SYSDATE,'MM')-1+level from dual connect by level<=to_char(last_day(sysdate),'DD');

结果
-----------------------------------------------
1 2007-12-1
2 2007-12-2
3 2007-12-3
4 2007-12-4
5 2007-12-5
6 2007-12-6
7 2007-12-7
8 2007-12-8
9 2007-12-9
10 2007-12-10
11 2007-12-11
12 2007-12-12
13 2007-12-13
14 2007-12-14
15 2007-12-15
16 2007-12-16
17 2007-12-17
18 2007-12-18
19 2007-12-19
20 2007-12-20
21 2007-12-21
22 2007-12-22
23 2007-12-23
24 2007-12-24
25 2007-12-25
26 2007-12-26
27 2007-12-27
28 2007-12-28
29 2007-12-29
30 2007-12-30
31 2007-12-31

3.构造出日期伪列后，我们只需要加上一个where条件既可以查询出这个月的星期日都是几号了

SQL>select to_char(sysdate,'DAY') from dual;

结果
-----------------------------------------------
1 星期六

SQL>select TRUNC(SYSDATE,'MM')-1+level from dual
SQL>where to_char(TRUNC(SYSDATE,'MM')-1+level,'DAY') = '星期日'
SQL>connect by level<=to_char(last_day(sysdate),'DD');

结果
-----------------------------------------------
1 2007-12-2
2 2007-12-9
3 2007-12-16
4 2007-12-23
5 2007-12-30

也可以把sql语句换成

SQL>select TRUNC(SYSDATE,'MM')-1+level from dual
SQL>where to_char(TRUNC(SYSDATE,'MM')-1+level,'D') = 1
SQL>connect by level<=to_char(last_day(sysdate),'DD');

结果
-----------------------------------------------
1 2007-12-2
2 2007-12-9
3 2007-12-16
4 2007-12-23
5 2007-12-30

数字1-7分别代表星期天到星期六;
OK到此分析完毕,Oracle的伪列的功能还是很不错的呀！~呵呵

结果
-----------------------------------------------
1 2007-12-2
2 2007-12-9
3 2007-12-16
4 2007-12-23
5 2007-12-30

数字1-7分别代表星期天到星期六;
OK到此分析完毕,Oracle的伪列的功能还是很不错的呀！~呵呵