MS-SQL과 비슷하다기에 하다보니..
서브쿼리에서 TOP이 안먹더군요.
검색해서 찾고 찾아서... 가상테이블을 이용해서 페이징을 완성했습니다.
참고하세요.
PS)쿼리문중에 조건부분은 적절히 수정하셔셔 분석하시기 바랍니다.~
sybase버젼은 아래와 같으며
SELECT @@version
Adaptive Server Enterprise/12.5.4/EBF 14340 ESD#5/P/Sun_svr4/OS 5.8/ase1254/2061/64-bit/FBO/Fri Apr 13 07:33:34 2007
===========================================================================================
int iPageSize = 10; //출력시킬 레코드 개수
int iPageSet = 10; //페이징 단위
int iMaxCount = 0;
String strSort = " REGDATE DESC "; //정렬기준
String sqlstr = null; //sql 쿼리
String strSubSql = null; //sql 서브
String strCols = ""; //컬럼
if(iPage==null || iPage.equals("") == true){
iPage = "1";
}
Date today = new Date();
SimpleDateFormat date = new SimpleDateFormat("yyMMdd");
ToDay_Value=date.format(today);
if (startDate.equals("")==false){
Start_ = startDate.replaceAll("\\-","").substring(2,8).trim();
}
if (endDate.equals("")==false){
End_ = endDate.replaceAll("\\-","").substring(2,8).trim();
}
//페이징 계산부분
int Page_Total = iPageSize*Integer.parseInt(iPage);
int Page_Delete = (iPageSize*Integer.parseInt(iPage))-iPageSize;
//조건 부분
if(Start_==null || Start_.equals("") == true || End_==null || End_.equals("") == true){
if (strQueryType.equals("")==true){
strSubSql = " WHERE 1=1 AND STAFF_AGENT_STATE='"+Sa_state+"'";
}else{
strSubSql = " WHERE 1=1 AND STAFF_AGENT_STATE='"+Sa_state+"' AND ("+strQueryType+" like '%"+strQueryWord+"%')";
}
}else{
strSubSql = " WHERE STAFF_AGENT_STATE='"+Sa_state+"' AND (DATEDIFF(day,REGDATE, '"+Start_+"') <= 0 AND DATEDIFF(day,REGDATE, '"+End_+"') >= 0) AND ("+strQueryType+" like '%"+strQueryWord+"%')";
}
StringBuffer sql = new StringBuffer();
try{
conn = SFADBManager.getConnection();
sqlstr = " SELECT COUNT(*) AS counter FROM 테이블명 "+strSubSql+"";
pstmt = new LoggablePreparedStatement(conn, sqlstr.toString());
rs = pstmt.executeQuery();
if(rs.next()){
iMaxCount = rs.getInt("counter");
}else{
iMaxCount = 0;
}
rs.close();
strCols= "불러올 컬럼";
sqlstr = "DECLARE @PAGE_TOTAL INT" +
" DECLARE @PAGE_DELETE INT" +
" SET @PAGE_TOTAL="+Page_Total+"" +
" SET @PAGE_DELETE="+Page_Delete+"" +
" SET ROWCOUNT @PAGE_TOTAL" +
" SELECT "+strCols+" INTO #TEMPA FROM 테이블명 "+strSubSql+" ORDER BY "+strSort+"" +
" SET ROWCOUNT @PAGE_DELETE" +
" IF @PAGE_DELETE > 0" +
" BEGIN" +
" DELETE FROM #TEMPA" +
" END" +
" SELECT * FROM #TEMPA" +
" DROP TABLE #TEMPA";
//out.println(sqlstr);
pstmt = new LoggablePreparedStatement(conn, sqlstr.toString());
rs = pstmt.executeQuery();
======================
[1][2][3][4][5]...
이부분에서는 iPage라는 변수로 get방식으던 post방식이던 넘겨주면 페이징이 됩니다.
=======================
|