MSSQL 쿼리 샘플

내가만든 쿼리 (2중 쿼리)

=============================

SELECT IMSI.CBTID AS '아이디', IMSI.CBTCNT AS '회', hist.listeningScore, hist.structureScore,
     hist.readingScore, hist.score
FROM testhistory hist,
         (SELECT a.id CBTID, MAX(b.testID) CBTCNT
       FROM kor_cbt4 a LEFT JOIN
             testhistory b ON a.id = b.id
       GROUP BY a.id) IMSI
WHERE hist.id = IMSI.CBTID AND IMSI.CBTCNT = hist.testID

=============================

 
 '####### 차수별 전체수강인원  #########################################################
 SQLStmt =    " SELECT COUNT(A.ID) TOTAL_COUNT            "
 SQLStmt = SQLStmt & " FROM (                                    "
 SQLStmt = SQLStmt & "      SELECT  ID                          "
 SQLStmt = SQLStmt & "      FROM MEMBER                         "
 SQLStmt = SQLStmt & "      WHERE SEQ=" &SEQ& "                 "
 SQLStmt = SQLStmt & "      AND ID <> 'TEST'                  "
 SQLStmt = SQLStmt & "      AND COMPANY LIKE  '" & PART & "'  "
 SQLStmt = SQLStmt & "      AND GUBN LIKE  '"  & COURSE & "'  "
 SQLStmt = SQLStmt & "      AND ID LIKE  '"  & ID & "'        "
 SQLStmt = SQLStmt & "      GROUP BY ID,GUBN                    "
 SQLStmt = SQLStmt & "      )A                                   "
 
 set rs = oConn.execute(SQLStmt)
 total_count = rs("TOTAL_COUNT")
                     
                     

 '####### 출석률 ####################################################################
 SQLStmt =    " SELECT CASE WHEN ROUND(AVG(A.ATTEND_RATE),0) IS NULL THEN 0.0 ELSE ROUND(AVG(A.ATTEND_RATE),0) END ATTEND "
 SQLStmt = SQLStmt & " FROM(                                                                         "
 SQLStmt = SQLStmt & "  SELECT A.subject, (CASE WHEN B.CNT=0 THEN 0 ELSE A.ATTEND/B.CNT*100 END) ATTEND_RATE FROM "
 SQLStmt = SQLStmt & "        (                                                                     "
 SQLStmt = SQLStmt & "        SELECT SUBJECT,  AVG(A.CNT)*1.0 ATTEND                             "
 SQLStmt = SQLStmt & "        FROM (                                                                "
 SQLStmt = SQLStmt & "         SELECT A.SUBJECT,A.id,MAX(A.ATTEND) CNT FROM COURSE A, MEMBER B   "
 SQLStmt = SQLStmt & "         WHERE A.SEQ=" &SEQ& "                                             "
 SQLStmt = SQLStmt & "           AND B.COMPANY LIKE  '" & PART & "'                              "
 SQLStmt = SQLStmt & "           AND A.SUBJECT LIKE  '"  & COURSE & "'                           "
 SQLStmt = SQLStmt & "           AND A.ID LIKE  '"  & ID & "'                           "
 SQLStmt = SQLStmt & "           AND A.ID<>'TEST'                                                "
 SQLStmt = SQLStmt & "        AND A.SUBJECT = B.GUBN                                          "
 SQLStmt = SQLStmt & "               AND A.SEQ = B.SEQ                                               "
 SQLStmt = SQLStmt & "               AND A.ID  = B.ID                                                "
 SQLStmt = SQLStmt & "         GROUP BY A.SUBJECT, A.ID                                          "
 SQLStmt = SQLStmt & "        )A                                                                 "
 SQLStmt = SQLStmt & "         GROUP BY A.SUBJECT                                                   "
 SQLStmt = SQLStmt & "       )A,                                                                    "
 SQLStmt = SQLStmt & "       (                                                                      "
 SQLStmt = SQLStmt & "         SELECT COURSE, MAX(SEQ)*1.0 CNT FROM LIST                            "
 SQLStmt = SQLStmt & "         WHERE SDATE <= CONVERT(VARCHAR(8),GETDATE(),112)                     "
 SQLStmt = SQLStmt & "          AND HANWHA_SEQ=" &SEQ& "                                            "
 SQLStmt = SQLStmt & "         GROUP BY COURSE                                                      "
 SQLStmt = SQLStmt & "       )B                                                                     "
 SQLStmt = SQLStmt & "  WHERE A.SUBJECT = B.COURSE                                                  "
 SQLStmt = SQLStmt & " )A                                                                         "
 
 set rs = oConn.execute(SQLStmt)
 
 
 
 
 IF rs.BOF or rs.EOF THEN
  total_attend =0
 ELSE
  total_attend = CInt(rs("ATTEND"))
 END IF
 
 
 
 IF total_attend > 100 THEN total_attend =100 END IF
 
 
 

 '####### 진도률 ####################################################################
 SQLStmt =           " SELECT CASE WHEN ROUND(AVG(A.AVERAGE),0) IS NULL THEN 0.0 ELSE ROUND(AVG(A.AVERAGE),0) END AVERAGE                            "
 SQLStmt = SQLStmt & " FROM (                                                              "
 SQLStmt = SQLStmt & "  SELECT SUBJECT,                                                   "
 SQLStmt = SQLStmt & "         CASE                                                       "
 SQLStmt = SQLStmt & "           WHEN SUBJECT='LC' THEN AVG(A.CNT)/42.0*100               "
 SQLStmt = SQLStmt & "           WHEN SUBJECT='SURVIVAL' THEN AVG(A.CNT)/41.0*100         "
 SQLStmt = SQLStmt & "           WHEN SUBJECT='TOEICALL1' THEN AVG(A.CNT)/38.0*100        "
 SQLStmt = SQLStmt & "         END  AVERAGE                                         "
 SQLStmt = SQLStmt & "  FROM (                                                            "
 SQLStmt = SQLStmt & "        SELECT A.SUBJECT,COUNT(*) CNT FROM LECTURENET A, MEMBER B   "
 SQLStmt = SQLStmt & "        WHERE A.SEQ=" &SEQ& "                                       "
 SQLStmt = SQLStmt & "       AND B.COMPANY LIKE  '" & PART & "'                        "
 SQLStmt = SQLStmt & "       AND A.SUBJECT LIKE  '"  & COURSE & "'                     "
 SQLStmt = SQLStmt & "       AND A.ID LIKE  '"  & ID & "'                              "
 SQLStmt = SQLStmt & "          AND A.ID<>'TEST'                                          "
 SQLStmt = SQLStmt & "           AND A.SUBJECT = B.GUBN                                    "
 SQLStmt = SQLStmt & "           AND A.SEQ = B.SEQ                                         "
 SQLStmt = SQLStmt & "           AND A.ID  = B.ID                                          "
 SQLStmt = SQLStmt & "        GROUP BY A.SUBJECT, A.ID                                    "
 SQLStmt = SQLStmt & "  )A                                                                "
 SQLStmt = SQLStmt & " GROUP BY A.SUBJECT                                                  "
 SQLStmt = SQLStmt & " )A                                                                  "

 'response.write(SQLStmt)
 
 set rs = oConn.execute(SQLStmt)
 
 IF rs.BOF or rs.EOF THEN
  total_progress =0
 ELSE
  total_progress = CInt(rs("AVERAGE"))
 END IF
 
 IF total_progress > 100 THEN total_progress =100 END IF
 

 '####### 평가결과  ##################################################################
 SQLStmt =    " SELECT A.GUBN,                              "
 SQLStmt = SQLStmt & "        ROUND(AVG(A.L_SCORE*2.5),0) L_SCORE, "
 SQLStmt = SQLStmt & "        ROUND(AVG(A.R_SCORE*2.5),0) R_SCORE, "
 SQLStmt = SQLStmt & "        ROUND(AVG(A.S_SCORE*2.5),0) S_SCORE, "
 SQLStmt = SQLStmt & "        ROUND(AVG(A.T_SCORE*2.5),0) T_SCORE  "
 SQLStmt = SQLStmt & " FROM TEST A, MEMBER B                       "
 SQLStmt = SQLStmt & " WHERE A.SEQ=" &SEQ& "                       "
 SQLStmt = SQLStmt & "   AND B.COMPANY LIKE  '" & PART & "'        "
 SQLStmt = SQLStmt & "  AND A.COURSE  LIKE  '"  & COURSE & "'     "   
 SQLStmt = SQLStmt & "  AND A.ID  LIKE  '"  & ID & "'     "   
    SQLStmt = SQLStmt & "   AND A.ID <> 'TEST'                        "
 SQLStmt = SQLStmt & "   AND A.COURSE = B.GUBN                     "
 SQLStmt = SQLStmt & "   AND A.SEQ = B.SEQ                         "
 SQLStmt = SQLStmt & "   AND A.ID  = B.ID                          "
 SQLStmt = SQLStmt & " GROUP BY A.GUBN                             "
 
 
 set rs = oConn.execute(SQLStmt)
  
  
 IF rs.BOF or rs.EOF THEN
     l_score1 = 0 
  r_score1 = 0 
  s_score1 = 0 
  t_score1 = 0
  total_test1 = 0  
    ELSE
     l_score1 = CInt(rs("L_SCORE"))
     r_score1 = CInt(rs("R_SCORE"))
     s_score1 = CInt(rs("S_SCORE"))
     t_score1 = CInt(rs("T_SCORE"))
     rs.MoveNext
    END IF
   
   
    IF rs.BOF or rs.EOF THEN
     l_score2 = 0   
  r_score2 = 0   
  s_score2 = 0  
  t_score2 = 0
 ELSE
  l_score2 = CInt(rs("L_SCORE"))
     r_score2 = CInt(rs("R_SCORE"))
     s_score2 = CInt(rs("S_SCORE"))
     t_score2 = CInt(rs("T_SCORE"))
     total_test2 = 0
    END IF
 total_test1 = t_score1                                                    
 total_test2 = t_score2                                                    

 '####### 한 번 이라도 강의를 신청한 사원이 있는 회사 ####################
 SQLStmt =    " SELECT DISTINCT COMPANY FROM MEMBER   "
 SQLStmt = SQLStmt & " WHERE ID <> 'test'                    "
 set rs_company = oConn.execute(SQLStmt)
 
 
 '####### 과목목록       ################################################
 SQLStmt =    " SELECT DISTINCT GUBN,COURSE FROM MEMBER   "
 SQLStmt = SQLStmt & " WHERE ID <> 'test'                        "
 set rs_course = oConn.execute(SQLStmt)
 
 
 
 '####### 차수별 전체수강인원  #########################################################
 SQLStmt =    " SELECT ID,NAME FROM MEMBER          "
 SQLStmt = SQLStmt & " WHERE SEQ=" &SEQ& "                 "
 SQLStmt = SQLStmt & "   AND ID <> 'TEST'                  "
 SQLStmt = SQLStmt & "   AND COMPANY LIKE  '" & PART & "'  "
 SQLStmt = SQLStmt & "   AND GUBN LIKE  '"  & COURSE & "'  "
 SQLStmt = SQLStmt & " ORDER BY NAME                       "
 
 set rs_name = oConn.execute(SQLStmt)
 
 
 '####### 수강자 목록 ####################################################################
 SQLStmt =           " SELECT                                                                   "
 SQLStmt = SQLStmt & "       B.COMPANY COMPANY,                                                 "
 SQLStmt = SQLStmt & "       MAX(B.NAME) NAME,                                                  "
 SQLStmt = SQLStmt & "       A.ID ID,                                                           "
 SQLStmt = SQLStmt & "       MAX(B.COURSE) SUBJECT,                                             "
 SQLStmt = SQLStmt & "       A.SUBJECT SUBJECT_CODE,                                            "
 SQLStmt = SQLStmt & "       A.id,                                                              "
 SQLStmt = SQLStmt & "       CASE                                                               "
 SQLStmt = SQLStmt & "           WHEN MAX(A.ATTEND)/MAX(D.CNT)IS NULL THEN 0                    "
 SQLStmt = SQLStmt & "           ELSE  CASE WHEN ROUND(MAX(A.ATTEND)/MAX(D.CNT)*100,0)>100      "
 SQLStmt = SQLStmt & "           THEN 100 ELSE    ROUND(MAX(A.ATTEND)/MAX(D.CNT)*100,0) END     "
 SQLStmt = SQLStmt & "       END ATTEND_RATE,                                                   "
 SQLStmt = SQLStmt & "       CASE                                                               "
 SQLStmt = SQLStmt & "           WHEN A.SUBJECT='LC' THEN ROUND(COUNT(*)/42.0 * 100,0)          "
 SQLStmt = SQLStmt & "           WHEN A.SUBJECT='SURVIVAL' THEN ROUND(COUNT(*)/41.0 * 100,0)    "
 SQLStmt = SQLStmt & "           WHEN A.SUBJECT='TOEICALL1' THEN ROUND(COUNT(*)/38.0 * 100,0)   "
 SQLStmt = SQLStmt & "      END PROGRESS_RATE,                                                  "
 SQLStmt = SQLStmt & "      CASE                                                                "
 SQLStmt = SQLStmt & "       WHEN MAX(E.L_SCORE1) IS NULL THEN 0 ELSE  MAX(E.L_SCORE1)          "
 SQLStmt = SQLStmt & "      END  L_SCORE1,                                                      "
 SQLStmt = SQLStmt & "      CASE                                                                "
 SQLStmt = SQLStmt & "       WHEN MAX(E.R_SCORE1) IS NULL THEN 0 ELSE  MAX(E.R_SCORE1)          "
 SQLStmt = SQLStmt & "      END R_SCORE1,                                                       "
 SQLStmt = SQLStmt & "      CASE                                                                "
 SQLStmt = SQLStmt & "       WHEN MAX(E.S_SCORE1) IS NULL THEN 0 ELSE  MAX(E.S_SCORE1)          "
 SQLStmt = SQLStmt & "      END S_SCORE1,                                                       "
 SQLStmt = SQLStmt & "      CASE                                                                "
 SQLStmt = SQLStmt & "       WHEN MAX(E.T_SCORE1) IS NULL THEN 0 ELSE  MAX(E.T_SCORE1)          "
 SQLStmt = SQLStmt & "      END T_SCORE1,                                                       "
 SQLStmt = SQLStmt & "                                                                          "
 SQLStmt = SQLStmt & "     CASE                                                                 "
 SQLStmt = SQLStmt & "       WHEN MAX(E.L_SCORE2) IS NULL THEN 0 ELSE  MAX(E.L_SCORE2)          "
 SQLStmt = SQLStmt & "      END  L_SCORE2,                                                      "
 SQLStmt = SQLStmt & "      CASE                                                                "
 SQLStmt = SQLStmt & "       WHEN MAX(E.R_SCORE2) IS NULL THEN 0 ELSE  MAX(E.R_SCORE2)          "
 SQLStmt = SQLStmt & "      END R_SCORE2,                                                       "
 SQLStmt = SQLStmt & "      CASE                                                                "
 SQLStmt = SQLStmt & "       WHEN MAX(E.S_SCORE2) IS NULL THEN 0 ELSE  MAX(E.S_SCORE2)          "
 SQLStmt = SQLStmt & "      END S_SCORE2,                                                       "
 SQLStmt = SQLStmt & "      CASE                                                                "
 SQLStmt = SQLStmt & "       WHEN MAX(E.T_SCORE2) IS NULL THEN 0 ELSE  MAX(E.T_SCORE2)          "
 SQLStmt = SQLStmt & "      END T_SCORE2,                                                        "
 SQLStmt = SQLStmt & "      CASE                                                                "
 SQLStmt = SQLStmt & "       WHEN MAX(E.T_SCORE) IS NULL THEN 0 ELSE  MAX(E.T_SCORE)            "
 SQLStmt = SQLStmt & "      END T_SCORE                                                         "
 SQLStmt = SQLStmt & " FROM COURSE A, MEMBER B, LECTURENET C,                                   "
 SQLStmt = SQLStmt & "       (                                                                  "
 SQLStmt = SQLStmt & "        SELECT COURSE, (MAX(SEQ)+0.00001)*1.0  CNT FROM LIST "
 SQLStmt = SQLStmt & "        WHERE SDATE <= CONVERT(VARCHAR(8),GETDATE(),112)                  "
 'SQLStmt = SQLStmt & "          AND HANWHA_SEQ=" &SEQ& "                                        "
 SQLStmt = SQLStmt & "        GROUP BY COURSE                                                   "
 SQLStmt = SQLStmt & "       )D ,                                                               "
 SQLStmt = SQLStmt & "      (                                                                   "
 SQLStmt = SQLStmt & "  SELECT  SEQ, COURSE,ID,                                                "
 SQLStmt = SQLStmt & "       SUM( CASE WHEN GUBN=2 THEN 0 ELSE L_SCORE*2.5 END) L_SCORE1,       "
 SQLStmt = SQLStmt & "   SUM( CASE WHEN GUBN=1 THEN 0 ELSE L_SCORE*2.5 END) L_SCORE2,       "
 SQLStmt = SQLStmt & "       SUM( CASE WHEN GUBN=2 THEN 0 ELSE R_SCORE*2.5 END) R_SCORE1,       "
 SQLStmt = SQLStmt & "   SUM( CASE WHEN GUBN=1 THEN 0 ELSE R_SCORE*2.5 END) R_SCORE2,       "
 SQLStmt = SQLStmt & "       SUM( CASE WHEN GUBN=2 THEN 0 ELSE S_SCORE*2.5 END) S_SCORE1,       "
 SQLStmt = SQLStmt & "   SUM( CASE WHEN GUBN=1 THEN 0 ELSE S_SCORE*2.5 END) S_SCORE2,       "
 SQLStmt = SQLStmt & "       SUM( CASE WHEN GUBN=2 THEN 0 ELSE T_SCORE*2.5 END) T_SCORE1,       "
 SQLStmt = SQLStmt & "   SUM( CASE WHEN GUBN=1 THEN 0 ELSE T_SCORE*2.5 END) T_SCORE2,       "
 SQLStmt = SQLStmt & "   SUM( T_SCORE*2.5 )/2 T_SCORE                                       "
 SQLStmt = SQLStmt & "  FROM TEST                                                              "
 SQLStmt = SQLStmt & "  WHERE SEQ=" &SEQ& "                                                    "
 SQLStmt = SQLStmt & "      AND ID<>'TEST'                                                   "
 SQLStmt = SQLStmt & "  GROUP BY SEQ,COURSE,ID                                                 "
 SQLStmt = SQLStmt & "      ) E                                                                 "
 SQLStmt = SQLStmt & " WHERE A.SEQ =" &SEQ& "                                                   "
 SQLStmt = SQLStmt & "        AND B.COMPANY LIKE  '" & PART & "'                             "
 SQLStmt = SQLStmt & "        AND A.SUBJECT LIKE  '"  & COURSE & "'                          "
 SQLStmt = SQLStmt & "        AND A.ID LIKE  '"  & ID & "'                                   "
 SQLStmt = SQLStmt & "    AND A.ID<>'TEST'                                                      "
 SQLStmt = SQLStmt & "    AND A.SUBJECT = B.GUBN                                                "
 SQLStmt = SQLStmt & "    AND A.SEQ = B.SEQ                                                     "
 SQLStmt = SQLStmt & "    AND A.ID  = B.ID                                                      "
 SQLStmt = SQLStmt & "    AND B.GUBN = C.SUBJECT                                                "
 SQLStmt = SQLStmt & "    AND B.SEQ  = C.SEQ                                                    "
 SQLStmt = SQLStmt & "    AND B.ID   = C.ID                                                     "
 SQLStmt = SQLStmt & "    AND C.SUBJECT = D.COURSE                                              "
 SQLStmt = SQLStmt & "    AND A.SUBJECT *= E.COURSE                                             "
 SQLStmt = SQLStmt & "    AND A.SEQ *= E.SEQ                                                    "
 SQLStmt = SQLStmt & "    AND A.ID  *= E.ID                                                     "
 SQLStmt = SQLStmt & " GROUP BY B.COMPANY,A.SUBJECT, A.ID                                       "
 SQLStmt = SQLStmt & " ORDER BY  COMPANY,SUBJECT,NAME                                           "

 
 'Response.write(SQLStmt)
 set rs_user_list = oConn.execute(SQLStmt)