select distinct(daima),decode(daima,'1','上海青','2','白萝卜','3','小白菜','4','西红柿','5','土豆','6','黄瓜','7','薄皮青椒','8','茄子','9','四季豆','10','芹菜','11','包菜','12','洋葱','13','冬瓜','14','花菜','15','大白菜','16','生菜','17','胡萝卜','18','藕','') as cm,
( select a.daima as 代码, avg(case when i.so_instance_code = 'pjcd01' then field1 end) 价格
from dyna_dataset_nfcp_range1 a, rpt7_reportinstance i, Rpt7_ReportPeriod d where r.reportinstance_guid = i.reportinstance_guid
and i.template_guid = 'AC100142FFFFFFFF8E6F8CAF00001075'and i.verifystatus >= 0 and substr(d.reportperiod_code, 5, 7) = '2012-01' and a.daima=f.daima )as field1
from dyna_dataset_nfcp_range1 f group by to_number(daima)
( select a.daima as 代码, avg(case when i.so_instance_code = 'pjcd01' then field1 end) 价格
from dyna_dataset_nfcp_range1 a, rpt7_reportinstance i, Rpt7_ReportPeriod d where r.reportinstance_guid = i.reportinstance_guid
and i.template_guid = 'AC100142FFFFFFFF8E6F8CAF00001075'and i.verifystatus >= 0 and substr(d.reportperiod_code, 5, 7) = '2012-01' and a.daima=f.daima )as field1
from dyna_dataset_nfcp_range1 f group by to_number(daima)
就是这个
( select a.daima as 代码, avg(case when i.so_instance_code = 'pjcd01' then field1 end) 价格
from dyna_dataset_nfcp_range1 a, rpt7_reportinstance i, Rpt7_ReportPeriod d where r.reportinstance_guid = i.reportinstance_guid
and i.template_guid = 'AC100142FFFFFFFF8E6F8CAF00001075'and i.verifystatus >= 0 and substr(d.reportperiod_code, 5, 7) = '2012-01' and a.daima=f.daima )as field1
distinct(daima)==>distinct(to_number(daima))
不能去掉,执行后就是ORA-00979:不是group by 表达式
color=#FF0000]这个怎么写呢[[/color]
decode(daima,'1','上海青','2','白萝卜','3','小白菜','4','西红柿','5','土豆','6','黄瓜','7','薄皮青椒','8','茄子','9','四季豆','10','芹菜','11','包菜','12','洋葱','13','冬瓜','14','花菜','15','大白菜','16','生菜','17','胡萝卜','18','藕','') as cm,
(select a.daima as 代码
from dyna_dataset_nfcp_range1 a, rpt7_reportinstance i, Rpt7_ReportPeriod d
where r.reportinstance_guid = i.reportinstance_guid
and i.template_guid = 'AC100142FFFFFFFF8E6F8CAF00001075'and
i.verifystatus >= 0 and
substr(d.reportperiod_code, 5, 7) = '2012-01'
and a.daima=f.daima )as field1,
(select avg(case when i.so_instance_code = 'pjcd01' then field1 end) 价格
from dyna_dataset_nfcp_range1 a, rpt7_reportinstance i, Rpt7_ReportPeriod d
where r.reportinstance_guid = i.reportinstance_guid
and i.template_guid = 'AC100142FFFFFFFF8E6F8CAF00001075'
and i.verifystatus >= 0
and substr(d.reportperiod_code, 5, 7) = '2012-01'
and a.daima=f.daima )as field2
from dyna_dataset_nfcp_range1 f
group by daima