<%@page import="java.sql.ResultSet"%>
<%@include file="db.jsp" %>
<%@page import="java.sql.PreparedStatement"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<jsp:include page="header.jsp"></jsp:include>
<section>
<form>
<table border="1">
<tr>
<td>학년</td>
<td>반</td>
<td>번호</td>
<td>이름</td>
<td>국어</td>
<td>수학</td>
<td>영어</td>
<td>역사</td>
<td>합계</td>
<td>평균</td>
<td>순위</td>
</tr>
<%
request.setCharacterEncoding("UTF-8");
int skor = 0;
int smath = 0;
int seng = 0;
int shist = 0;
int cnt = 0;
try {
String sql = "select substr(s.sno, 1, 1) as 학년, substr(s.sno, 2, 2) as 반, substr(s.sno, 4, 2) as 번호, " +
"s.sname as 이름, e.ekor as 국어, e.emath as 수학, e.eeng as 영어, e.ehist as 역사, " +
"sum(e.ekor + e.emath + e.eeng + e.ehist) as 합계, " +
"sum(e.ekor + e.emath + e.eeng + e.ehist) / 4 as 평균, " +
"rank() over(order by nvl(sum(e.ekor + e.emath + e.eeng + e.ehist), 0) desc) as 순위 " +
"from student_tbl_03 s, exam_tbl_03 e " +
"where s.sno = e.sno(+) " +
"group by substr(s.sno, 1, 1), substr(s.sno, 2, 2), substr(s.sno, 4, 2), s.sname, e.ekor, e.emath, e.eeng, e.ehist";
PreparedStatement pstmt = c.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
%>
<tr>
<td><%=rs.getString(1) %></td>
<td><%=rs.getString(2) %></td>
<td><%=rs.getString(3) %></td>
<td><%=rs.getString(4) %></td>
<td><%=rs.getString(5) %></td>
<td><%=rs.getString(6) %></td>
<td><%=rs.getString(7) %></td>
<td><%=rs.getString(8) %></td>
<td><%=rs.getString(9) %></td>
<td><%=rs.getString(10) %></td>
<td><%=rs.getString(11) %></td>
</tr>
<%
skor = skor + rs.getInt(5);
smath += rs.getInt(6);
seng += rs.getInt(7);
shist += rs.getInt(8);
cnt++;
}
} catch(Exception e) {
e.printStackTrace();
}
%>
<tr>
<td colspan="4">총합계</td>
<td><%=skor %></td>
<td><%=smath %></td>
<td><%=seng %></td>
<td><%=shist %></td>
<td><%=skor + smath + seng + shist %></td>
<td><%=(skor + smath + seng + shist) / 4 %></td>
<td rowspan="2"></td>
</tr>
<tr>
<td colspan="4">총평균</td>
<td><%=skor/cnt %></td>
<td><%=smath/cnt %></td>
<td><%=seng/cnt %></td>
<td><%=shist/cnt %></td>
<td><%=(skor + smath + seng + shist) / cnt %></td>
<td><%=(skor + smath + seng + shist) / (cnt * 4) %></td>
</tr>
</table>
</form>
</section>
<jsp:include page="footer.jsp"></jsp:include>
</body>
</html>