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
             );

I want to select sem_code as startsem_code and sem_code as lastsem_code

How can I fix this error?

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

DATA ATTACHED HERE YOU CAN DOWNLOAD FOR YOU CONVENIENCE

回答1:

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

             );

回答2:

Because the subqueries have an explicit requirement that they match min(startsem) or max(startsem), I believe that the subqueries will return only one value. However, they may return multiple instances of the same value.

To protect against that, I think you want to add distinct to both subqueries, like this:

            (select distinct 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)

            ) as endsemester

回答3:

Problem is in one (or both) SELECT statements that return startsemester and endsemester values. For example:

(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

It must not return more than a single value. As we don't have your data, we can't answer why you got too_many_rows. There are a few ways out, e.g.

• use one of aggregates, such as MAX, e.g. select max(sem_code) from ...
• see whether distinct helps, e.g. select distinct sem_code from ...
• include rownum into the where clause, e.g. ... and rownum = 1

but - from my point of view - you should research what causes the error and fix it appropriately. Perhaps you missed some more conditions in the where clause; who knows? We don't, you might.

回答4:

     with tt AS
    ( select    f.stdcode,
             f.name,
             f.degree_code,
             f.phone,
             MIN(f.startsem)as startsemdate,
             MAX(f.startsem)as endsemedate
    FROM V_GRADUATED f
    GROUP BY 
      f.stdcode,
      f.name,
      f.degree_code,
      f.phone)
    select    tt.stdcode,
      tt.name,
      tt.degree_code,
      tt.phone,
      (select sem_code 
             from v_graduated a
            where a.stdcode=tt.stdcode 
             and a.startsem=tt.startsemdate) startsemester,
      (select sem_code
             from v_graduated a
             where a.stdcode=tt.stdcode 
            and a.startsem=tt.endsemedate) endsemester
      from tt

this is what i was want .......> THIS IS CORRECT ANSWER

enter image description here