表数据ID Name CreateDate
0 aa 2009-05-04
1 aa 2009-05-05
2 aa 2009-05-06
3 aa 2009-05-08
4 aa 2009-05-08SQL 查询后得到的数据(有一个日期范围 2009-05-03 和 2009-05-09) ID Name CreateDate Date Week
2009-05-03 星期日
0 aa 2009-05-04 2009-05-04 星期一
1 aa 2009-05-05 2009-05-05 星期二
2 aa 2009-05-06 2009-05-06 星期三
2009-05-07 星期四
3 aa 2009-05-08 2009-05-08 星期五
4 aa 2009-05-08 2009-05-08 星期五
0 aa 2009-05-04
1 aa 2009-05-05
2 aa 2009-05-06
3 aa 2009-05-08
4 aa 2009-05-08SQL 查询后得到的数据(有一个日期范围 2009-05-03 和 2009-05-09) ID Name CreateDate Date Week
2009-05-03 星期日
0 aa 2009-05-04 2009-05-04 星期一
1 aa 2009-05-05 2009-05-05 星期二
2 aa 2009-05-06 2009-05-06 星期三
2009-05-07 星期四
3 aa 2009-05-08 2009-05-08 星期五
4 aa 2009-05-08 2009-05-08 星期五
然后左连接或右连接select b.id, b.name, b.createdate, a.vdat date, to_char(a.vdat, 'DAY') week
from (select to_date(to_char(to_date('2009/05/03', 'yyyy/mm/dd') + rownum - 1,
'yyyy/mm/dd'),
'yyyy/mm/dd') vdat
from dual
connect by rownum <= to_date('2009/05/09', 'yyyy/mm/dd') -
to_date('2009/05/03', 'yyyy/mm/dd') + 1) a,
table b
where a.vdat = b.createdate(+)
select b.id, b.name, b.createdate, a.vdat date, to_char(a.vdat, 'DAY') week
from (select to_date('2009/05/03', 'yyyy/mm/dd') + rownum - 1 vdat
from dual
connect by rownum <= to_date('2009/05/09', 'yyyy/mm/dd') -
to_date('2009/05/03', 'yyyy/mm/dd') + 1) a,
table b
where a.vdat = b.createdate(+)
CreateDate的日期格式是2009-5-5 11:15:59
a.vdat = b.createdate(+)to_date(a.vdat, 'yyyy-mm-dd') = to_date(b.createdate, 'yyyy-mm-dd') (+)
出错
from (select to_date('2009-05-03', 'yyyy-mm-dd') + rownum - 1 vdat
from dual
connect by rownum <= to_date('2009-05-09', 'yyyy-mm-dd') -
to_date('2009-05-03', 'yyyy-mm-dd') + 1) a,
table b
where a.vdat = b.createdate(+)
a.vdat = b.createdate(+) to_date(a.vdat, 'yyyy-mm-dd') = to_date(b.createdate, 'yyyy-mm-dd') (+)
出错
出错
from (select to_date('2009/05/03', 'yyyy/mm/dd') + rownum - 1 vdat
from dual
connect by rownum <= to_date('2009/05/09', 'yyyy/mm/dd') -
to_date('2009/05/03', 'yyyy/mm/dd') + 1) a left join
table b on trunc(a.vdat) = trunc(b.createdate);