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');
t_list 操作流水表, type 操作类型 ACCEPT_TIME操作时间
查询结果
序号 时间 类型 数量
1 2010-05-11 17011000 4
2 2010-05-11 1701100001 1
3 2010-05-12 1701100001 1要求显示的结果
类型 日期 数量
2010-05-11 2010-05-12 ....(日期不固定) 6(所有日期的汇总)
17011000 4 0 4
1701100001 1 1 2
看看WILDWARE的帖子http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html?17965
哈哈!
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
wildwave 的帖子太深奥了 看不太懂。。
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');