建议,新增加一张表tab_date,用来控制那些日期需要统计考勤 ex:create table tab_date(id varchar(20),cdate date) insert into tab_date values ('100','2006-5-1'); insert into tab_date values ('100','2006-5-2'); insert into tab_date values ('100','2006-5-3'); ... insert into tab_date values ('100','2006-5-31'); ... commit ;然后连表查询得到结果集: select a.cdate ,nvl(b.scount,0) from tab_date a, (select count(*) as scount, kaoqin_date from tab_kaoqin group by kaoqin_date)b where a.cdate = b.kaoqin_date(+)
应该就不行 我一般的做法是加一个临时表 当然,也可以这样,但是灵活性就不好了SELECT AA.日期, NVL(CC.当日考勤人数, 0) AS 当日考勤人数 FROM (SELECT '2006-05-18' AS 日期 FROM DUAL UNION SELECT '2006-05-19' FROM DUAL UNION SELECT '2006-05-20' FROM DUAL UNION SELECT '2006-05-21' FROM DUAL UNION SELECT '2006-05-22' FROM DUAL UNION SELECT '2006-05-23' FROM DUAL) AA LEFT JOIN (select count(*) as 当日考勤人数,to_char(kaoqin_date,'yyyy-mm-dd') as 日期 from tab_kaoqin group by to_char(kaoqin_date,'yyyy-mm-dd')) CC ON AA.日期 = CC.日期
SELECT COUNT(a.rq) AS 当日考勤人数, to_char(rq1, 'yyyy-mm-dd') AS 日期 FROM tab_kaoqin a, (SELECT (DATE '2005-5-1' + rownum - 1) rq1 FROM dual CONNECT BY LEVEL <= 31) b WHERE a.rq(+) = b.rq1 GROUP BY b.rq1
ex:create table tab_date(id varchar(20),cdate date)
insert into tab_date values ('100','2006-5-1');
insert into tab_date values ('100','2006-5-2');
insert into tab_date values ('100','2006-5-3');
...
insert into tab_date values ('100','2006-5-31');
...
commit ;然后连表查询得到结果集:
select a.cdate ,nvl(b.scount,0) from tab_date a,
(select count(*) as scount, kaoqin_date from tab_kaoqin group by kaoqin_date)b
where a.cdate = b.kaoqin_date(+)
我一般的做法是加一个临时表
当然,也可以这样,但是灵活性就不好了SELECT AA.日期, NVL(CC.当日考勤人数, 0) AS 当日考勤人数 FROM
(SELECT '2006-05-18' AS 日期 FROM DUAL
UNION
SELECT '2006-05-19' FROM DUAL
UNION
SELECT '2006-05-20' FROM DUAL
UNION
SELECT '2006-05-21' FROM DUAL
UNION
SELECT '2006-05-22' FROM DUAL
UNION
SELECT '2006-05-23' FROM DUAL) AA
LEFT JOIN
(select count(*) as 当日考勤人数,to_char(kaoqin_date,'yyyy-mm-dd') as 日期 from tab_kaoqin group by to_char(kaoqin_date,'yyyy-mm-dd')) CC
ON AA.日期 = CC.日期
FROM tab_kaoqin a, (SELECT (DATE '2005-5-1' + rownum - 1) rq1 FROM dual CONNECT BY LEVEL <= 31) b
WHERE a.rq(+) = b.rq1
GROUP BY b.rq1
duanzilin(寻),给了好的思路,谢谢~