需要查询更正 [英] Query correction needed

问题描述

select stdcode,name,degree_code,phone,startsemester,endsemester
from  (                   
         select distinct stdcode as stdcode,name as name,degree_code as degree_code,phone as phone,
              (
                SELECT sem_code
                FROM V_ALLSTUDATA b
                WHERE  a.name = b.name 
                and a.stdcode= b.stdcode 
                and a.degree_code=b.degree_code 
                and a.phone=b.phone
                AND  startsem=(select min(startsem) 
                               from V_ALLSTUDATA b)    
             ) as startsemester,
             (
                SELECT sem_code
                FROM V_ALLSTUDATA b
                WHERE  a.name = b.name 
                and a.stdcode= b.stdcode 
                and a.degree_code=b.degree_code 
                and a.phone=b.phone
                AND  startsem=(select 
                               max(startsem) from V_ALLSTUDATA a)
              ) as endsemester

                from V_ALLSTUDATA a
             );

我想选择sem_code作为startsem_code和sem_code作为lastsem_code

如何解决此错误?

ORA-01427:单行子查询返回多个行
01427. 00000-单行子查询返回多个行"
*原因:
*动作:

ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:

附带的数据，您可以在这里方便下载

推荐答案

您在单个查询中有多个行，因此我认为您将使用rownum. 所以尝试下面的查询

you got multiple rows in the single query so I think you will be use rownum. so Try Below Query

select stdcode,name,degree_code,phone,startsemester,endsemester
        from  (                   
                select distinct stdcode as stdcode,name as name,degree_code as degree_code,phone as phone,
              (
                SELECT sem_code
                      FROM V_ALLSTUDATA b
                      WHERE  a.name = b.name and a.stdcode= b.stdcode and a.degree_code=b.degree_code 
 and a.phone=b.phone
                     AND  startsem=(select min(startsem) from V_ALLSTUDATA b where rownum=1)

                    ) as startsemester,
                    (select sem_code
                      from V_ALLSTUDATA b
                       where  a.name = b.name and a.stdcode= b.stdcode and a.degree_code=b.degree_code and a.phone=b.phone
                      and endsem=(select max(startsem) from V_ALLSTUDATA a where rownum=1)

                    ) as endsemester

                from V_ALLSTUDATA a

             );

这篇关于需要查询更正的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！