3,490
社区成员
发帖
与我相关
我的任务
分享
SELECT TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd') dt,
type,
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'), 'yyyy-mm-dd'),type
ORDER BY TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd');
SELECT TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd') dt,
type,
COUNT(*)
FROM T_LIST
WHERE to_char(to_date(ACCEPT_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')>='2010-05-11' AND to_char(to_date(ACCEPT_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')<='2010-05-19'
GROUP BY TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd'),type
ORDER BY TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd');
SELECT TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd') dt,
type,
COUNT(*)
FROM T_LIST
WHERE to_char(to_date(ACCEPT_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')>='2010-05-11' AND to_char(to_date(ACCEPT_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')<='2010-05-19'
GROUP BY TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd'),type
ORDER BY TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd');
drop table t_list;
create table t_list (id number primary key, t_date date, t_type varchar2(40), amount number);
insert into t_list values (1, to_date('2010-05-11','yyyy-mm-dd'), '17011000', 4);
insert into t_list values (2, to_date('2010-05-11','yyyy-mm-dd'), '1701100001', 1);
insert into t_list values (3, to_date('2010-05-12','yyyy-mm-dd'), '1701100001', 1);
insert into t_list values (4, to_date('2010-05-14','yyyy-mm-dd'), '17011001', 4);
insert into t_list values (5, to_date('2010-05-21','yyyy-mm-dd'), '1701100002', 1);
insert into t_list values (6, to_date('2010-05-21','yyyy-mm-dd'), '1701100002', 2);
insert into t_list values (7, to_date('2010-05-21','yyyy-mm-dd'), '1701100001', 7);
insert into t_list values (8, to_date('2010-05-21','yyyy-mm-dd'), '1701100001', 1);
commit;
select t_type
,nvl(sum(decode(to_char(t_date),'11-MAY-10',amount)),0)"11-MAY-10"
,nvl(sum(decode(to_char(t_date),'12-MAY-10',amount)),0)"12-MAY-10"
,nvl(sum(decode(to_char(t_date),'14-MAY-10',amount)),0)"14-MAY-10"
,nvl(sum(decode(to_char(t_date),'21-MAY-10',amount)),0)"21-MAY-10",sum(amount) "total"
from t_list group by t_type
---------------------------------
T_TYPE 11-MAY-10 12-MAY-10 14-MAY-10 21-MAY-10 total
-------------------- ---------- ---------- ---------- ---------- ----------
17011001 0 0 4 0 4
1701100001 1 1 0 8 10
1701100002 0 0 0 3 3
17011000 4 0 0 0 4