--查询时间段内有几个周四 select count(*) from tab where 时间段(startdate-enddate)年月格式查询 and to_char(sysdate,'day') ='星期四'2 select trunc(sysdate,'mm') 本月第一天, last_day(sysdate) 本月最后第一天 FROM DUAL
查询从2010年1月到6月,每月有多少个周四select to_char(d, 'yyyymm'), count(d) from ( select next_day(to_date('20100101','yyyymmdd'), 'THURSDAY') + (level - 1) * 7 d from dual connect by level < (to_date('20100630','yyyymmdd') - to_date('20100101','yyyymmdd')) / 7 ) where d between to_date('20100101','yyyymmdd') and to_date('20100630','yyyymmdd') group by to_char(d, 'yyyymm') order by 1
因为要球个数,没有列出具体日期,你自己改改就可以显式所有周四的日期了select to_char(d, 'yyyymm'), count(d) from ( select next_day(to_date('201001','yyyymm'), 'THURSDAY') + (level - 1) * 7 d from dual connect by level < (decode('201005', to_char(sysdate,'yyyymm'), trunc(sysdate), to_date('201005','yyyymm') + interval '1' month - 1) - to_date('201001','yyyymm')) / 7 ) where d between to_date('201001','yyyymm') and decode('201005', to_char(sysdate,'yyyymm'), trunc(sysdate),to_date('201005','yyyymm') + interval '1' month - 1) group by to_char(d, 'yyyymm') order by 1
connect by level < 改成 connect by level <=
select d as "日期", to_char(d, 'yyyymm') as "月份", count(d) over (partition by to_char(d, 'yyyymm')) as "周四个数" from ( select next_day(to_date('201001','yyyymm'), 'THURSDAY') + (level - 1) * 7 d from dual connect by level <= (decode('201004', to_char(sysdate,'yyyymm'), trunc(sysdate), to_date('201004','yyyymm') + interval '1' month - 1) - to_date('201001','yyyymm')) / 7 ) where d between to_date('201001','yyyymm') and decode('201004', to_char(sysdate,'yyyymm'), trunc(sysdate),to_date('201004','yyyymm') + interval '1' month - 1) order by 1日期 月份 周四个数 --------- --------- ---------- 07-JAN-10 201001 4 14-JAN-10 201001 4 21-JAN-10 201001 4 28-JAN-10 201001 4 04-FEB-10 201002 4 11-FEB-10 201002 4 18-FEB-10 201002 4 25-FEB-10 201002 4 04-MAR-10 201003 4 11-MAR-10 201003 4 18-MAR-10 201003 4 25-MAR-10 201003 4 01-APR-10 201004 5 08-APR-10 201004 5 15-APR-10 201004 5 22-APR-10 201004 5 29-APR-10 201004 5
SQL> with tmp 2 as( 3 select to_date('10-01','yy-mm') start_time, 4 decode('10-04',to_char(sysdate,'yy-mm'),trunc(sysdate,'dd'),last_day(to_date('10-04','yy-mm'))) end_time 5 from dual 6 ) 7 select ddate,to_char(ddate,'yyyymm') ym,count(*) over(partition by trunc(ddate,'mm')) cnt from ( 8 select start_time+level-1 ddate from tmp 9 connect by level <= end_time-start_time+1 10 ) where to_char(ddate,'d')='5' 11 order by ddate;
select count(*) from tab
where 时间段(startdate-enddate)年月格式查询
and to_char(sysdate,'day') ='星期四'2
select trunc(sysdate,'mm') 本月第一天, last_day(sysdate) 本月最后第一天 FROM DUAL
(
select next_day(to_date('20100101','yyyymmdd'), 'THURSDAY') + (level - 1) * 7 d
from dual
connect by level < (to_date('20100630','yyyymmdd') - to_date('20100101','yyyymmdd')) / 7
)
where d between to_date('20100101','yyyymmdd') and to_date('20100630','yyyymmdd')
group by to_char(d, 'yyyymm')
order by 1
而且也没判断enddate是否为当前月,若为当前月必须求当月实际发生的周四日期和个数。请再修改一下吧!
(
select next_day(to_date('201001','yyyymm'), 'THURSDAY') + (level - 1) * 7 d
from dual
connect by level <
(decode('201005',
to_char(sysdate,'yyyymm'), trunc(sysdate),
to_date('201005','yyyymm') + interval '1' month - 1) - to_date('201001','yyyymm')) / 7
)
where d between to_date('201001','yyyymm') and decode('201005', to_char(sysdate,'yyyymm'), trunc(sysdate),to_date('201005','yyyymm') + interval '1' month - 1)
group by to_char(d, 'yyyymm')
order by 1
改成
connect by level <=
select d as "日期", to_char(d, 'yyyymm') as "月份", count(d) over (partition by to_char(d, 'yyyymm')) as "周四个数" from
(
select next_day(to_date('201001','yyyymm'), 'THURSDAY') + (level - 1) * 7 d
from dual
connect by level <=
(decode('201004',
to_char(sysdate,'yyyymm'), trunc(sysdate),
to_date('201004','yyyymm') + interval '1' month - 1) - to_date('201001','yyyymm')) / 7
)
where d between to_date('201001','yyyymm') and decode('201004', to_char(sysdate,'yyyymm'), trunc(sysdate),to_date('201004','yyyymm') + interval '1' month - 1)
order by 1日期 月份 周四个数
--------- --------- ----------
07-JAN-10 201001 4
14-JAN-10 201001 4
21-JAN-10 201001 4
28-JAN-10 201001 4
04-FEB-10 201002 4
11-FEB-10 201002 4
18-FEB-10 201002 4
25-FEB-10 201002 4
04-MAR-10 201003 4
11-MAR-10 201003 4
18-MAR-10 201003 4
25-MAR-10 201003 4
01-APR-10 201004 5
08-APR-10 201004 5
15-APR-10 201004 5
22-APR-10 201004 5
29-APR-10 201004 5
2 as(
3 select to_date('10-01','yy-mm') start_time,
4 decode('10-04',to_char(sysdate,'yy-mm'),trunc(sysdate,'dd'),last_day(to_date('10-04','yy-mm'))) end_time
5 from dual
6 )
7 select ddate,to_char(ddate,'yyyymm') ym,count(*) over(partition by trunc(ddate,'mm')) cnt from (
8 select start_time+level-1 ddate from tmp
9 connect by level <= end_time-start_time+1
10 ) where to_char(ddate,'d')='5'
11 order by ddate;
DDATE YM CNT
----------- ------ ----------
2010/1/7 201001 4
2010/1/14 201001 4
2010/1/21 201001 4
2010/1/28 201001 4
2010/2/4 201002 4
2010/2/11 201002 4
2010/2/18 201002 4
2010/2/25 201002 4
2010/3/4 201003 4
2010/3/11 201003 4
2010/3/18 201003 4
2010/3/25 201003 4
2010/4/1 201004 5
2010/4/8 201004 5
2010/4/15 201004 5
2010/4/22 201004 5
2010/4/29 201004 5
17 rows selected
SQL>
SQL> with tmp
2 as(
3 select to_date('10-01','yy-mm') start_time,
4 decode('10-05',to_char(sysdate,'yy-mm'),trunc(sysdate,'dd'),last_day(to_date('10-05','yy-mm'))) end_time
5 from dual
6 )
7 select ddate,to_char(ddate,'yyyymm') ym,count(*) over(partition by trunc(ddate,'mm')) cnt from (
8 select start_time+level-1 ddate from tmp
9 connect by level <= end_time-start_time+1
10 ) where to_char(ddate,'d')='5'
11 order by ddate;
DDATE YM CNT
----------- ------ ----------
2010/1/7 201001 4
2010/1/14 201001 4
2010/1/21 201001 4
2010/1/28 201001 4
2010/2/4 201002 4
2010/2/11 201002 4
2010/2/18 201002 4
2010/2/25 201002 4
2010/3/4 201003 4
2010/3/11 201003 4
2010/3/18 201003 4
2010/3/25 201003 4
2010/4/1 201004 5
2010/4/8 201004 5
2010/4/15 201004 5
2010/4/22 201004 5
2010/4/29 201004 5
2010/5/6 201005 2
2010/5/13 201005 2
19 rows selected