(매출조회)과정평가형 정보처리산업기사 홈쇼핑 회원관리 프로그램

값을변경 2022. 2. 12. 18:17

테이블 두개 + sum 함수

select n.custno ncust, sum(n.price) tprice, m.custno mcust, m.custname, m.grade from 
(select custno, custname, grade from member02)m, money02 n 
where n.custno = m.custno 
group by n.custno,  m.custno, m.custname, m.grade 
order by tprice desc;

함수나오면 group by

매출 높은 순위로 내림차순 order by 칼럼 decs

전체

<%@page import="DBPKG.dbcon"%>
<%@page import="java.sql.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
    <%
    Connection con = null;
   	Statement stmt = null;
   	ResultSet rs = null;
   	
   	try{
   		
   		con = dbcon.getConnection();
   		stmt = con.createStatement();
   		String sql ="select n.custno ncust, sum(n.price) tprice, m.custno mcust, m.custname, m.grade from "+
   				"(select custno, custname, grade from member02)m, money02 n "+
   				"where n.custno = m.custno "+
   				"group by n.custno,  m.custno, m.custname, m.grade "+
   				"order by tprice desc";
   		rs = stmt.executeQuery(sql);
    %>
    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	
	<jsp:include page="header.jsp"></jsp:include>
	
	<section>
	
		<h3>회원매출조회</h3>
		
		<div class="tablediv">
			<table>
				<tr>
					<th>회원번호</th>
					<th>회원성명</th>
					<th>고객등급</th>
					<th>매출</th>
				</tr>
				
				<tr>
				<%
				while(rs.next()){
					String grade =rs.getString("grade") ;
					switch(grade){
					case "A" : grade = "VIP"; break;
					case "B" : grade = "일반"; break;
					case "C" : grade = "직원"; break;
					}
				%>
					<td><%=rs.getString("ncust") %></td>
					<td><%=rs.getString("custname") %></td>
					<td><%=grade%></td>
					<td><%=rs.getString("tprice") %></td>
					</tr>
				<%
				} 
				%>
				
				
			</table>
		</div>
	</section>
	
	<%

    if(rs!=null) rs.close();
	if(stmt!=null) stmt.close();
	if(con!=null) con.close();
}catch(Exception e){
e.printStackTrace();
}
	%>
	
	<jsp:include page="footer.jsp"></jsp:include>
</body>
</html>

저작자표시 비영리 변경금지 (새창열림)