Oracle 复杂sql 语句，在线等.....

[飞过海啊lsq]

表结构如下:
CREATE TABLE [dbo].[recordInfo](
[id] [int] IDENTITY(1,1) NOT NULL primary key ,
[name] [varchar](20) NOT NULL, --姓名
[recorddate] [varchar](20) NULL, --工作时间
[workhours] [numeric](10, 2) NULL) --工作小时

数据如下：
id name recorddate workhours
1 sa 2012-06-10 8.00
2 sa 2012-06-11 8.00
3 root 2012-06-11 9.00
4 root 2012-06-14 7.00
5 admin 2012-06-15 6.00
6 sa 2012-06-10 3.00

现在需要查询2012-06-10至2012-06-15用户的数据

查询每个人每天的总共工作小时数据，没有默认workhours为0

需要的结果

admin 2012-06-10 0.00
admin 2012-06-11 0.00
admin 2012-06-12 0.00
admin 2012-06-13 0.00
admin 2012-06-14 0.00
admin 2012-06-15 6.00
root 2012-06-10 0.00
root 2012-06-11 9.00
root 2012-06-12 0.00
root 2012-06-13 0.00
root 2012-06-14 7.00
root 2012-06-15 0.00
sa 2012-06-10 11.00
sa 2012-06-11 8.00
sa 2012-06-12 0.00
sa 2012-06-13 0.00
sa 2012-06-14 0.00
sa 2012-06-15 0.00

[renfengjun]

--想不出什么好方法了，输入起始时间和结束时间
with recordInfo(id,name,recorddate,workhours) as 
(
select 1,'sa',to_date(20120610,'yyyymmdd'),8.00 from dual
union
select 2,'sa',to_date(20120611,'yyyymmdd'),8.00 from dual
union
select 3,'root',to_date(20120611,'yyyymmdd'),9.00 from dual
union
select 4,'root',to_date(20120614,'yyyymmdd'),7.00 from dual
union
select 5,'admin',to_date(20120615,'yyyymmdd'),6.00 from dual
union
select 6,'sa',to_date(20120610,'yyyymmdd'),3.00 from dual
)
select  s.name ,s.dat , nvl(sum(rec2.workhours),0)
  from 
       (select distinct (rec.name) as name, t.dat
          from recordInfo rec,
               (select to_date(replace('&&begin_dat', '-'), 'yyyymmdd') +
                       level - 1 as dat
                  from dual
                connect by level <=
                           (to_date(replace('&&end_dat', '-'), 'yyyymmdd') -
                           to_date(replace('&&begin_dat', '-'), 'yyyymmdd')) + 1
                ) t) s left join recordInfo rec2 on (s.name = rec2.name and trunc(s.dat)  = trunc(rec2.recorddate))
            group by s.name , s.dat
            order by s.name , s.dat 
/


sys@ORCL> /
Enter value for begin_dat: 20120610
old  19:                (select to_date(replace('&&begin_dat', '-'), 'yyyymmdd') +
new  19:                (select to_date(replace('20120610', '-'), 'yyyymmdd') +
Enter value for end_dat: 20120615
old  23:                            (to_date(replace('&&end_dat', '-'), 'yyyymmdd') -
new  23:                            (to_date(replace('20120615', '-'), 'yyyymmdd') -
old  24:                            to_date(replace('&&begin_dat', '-'), 'yyyymmdd')) + 1
new  24:                            to_date(replace('20120610', '-'), 'yyyymmdd')) + 1


NAME  DAT        NVL(SUM(REC2.WORKHOURS),0)
----- ---------- --------------------------
admin 2012-06-10                          0
admin 2012-06-11                          0
admin 2012-06-12                          0
admin 2012-06-13                          0
admin 2012-06-14                          0
admin 2012-06-15                          6
root  2012-06-10                          0
root  2012-06-11                          9
root  2012-06-12                          0
root  2012-06-13                          0
root  2012-06-14                          7
root  2012-06-15                          0
sa    2012-06-10                         11
sa    2012-06-11                          8
sa    2012-06-12                          0
sa    2012-06-13                          0
sa    2012-06-14                          0
sa    2012-06-15                          0

[大明湖畔_帅锅]

构造一张日期表 关键这个表

[大明湖畔_帅锅]

貌似是写复杂了点 还没怎么想 应该还有简单的吧 大概就这思路了

select t3.name,t3.r_date,to_char(nvl(sum(t4.workhours),0),'FM99990.00') workhours
from
(select distinct name,r_date
from (select n_date+level-1 r_date
 from (select max(recorddate) m_date,min(recorddate) n_date from recordInfo)
 connect by level <= m_date-n_date+1) t2,recordInfo t1
) t3 left join recordInfo t4 on t3.name = t4.name and t3.r_date = t4.recorddate
group by t3.name,t3.r_date
order by t3.name,t3.r_date

      name     r_date     workhours
-----------------------------------------
1	admin	2012/6/10	0.00
2	admin	2012/6/11	0.00
3	admin	2012/6/12	0.00
4	admin	2012/6/13	0.00
5	admin	2012/6/14	0.00
6	admin	2012/6/15	6.00
7	root	2012/6/10	0.00
8	root	2012/6/11	9.00
9	root	2012/6/12	0.00
10	root	2012/6/13	0.00
11	root	2012/6/14	7.00
12	root	2012/6/15	0.00
13	sa	2012/6/10	11.00
14	sa	2012/6/11	8.00
15	sa	2012/6/12	0.00
16	sa	2012/6/13	0.00
17	sa	2012/6/14	0.00
18	sa	2012/6/15	0.00