(지역별_검사건수통계)과정평가형 정보처리산업기사_v1 진단검사 프로그램

---

 

 

쿼리  count함수 / group by !!

select  ppcity, count(ppcity) from 

(select r.p_no rpno, p.p_no ppno, p.p_city ppcity from 

(select * from tbl_patient_202004) p, 

tbl_result_202004 r where r.p_no = p.p_no ) 

  GROUP by ppcity 

  order by ppcity ;

 

jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

    <jsp:include page="header.jsp"></jsp:include>
    
    <section>
    	<h3>(지역별)검사건수통계</h3>
    	
    	<div class="vTableDiv">
    		<table class="vTable">
    			<tr>
    				<th>지역코드</th>
    				<th>지역별</th>
    				<th>검사건수</th>
    			</tr>
    			<%
    			Connection con = null;
    			Statement stmt = null;
    			ResultSet rs = null;
    			
    			con = dbcon.getConnection();
    			stmt = con.createStatement();
				String sql = "select  ppcity, count(ppcity) from "+ 
					"(select r.p_no rpno, p.p_no ppno, p.p_city ppcity from "+
					"(select * from tbl_patient_202004) p, tbl_result_202004 r "+
					"where r.p_no = p.p_no ) "+
					"GROUP by ppcity "+
					"order by ppcity ";
			    rs=stmt.executeQuery(sql);
			    while(rs.next()){
			    	String city = rs.getString(1);
			    	String cityStr ="" ;
			    		switch(city){
			    		case "10": cityStr = "서울"; break;
			    		case "20": cityStr = "경기"; break;
			    		case "30": cityStr = "강원"; break;
			    		case "40": cityStr = "대구"; break;
			    		}
			    	String cityCount = rs.getString(2);
				%>
				
    			<tr>
    				<td><%=city %></td>
    				<td><%=cityStr %></td>
    				<td><%=cityCount %></td>
    			<%} %>
    			</tr>
    			
    		</table>
    	</div>
    </section>
    
    <jsp:include page="footer.jsp"></jsp:include>