음.. 저도 sql은 초보라.. 어거지로라도 짜보겟습니다;

SELECT 학번, COUNT(S)

FROM 학적변동

WHERE CONCAT(년도,학기) IN (20142, 20151, 20152)

AND 학번 = 1

AND 변동학적 = '휴학'

GROUP BY 학번;

이렇게하면 2014년도 2학기부터 2015년도 2학기까지 복학횟수가 나오지 안을까 싶내요

with t as
(
select '2013' yyyy, '1' term, '1' id, '휴학' gb from dual union all
select '2013' yyyy, '2' term, '1' id, '휴학' gb from dual union all
select '2014' yyyy, '1' term, '1' id, '복학' gb from dual union all
select '2014' yyyy, '2' term, '1' id, '휴학' gb from dual union all
select '2015' yyyy, '1' term, '1' id, '휴학' gb from dual union all
select '2015' yyyy, '2' term, '1' id, '휴학' gb from dual union all
select '2016' yyyy, '1' term, '1' id, '복학' gb from dual union all
select '2016' yyyy, '2' term, '1' id, '휴학' gb from dual
)
select cnt cnt
  from
    (
    select max(yyyy) yyyy, count(1) + 1 cnt
    from
        (
        select yyyy,rnum,row_number() over(order by rnum) rnum2
          from
            (
            select yyyy, term, id, gb
                 , lag(gb) over(order by yyyy, term) befgb
                 , lag(yyyy) over(order by yyyy, term) befyyyy
                 , lag(term) over(order by yyyy, term) befterm
                 , row_number() over(order by yyyy, term) rnum
              from t
             where id = :id
            )
         where gb = befgb
           and gb = '휴학'
           and (yyyy = befyyyy or (term != befterm and yyyy - 1 = befyyyy)) 
        )
    group by rnum - rnum2
    having count(1) + 1 > 1
    order by yyyy desc
    )
  where rownum = 1

뭔가 복잡하네요. 간단하게 할 수있을 것 같은데..

WITH t AS
(
SELECT 1 no, '2013' yyyy, 1 seq, '휴학' gb FROM dual
UNION ALL SELECT 1, '2013', 2, '휴학' FROM dual
UNION ALL SELECT 1, '2014', 1, '복학' FROM dual
UNION ALL SELECT 1, '2014', 2, '휴학' FROM dual
UNION ALL SELECT 1, '2015', 1, '휴학' FROM dual
UNION ALL SELECT 1, '2015', 2, '휴학' FROM dual
UNION ALL SELECT 1, '2016', 1, '복학' FROM dual
UNION ALL SELECT 1, '2016', 2, '휴학' FROM dual
)
SELECT no
     , cnt
  FROM (SELECT no
             , COUNT(*) cnt
             , ROW_NUMBER() OVER(PARTITION BY no ORDER BY MIN(yyyy || seq) DESC) rn
          FROM (SELECT no, yyyy, seq, gb
                     , ROW_NUMBER() OVER(PARTITION BY no     ORDER BY yyyy, seq) rn1
                     , ROW_NUMBER() OVER(PARTITION BY no, gb ORDER BY yyyy, seq) rn2
                  FROM t
                )
         WHERE gb = '휴학'
         GROUP BY no, rn1-rn2
        HAVING COUNT(*) > 1
        )
 WHERE rn = 1
;

답변주신 서승표, jkson , 마농님 정말감사합니다. 

SELECT no
     , LENGTH(
       REGEXP_SUBSTR(
       LISTAGG(gb) WITHIN GROUP(ORDER BY yyyy DESC, seq DESC)
       , '(휴학){2,}')
       ) / 2 cnt
  FROM t
 GROUP BY no
;