为什么我在SQL查询中获取ORA-00907

Question

我有一个SQL查询，合作伙伴为大学的一个小项目所做的(这是我们第一次使用SQL(，但是我们遇到了ora-00907错误，我们两个人都不知道T知道为什么。

我已经检查了括号，他们似乎还可以，所以问题一定是另一个。

select
    persona.nombre,
    anyo,
    t2.total
from persona join
(
    select
        t1.idPersona,
        count(produccion.anyo) as total,
        anyo 
        from
        (
            select *
            from produccion
            join pelicula
                on produccion.id = pelicula.id
        ) as pel
        join
        (
            select *
            from participa
            where idPapel = 8
        ) as t1
           on t1.idProduccion = pel.id
    )
    group by t1.idPersona
) as t2
    on persona.id = t2.idPersona
where t2.total > 2
order by t2.total desc;

Answer 1

您正在选择 *并在一个列中进行组进行组，该列正在创建问题。您要么仅根据条件选择各个列，要么您删除组。

select *
               from (produccion join pelicula on produccion.id=pelicula.id) as pel
               join
               (select *
                from participa
                where idPapel=8) as t1
               on t1.idProduccion=pel.id)
         group by t1.idPersona

上面的代码部分不允许使用组。

如果非常需要组，我建议您稍后再使用它。另一个选项是使用分析功能，并在您已经拥有的查询的上嵌套中过滤掉休息的未杀伤记录。

Answer 2

您有很多嵌套的视图，这使您的查询很难调试。您有很多括号，需要匹配。

无论如何这是错误的： select t1.idPersona, count(produccion.anyo) as total, anyo。您需要将anyo包含在组中的子句中，这可能会更改您想要的结果集。

select persona.nombre, 
       t2.anyo,
       t2.total
from persona join
        (select t1.idPersona, 
                count(produccion.anyo) as total, 
                anyo 
         from (select *
               from produccion 
                    join pelicula 
                    on produccion.id=pelicula.id) pel
               join
               (select *
                from participa
                where idPapel=8) t1
               on t1.idProduccion=pel.id
         group by t1.idPersona, t1.anyo) t2
     on persona.id=t2.idPersona
where t2.total>2
order by t2.total desc;

Answer 3

我认为您的查询可以像这样简化/更正：

select  persona.nombre,
        anyo,
        t2.total
from    persona 
join    (
           select     par.idPersona,
                      count(produccion.anyo) as total,
                      anyo 
           from       produccion
           join       pelicula
                  on  produccion.id = pelicula.id        
           left join  participa par          
                  on  par.idProduccion = pelicula.id -- or produccion.id, 
                                                     -- this was also an error in the original query,
                                                     -- since the subquery selected both
                  and par.idPapel = 8    
           group by   t1.idPersona
           ,          anyo    -- Was missing, but it also doesn't make sense, as this is what you count, so you'll just get 1's here. What do you want with this?
       ) as t2
    on persona.id = t2.idPersona
where  t2.total > 2
order by t2.total desc;