>错误:ORA-00937:不是单组组函数
查询:
select count(*) todas,
sum(case when i.prioridade = 1 then 1 else 0 end) urgente,
sum(case when i.prioridade = 2 then 1 else 0 end) alta,
sum(case when i.prioridade = 3 then 1 else 0 end) normal,
sum(case when i.prioridade = 4 then 1 else 0 end) baixa,
(select count(*)
from GMITEMOS i
inner join GMCTLSLA c on c.os = i.cd_numero_os and c.item = i.item
where i.situacao in ('A', 'I', 'P')
and c.ordem = 99999
) naoAvaliados,
sum(case when i.situacao = 'P' then 1 else 0 end) pendentes,
sum(case when i.situacao = 'A' or i.situacao = 'I' then 1 else 0 end) iniciados
from GMITEMOS i
where i.situacao in ('A', 'I', 'P')
and exists (select 1
from GMCTLSLA c
where c.os = i.cd_numero_os
and c.item = i.item)
此处出现错误:
(select count(*)
from GMITEMOS i
inner join GMCTLSLA c on c.os = i.cd_numero_os and c.item = i.item
where i.situacao in ('A', 'I', 'P')
and c.ordem = 99999
) naoAvaliados
有人能说出为什么会这样吗?
已经用max
修复了它,但这不是它发生的原因,而且有点黑客。您的问题是转换为单个列的子查询不是聚合查询,min
、max
、sum
等,因此需要包含在group by
子句中。您可以通过将其包装在 max
中来解决此问题,因为单个值的最大值将始终保持不变。
但是,由于子查询本身就是一个分析查询,并且只会返回一行,因此显而易见的事情是使用笛卡尔连接将其添加到查询中。在显式连接语法中,这称为 cross join
。
select count(*) todas
, sum(case when i.prioridade = 1 then 1 else 0 end) urgente
, sum(case when i.prioridade = 2 then 1 else 0 end) alta
, sum(case when i.prioridade = 3 then 1 else 0 end) normal
, sum(case when i.prioridade = 4 then 1 else 0 end) baixa
, naoAvaliados
, sum(case when i.situacao = 'P' then 1 else 0 end) pendentes
, sum(case when i.situacao = 'A' or i.situacao = 'I' then 1 else 0 end) iniciados
from GMITEMOS i
cross join (select count(*) as naoAvaliados
from GMITEMOS j
inner join GMCTLSLA k
on k.os = j.cd_numero_os
and k.item = j.item
where j.situacao in ('A', 'I', 'P')
and k.ordem = 99999
)
where i.situacao in ('A', 'I', 'P')
and exists (select 1
from GMCTLSLA c
where c.os = i.cd_numero_os
and c.item = i.item
)
笛卡尔连接有一个不好的声誉,因为它将连接一侧的行数乘以另一侧的行数。但是,它确实有其用途,尤其是在这种情况下。
发生这种情况是因为子查询本身是一个标量结果,而不是一个组函数。正如您显然已经发现的那样,您可以通过替换一个组函数来修复它,该函数产生与您的子查询等效的结果。
在合并语句中,如果您收到此错误,则只需使用组,它将解决问题。
merge into table1 tb1
using
(select a.id,a.ac_no,sum(a.qy) as qyt,sum(a.amt) as sum_amt from
table2 a, table1 b
where a.id=b.id
and a.id = '1234'
and a.date = '08Oct2014'
and a.ac_no in (123, 234, 345)
and a.ac_no = b.ac_no
group by a.ac_no,a.id
)qry
on (qry.id=tb1.id and qry.ac_no=tb1.ac_no )
when matched then
update set qy=qry.qy,amt = qry.sum_amt;