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字段值为油建(即非钻井,测井,物探,作业,开采这五种),那么就把它归到“其它”中我实在是解决不了,大家帮帮忙吧,不胜感激!
COUNT(*) SUM(YJJE)
---------- ----------
6 64
12 20但是上面这两行数据没有顺序,即不知道哪行是测井的哪行是钻井的,我要的分组是:
1,一定要按照“钻井,测井,物探,作业,开采,其它”这五种顺序来排列,没有j_dwmc字段值为作业的数据,也要按顺序排列出来,不过要用0来填充检索后的COUNT(*)列和SUM(YJJE)列的值
2,如果某条数据的j_dwmc字段值为油建(即非钻井,测井,物探,作业,开采这五种),那么就把它归到“其它”中我实在是解决不了,大家帮帮忙吧,不胜感激!
(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);
DECODE(A,7,0,S)改成DECODE(A,'7',0,S)
没测试数据,为NULL的排在其他的后面了
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;
group by (DECODE(j_dwmc,'钻井','1','测井','2','物探','3','作业','4','开采','5',NULL,'7','6'))
ERROR at line 4:
ORA-00907: missing right parenthesis
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 DECODE(j_dwmc,'钻井','1','测井','2','物探','3','作业','4','开采','5',NULL,'7','6')
);
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
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;