比如有一个表：
- SQL code
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是该小组当月未完成总金额，没有数据即为空，不知道我有没有说明白，各位大侠拜托啦！
------解决方案--------------------
- SQL code
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)) money2from testgroup 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 02 小组1 2012-03 0 0 1 3003 小组2 2012-02 1 80 0 04 小组2 2012-03 1 30 0 0