日期:2014-05-17  浏览次数:20723 次

超难的分组,大家进来看看吧
select   count(*),sum(yjje)   from   app_jhpz   where   gs_jbrq   like   '2007-01% '   group   by   dwmc;

现在数据库中符合gs_jbrq   like   '2007-01% '的数据有很多,但是j_dwmc字段只有两类的值,一类为测井,一类为钻井,所以,上面的分组查询结果如下:
    COUNT(*)     SUM(YJJE)
----------   ----------
                  6                   64
                  12                 20

但是上面这两行数据没有顺序,即不知道哪行是测井的哪行是钻井的,我要的分组是:
1,一定要按照“钻井,测井,物探,作业,开采,其它”这五种顺序来排列,没有j_dwmc字段值为作业的数据,也要按顺序排列出来,不过要用0来填充检索后的COUNT(*)列和SUM(YJJE)列的值
2,如果某条数据的j_dwmc字段值为油建(即非钻井,测井,物探,作业,开采这五种),那么就把它归到“其它”中

我实在是解决不了,大家帮帮忙吧,不胜感激!

------解决方案--------------------
SELECT DECODE(A,7,0,C),DECODE(A,7,0,S),A FROM
(select count(*) C,sum(yjje) S,
DECODE(j_dwmc, '钻井 ', '1 ', '测井 ', '2 ', '物探 ', '3 ', '作业 ', '4 ', '开采 ', '5 ',NULL, '7 ', '6 ') A
from app_jhpz where gs_jbrq like '2007-01% ' group by A);

------解决方案--------------------
select a.j_dwmc, nvl(ct,0),nvl(s,0)
from (select 1 o, '钻井 ' j_dwmc from dual
union all
select 2, '测井 ' from dual
union all
select 3, '物探 ' from dual
union all
select 4, '作业 ' from dual
union all
select 5, '开采 ' from dual) a,
union all
select 6, '其它 ' from dual) a,
(select decode(j_dwmc, '钻井 ', j_dwmc,
'测井 ', j_dwmc,
'物探 ', j_dwmc,
'作业 ', j_dwmc,
'开采 ', j_dwmc,
'其它 ') j_dwmc,
count(*) ct, sum(yjje) s
from app_jhpz
where gs_jbrq like '2007-01% '
group by decode(j_dwmc, '钻井 ', j_dwmc,
'测井 ', j_dwmc,
'物探 ', j_dwmc,
'作业 ', j_dwmc,
'开采 ', j_dwmc,
'其它 ') ) b
where a.j_dwmc=b.j_dwmc(+)
order by a.o;