比如有一个表:
create table TEST
(
ID VARCHAR2(5) ,
MONEY NUMBER,
STATE VARCHAR2(8),
RIQI VARCHAR2(10),
XIAOZU VARCHAR2(10)
)insert into test (ID, MONEY, STATE, RIQI, XIAOZU) values ('1', 100, '已完成', '2012-02-10', '小组1');
insert into test (ID, MONEY, STATE, RIQI, XIAOZU) values ('2', 300, '未完成', '2012-03-1', '小组1');
insert into test (ID, MONEY, STATE, RIQI, XIAOZU) values ('3', 150, '已完成', '2012-02-15', '小组1');
insert into test (ID, MONEY, STATE, RIQI, XIAOZU) values ('4', 80, '已完成', '2012-02-23', '小组2');
insert into test (ID, MONEY, STATE, RIQI, XIAOZU) values ('5', 30, '已完成', '2012-03-23', '小组2');需求按所属小组(XIAOZU)、月份(to_char(substr(RIQI, 1, 7)))和完成状态(STATE)汇总查询出如下格式的数据,该怎么做?
xiaozu month count1 money1 count2 money2
小组1 2012-02 2 250
小组1 2012-03 1 300
小组2 2012-02 1 80
小组2 2012-03 1 30 其中count1是该小组当月已完成总条数、money1是该小组当月已完成总金额、count2是该小组当月未完成总条数、money2是该小组当月未完成总金额,没有数据即为空,不知道我有没有说明白,各位大侠拜托啦!
create table TEST
(
ID VARCHAR2(5) ,
MONEY NUMBER,
STATE VARCHAR2(8),
RIQI VARCHAR2(10),
XIAOZU VARCHAR2(10)
)insert into test (ID, MONEY, STATE, RIQI, XIAOZU) values ('1', 100, '已完成', '2012-02-10', '小组1');
insert into test (ID, MONEY, STATE, RIQI, XIAOZU) values ('2', 300, '未完成', '2012-03-1', '小组1');
insert into test (ID, MONEY, STATE, RIQI, XIAOZU) values ('3', 150, '已完成', '2012-02-15', '小组1');
insert into test (ID, MONEY, STATE, RIQI, XIAOZU) values ('4', 80, '已完成', '2012-02-23', '小组2');
insert into test (ID, MONEY, STATE, RIQI, XIAOZU) values ('5', 30, '已完成', '2012-03-23', '小组2');需求按所属小组(XIAOZU)、月份(to_char(substr(RIQI, 1, 7)))和完成状态(STATE)汇总查询出如下格式的数据,该怎么做?
xiaozu month count1 money1 count2 money2
小组1 2012-02 2 250
小组1 2012-03 1 300
小组2 2012-02 1 80
小组2 2012-03 1 30 其中count1是该小组当月已完成总条数、money1是该小组当月已完成总金额、count2是该小组当月未完成总条数、money2是该小组当月未完成总金额,没有数据即为空,不知道我有没有说明白,各位大侠拜托啦!
xiaozu month count1 money1 count2 money2
小组1 2012-02 2------250
小组1 2012-03 --------------1------300
小组2 2012-02 1------80
小组2 2012-03 1------30
group by xiaozu,to_char(riqi,'yyyy-mm')
money else 0 end) mon1,
count(case when state='已完成' then
1 else 0 end) c1
, sum(case when state='未完成' then
money else 0 end ) mon2,
count(case when state='未完成' then
1 else 0 end) c2
from (select to_char(to_date(riqi,'yyyy-mm-dd'),'yyyymm') rq,id,money, state,riqi,xiaozu from test) a
group by xiaozu,rq
order by xiaozu,rq
select XIAOZU,
substr(RIQI,1,7) month ,
count(decode(STATE,'已完成',1,'')) count1,
sum(decode(STATE,'已完成',money,0)) money1,
count(decode(STATE,'未完成',1,'')) count2,
sum(decode(STATE,'未完成',money,0)) money2
from test
group by XIAOZU,substr(RIQI,1,7)
order by XIAOZU,substr(RIQI,1,7)
xiaozu month count1 money1 count2 money2
----------------------------------------------------
1 小组1 2012-02 2 250 0 0
2 小组1 2012-03 0 0 1 300
3 小组2 2012-02 1 80 0 0
4 小组2 2012-03 1 30 0 0