๊ตญ๋น์ง์ D+79
๊ณผ์ ํ๊ฐํ ์ ๋ณด์ฒ๋ฆฌ์ฐ์ ๊ธฐ์ฌ
- ํ์๋งค์ถ์กฐํํ๋ฉด(sale.jsp) ๊ตฌํ -
ํ์๋งค์ถ์กฐํํ๋ฉด(sale.jsp) ๊ตฌํ
ํ์๋งค์ถ์กฐํ๋ ํ์ํ ์ด๋ธ๊ณผ ํ์๋งค์ถํ ์ด๋ธ์ ์กฐํฉํด ํ๋ฉด์ ๋์ฐ๋ ๊ฒ์ผ๋ก join์ ํด์ผ ํ๋ค.
์ด๋ ๋ ํ ์ด๋ธ๊ฐ์ ๊ณตํต์ผ๋ก ๊ฒน์น๋ ์ ๋ณด๋ง ํ๋ฉด์ ๋์์ค ์์ ์ด๊ธฐ ๋๋ฌธ์ inner join์ผ๋ก ๋ฐ์ดํฐ ์ ๋ณด๋ฅผ ๋์ด์ฌ ์ ์๋๋ก ํ๋ค.
• sale.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="DBPKG.DBConnection" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>ํ์๋งค์ถ์กฐํ</title>
<link rel="stylesheet" href="main.css">
</head>
<body>
<header>
<div>
<h2>์ผํ๋ชฐ ํ์๊ด๋ฆฌ ver 1.0</h2>
</div>
</header>
<nav>
<div>
<a href="register.jsp">ํ์๋ฑ๋ก</a>
<a href="inquiry.jsp">ํ์๋ชฉ๋ก์กฐํ/์์ </a>
<a href="sale.jsp">ํ์๋งค์ถ์กฐํ</a>
<a href="index.jsp">ํ์ผ๋ก</a>
</div>
</nav>
<section>
<div>
<h3>ํ์๋งค์ถ์กฐํ</h3>
<table border="1" style="width: 500px">
<tr>
<th>ํ์๋ฒํธ</th>
<th>ํ์์ฑ๋ช
</th>
<th>๊ณ ๊ฐ๋ฑ๊ธ</th>
<th>๋งค์ถ</th>
</tr>
<%
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
String custno = "";
String custname = "";
String grade = "";
String sale = "";
String sql = "select member.custno, member.custname, member.grade, sum(money.price) " +
"from member_tbl_02 member inner join money_tbl_02 money " +
"on member.custno = money.custno " +
"group by member.custno, member.custname, member.grade " +
"order by sum(money.price) desc";
try
{
conn = DBConnection.getConnection();
stmt = conn.createStatement();
rset = stmt.executeQuery(sql);
while(rset.next())
{
custno = rset.getString(1);
custname = rset.getString(2);
grade = rset.getString(3);
sale = rset.getString(4);
switch(grade)
{
case "A":
grade = "VIP";
break;
case "B":
grade = "์ผ๋ฐ";
break;
case "C":
grade = "์ง์";
break;
default:
grade = "๋ฏธ์ ";
break;
}
%>
<tr>
<td><%= custno %></td>
<td><%= custname %></td>
<td><%= grade %></td>
<td><%= sale %></td>
</tr>
<%
}
}
catch(Exception e)
{
System.out.println("[ERROR] " + e.getMessage());
e.printStackTrace();
}
%>
</table>
</div>
</section>
<footer>
<div>
<h4>HRDKOREA Copyright@2016 All rights reserved. Human Resources Development Service of Korea</h4>
</div>
</footer>
</body>
</html>
๋ฐ์ํ
๋๊ธ