아래의 쿼리를 한 번으로 줄일 수 있는 방법이 없을까요? 0 4 1,319

by sjydevil [SQL Query] [2013.07.04 15:17:37]


아래의 쿼리를 한 번으로 줄일 수 있는 방법이 없을까요?


---------------------------------------------------------------------

    select count(*) AS CNT_CHULSUK -- 출석 인원 
  INTO CNT_CHULSUK 
  from AP_CURRENT_MGR CM INNER JOIN AP_ATTENDANCE AT  
    ON CM.Session_Cd    = AT.Session_Cd
   AND CM.Currents_CD   = AT.Currents_CD
   AND CM.Currents_Kind = AT.Currents_Kind
    where ATTEND_KIND = '1' 
    ;


    select count(*) AS CNT_GYEOLSUK -- 결석 인원
  INTO CNT_GYEOLSUK 
  from AP_CURRENT_MGR CM INNER JOIN AP_ATTENDANCE AT  
    ON CM.Session_Cd    = AT.Session_Cd
   AND CM.Currents_CD   = AT.Currents_CD
   AND CM.Currents_Kind = AT.Currents_Kind
    where ATTEND_KIND = '0' 
    ;
  

    select count(*) AS CNT_CHUNGGA -- 청가 인원
  INTO CNT_CHUNGGA 
  from AP_CURRENT_MGR CM INNER JOIN AP_ATTENDANCE AT  
   ON  CM.Session_Cd    = AT.Session_Cd
   AND CM.Currents_CD   = AT.Currents_CD
   AND CM.Currents_Kind = AT.Currents_Kind
    where ATTEND_KIND = '2'
    ;


    select count(*) AS CNT_CHULJANG -- 출장 인원
  INTO CNT_CHULJANG
  from AP_CURRENT_MGR CM INNER JOIN AP_ATTENDANCE AT  
    ON CM.Session_Cd    = AT.Session_Cd
   AND CM.Currents_CD   = AT.Currents_CD
   AND CM.Currents_Kind = AT.Currents_Kind
    where ATTEND_KIND = '3'
    ;


    select count(*) AS CNT_GYEOLSUK2 -- 결석계 인원
  INTO CNT_GYEOLSUK2
  from AP_CURRENT_MGR CM INNER JOIN AP_ATTENDANCE AT  
    ON CM.Session_Cd    = AT.Session_Cd
   AND CM.Currents_CD   = AT.Currents_CD
   AND CM.Currents_Kind = AT.Currents_Kind
    where ATTEND_KIND = '4'
    ;

by 용근님 [2013.07.04 15:21:10]

where 절 삭제

   count ( decode ( ATTEND_KIND, '0', 1 ) ) "결석 인원"
,  count ( decode ( ATTEND_KIND, '1', 1 ) ) "출석 인원"
.....

by 우리집아찌 [2013.07.04 15:22:44]

 COUNT(DECODE(ATTEND_KIND,'1',1)) AS  CNT_CHULSUK
,COUNT(DECODE(ATTEND_KIND,'0',1)) AS  CNT_GYEOLSUK
,COUNT(DECODE(ATTEND_KIND,'2',1)) AS   CNT_CHUNGGA
,COUNT(DECODE(ATTEND_KIND,'3',1)) AS  CNT_CHULJANG
,COUNT(DECODE(ATTEND_KIND,'4',1)) AS  CNT_GYEOLSUK2

by 아발란체 [2013.07.04 15:53:59]

SELECT F1, F2 INTO V1, V2 FROM (
  SELECT 1 AS F1, COUNT(*) AS F2 FROM TABLE_A
  UNION ALL
  SELECT 2, COUNT(*) FROM TABLE_B
  UNION ALL
  SELECT 3, COUNT(*) FROM TABLE_C
  UNION ALL
  ...
)


LOOP...

IF V1 = 1 ....--CNT_CHULSUK 
ELSEIF V1 = 2 ..... --CNT_GYEOLSUK
    ...
END LOOP;


 


by 아발란체 [2013.07.04 15:59:04]
아.. 테이블명이 다 똑같군요.. ㅋ
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입