表结构如下:
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
------解决方案--------------------
- SQL code
--想不出什么好方法了，输入起始时间和结束时间with recordInfo(id,name,recorddate,workhours) as (select 1,'sa',to_date(20120610,'yyyymmdd'),8.00 from dualunionselect 2,'sa',to_date(20120611,'yyyymmdd'),8.00 from dualunionselect 3,'root',to_date(20120611,'yyyymmdd'),9.00 from dualunionselect 4,'root',to_date(20120614,'yyyymmdd'),7.00 from dualunionselect 5,'admin',to_date(20120615,'yyyymmdd'),6.00 from dualunionselect 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: 20120610old 19: (select to_date(replace('&&begin_dat', '-'), 'yyyymmdd') +new 19: (select to_date(replace('20120610', '-'), 'yyyymmdd') +Enter value for end_dat: 20120615old 23: (to_date(replace('&&end_dat', '-'), 'yyyymmdd') -new 23: (to_date(replace('20120615', '-'), 'yyyymmdd') -old 24: to_date(replace('&&begin_dat', '-'), 'yyyymmdd')) + 1new 24: to_date(replace('20120610', '-'), 'yyyymmdd')) + 1NAME DAT NVL(SUM(REC2.WORKHOURS),0)----- ---------- --------------------------admin 2012-06-10 0admin 2012-06-11 0admin 2012-06-12 0admin 2012-06-13 0admin 2012-06-14 0admin 2012-06-15 6root 2012-06-10 0root 2012-06-11 9root 2012-06-12 0root 2012-06-13 0root 2012-06-14 7root 2012-06-15 0sa 2012-06-10 11sa 2012-06-11 8sa 2012-06-12 0sa 2012-06-13 0sa 2012-06-14 0sa 2012-06-15 0
------解决方案--------------------
貌似是写复杂了点 还没怎么想 应该还有简单的吧 大概就这思路了
- SQL code
select t3.name,t3.r_date,to_char(nvl(sum(t4.workhours),0),'FM99990.00') workhoursfrom(select distinct name,r_datefrom (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.recorddategroup by t3.name,t3.r_dateorder by t3.name,t3.r_date name r_date workhours-----------------------------------------1 admin 2012/6/10 0.002 admin 2012/6/11 0.003 admin 2012/6/12 0.004 admin 2012/6/13 0.005 admin 2012/6/14 0.006 admin 2012/6/15 6.007 root 2012/6/10 0.008 root 2012/6/11 9.009 root 2012/6/12 0.0010 root 2012/6/13 0.0011 root 2012/6/14 7.0012 root 2012/6/15 0.0013 sa 2012/6/10 11.0014 sa 2012/6/11 8.0015 sa 2012/6/12 0.0016 sa 2012/6/13 0.0017 sa 2012/6/14 0.0018 sa 2012/6/15 0.00