日期:2014-05-16  浏览次数:20871 次

数据库表按一字段状态分列汇总问题
比如有一个表:
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)) 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