一年中有多少个工作日(答案)

显示全部楼层 · 2008-2-13 12:43:03

select count(*) from
(select first_day,last_day,first_day+rownum-1,to_char(first_day+rownum,'d') d
from
(
select (select trunc(sysdate,'year') from dual) first_day,
(select trunc(add_months(sysdate,12),'year')-1 from dual) last_day
from dual
) t_year
connect by rownumselect count(*) from
2(select first_day,last_day,first_day+rownum-1,to_char(first_day+rownum,'d') d
3from
4(
5select (select trunc(sysdate,'year') from dual) first_day,
6(select trunc(add_months(sysdate,12),'year')-1 from dual) last_day
7from dual
8) t_year
9connect by rownumselect count(*) from
2(select first_day,last_day,first_day+rownum-1,to_char(first_day+rownum,'d') d
3from
4(
5select (select trunc(sysdate,'year') from dual) first_day,
6(select trunc(add_months(sysdate,12),'year')-1 from dual) last_day
7from dual
8) t_year
9connect by rownum
SQL> select * from
2(select first_day,last_day,first_day+rownum-1,to_char(first_day+rownum,'d') d
3from
4(
5select (select trunc(sysdate,'year') from dual) first_day,
6(select trunc(add_months(sysdate,12),'year')-1 from dual) last_day
7from dual
8) t_year
9connect by rownum
[/PHP]

千问 · 2008-2-13 12:43:03

写这么复杂做什么，看偶的：
select day, to_char(day, 'DY')
from (select trunc(sysdate, 'year') + rownum - 1 day

from dual
connect by rownum < 400)
where day <= trunc(add_months(sysdate, 12), 'year') - 1
and to_char(day, 'd') not in (7, 1)

千问 · 2008-2-13 12:43:03

的确简单了，不过 to_char(day, 'd') not in (7, 1)后面应该用字符型吧，虽然可以自动转换！

千问 · 2008-2-13 12:43:03

想问下，为何
select trunc(sysdate, 'year') + rownum - 1 day
from dual
connect by rownum < 400
这个查询只能显示100条，为何整个sql结果却能查出超过100呢？？

千问 · 2008-2-13 12:43:03

http://www.itpub.net/showthread. ... =%B9%A4%D7%F7%C8%D5

千问 · 2008-2-13 12:43:03

通过一个表达式计算就可以了哇

千问 · 2008-2-13 12:43:03

最初由 zhouwf0726 发布
[B]http://www.itpub.net/showthread. ... =%B9%A4%D7%F7%C8%D5 [/B]
多谢了，估计是显示的问题，10g就可以了
应该是oracle限制了connect by的显示！