今天,写了以下SQL语句:
select (select well_name from well where well_id = wa.well_id) well_name,sum(prod_time)/24
prod_days
FROM pro_well_vol_daily daily, well_attribute wa
WHERE daily.well_id = wa.well_id
报错:ORA-00937:不是单组分组函数
加上GROUP BY之后,是这样的:
select (select well_name from well where well_id = wa.well_id) well_name,sum(prod_time)/24
prod_days
FROM pro_well_vol_daily daily, well_attribute wa
WHERE daily.well_id = wa.well_id
group by (select well_name from well where well_id = wa.well_id)
但是,报错:ORA-22818:这里不允许出现子查询表达式。
在群里问了一下,那个高手说可以吧well那个表放在外面进行查询。
于是,我试了一下:
SELECT w.well_name ,sum(prod_time)/24 prod_days
FROM pro_well_vol_daily daily, well_attribute wa, well w
WHERE daily.well_id = wa.well_id
AND well_id = wa.well_id
group by w.well_name;
可以~\(≧▽≦)/~啦啦啦。
还有一种方式:
当查询语句中有MAX,AVG等聚合函数,SELECT 后面出现的字段或子查询必须出现在GROUP BY后面(group
by后面不能跟子查询)。
但若不想按这个字段排序,又想让这个字段出现在查询语句中,可以在这些字段或子查询前加
MAX,MIN,DECODE等函数。
select MAX( (select well_name from well where well_id = wa.well_id) )well_name,sum(prod_time)/24
prod_days
FROM pro_well_vol_daily daily, well_attribute wa
WHERE daily.well_id = wa.well_id