如题:
SELECT TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'W'),
COUNT(*)
FROM T_LIST
WHERE to_char(to_date(ACCEPT_TIME,'yyyy-mm-dd hh24:mi:ss'),'MM')=to_char(SYSDATE-1,'MM')
GROUP BY TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'W')
ORDER BY TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'W') ;
一个月的周报表 当某一周没数据时 怎么把那一周显示出来 结果显示为0;
T_list 是一个操作流水表 ACCEPT_TIME是操作时间。
SELECT TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'W'),
COUNT(*)
FROM T_LIST
WHERE to_char(to_date(ACCEPT_TIME,'yyyy-mm-dd hh24:mi:ss'),'MM')=to_char(SYSDATE-1,'MM')
GROUP BY TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'W')
ORDER BY TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'W') ;
一个月的周报表 当某一周没数据时 怎么把那一周显示出来 结果显示为0;
T_list 是一个操作流水表 ACCEPT_TIME是操作时间。
COUNT(*)
FROM T_LIST
WHERE to_char(to_date(ACCEPT_TIME,'yyyy-mm-dd hh24:mi:ss'),'MM')=to_char(SYSDATE-1,'MM')
GROUP null(TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'W'),0)
ORDER BY null(TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'W'),0) ;
--笔误,应该是nvl()函数
SELECT nvl(TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'W'),0),
COUNT(*)
FROM T_LIST
WHERE to_char(to_date(ACCEPT_TIME,'yyyy-mm-dd hh24:mi:ss'),'MM')=to_char(SYSDATE-1,'MM')
GROUP nvl(TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'W'),0)
ORDER BY nvl(TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'W'),0) ;
如果有記錄但accept_time為null,用nvl就行了
tmp.yyyymm,count(hire_date)
from employees e,
(select to_char(to_date('19980101','yyyymmdd') + (interval '1' month * (level - 1)), 'yyyymm') yyyymm
from dual
connect by level <= 12) tmp
where to_char(e.hire_date(+),'yyyy') = 1998
and to_char(E.HIRE_DATE(+),'yyyymm') = TMP.yyyymm
group by to_char(hire_date,'mm'), tmp.yyyymm
order by 1YYYYMM COUNT(HIRE_DATE)
--------- ----------------
199801 2
199802 3
199803 5
199804 3
199805 1
199806 1
199807 3
199808 1
199809 1
199810 0
199811 2
199812 1
create table t_list (accept_time varchar2(40)); -- 就單獨測試一個accept_time字段insert into t_list values ('2010-05-01 12:34:56');
insert into t_list values ('2010-05-02 12:34:56');
insert into t_list values ('2010-05-03 12:34:56');
insert into t_list values ('2010-05-04 12:34:56');
insert into t_list values ('2010-05-07 12:34:56');
insert into t_list values ('2010-05-09 12:34:56');
insert into t_list values ('2010-05-11 12:34:56');
insert into t_list values ('2010-05-11 22:34:56');
insert into t_list values ('2010-05-12 12:34:56');
insert into t_list values ('2010-05-13 12:34:56');
insert into t_list values ('2010-05-14 12:34:56');
insert into t_list values ('2010-05-24 12:34:56');
insert into t_list values ('2010-05-25 12:34:56');
insert into t_list values ('2010-05-26 12:34:56');
insert into t_list values ('2010-05-27 12:34:56');
insert into t_list values ('2010-05-29 12:34:56');
insert into t_list values ('2010-05-30 12:34:56');
insert into t_list values ('2010-05-31 12:34:56');
commit;-- 樓主原SQL
SELECT TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'W'),
COUNT(*)
FROM T_LIST
WHERE to_char(to_date(ACCEPT_TIME,'yyyy-mm-dd hh24:mi:ss'),'MM')=to_char(SYSDATE-1,'MM')
GROUP BY TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'W')
ORDER BY TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'W') ;-- 結果
TO_CHAR(TO_DATE(ACCEPT_TIME,'YYYY-MM-DDHH24:MI:SS'),'W') COUNT(*)
-------------------------------------------------------- ----------
1 5
2 6
4 4
5 3-- 自己加一個臨時的子查詢,每個月有4到5周,二月只有4周
select TMP.nw, sum(nvl(nc,0)) from (
SELECT TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'W') nw,
COUNT(*) nc
FROM T_LIST
WHERE to_char(to_date(ACCEPT_TIME,'yyyy-mm-dd hh24:mi:ss'),'MM')=to_char(SYSDATE-1,'MM')
GROUP BY TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'W')
ORDER BY TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'W')) tl,
(select level nw from dual connect by level <= decode(to_char(sysdate - 1, 'mm'), '02', 4, 5)) tmp
where tl.nw(+) = TMP.nw
group by tmp.nw
order by tmp.nw;-- 結果
NW SUM(NVL(NC,0))
---------- --------------
1 5
2 6
3 0
4 4
5 3
再帮我看看http://topic.csdn.net/u/20100601/14/a2b1cfe8-20e3-4a23-9dc5-ae9e0acd0a57.html这个帖子 。。