SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM 表1) A WHERE ROWNUM <= 20)WHERE RN >= 0
我有表1 跟表2 他们的ID相同 我想他们关联查询分页出来,现在 这个分页是可以实现的,但是如何关联查询呢?我只知道SELECT * FROM 表1 a,表2 b WHERE a.id = b.id
但是这怎么才能结合起来呢?
我有表1 跟表2 他们的ID相同 我想他们关联查询分页出来,现在 这个分页是可以实现的,但是如何关联查询呢?我只知道SELECT * FROM 表1 a,表2 b WHERE a.id = b.id
但是这怎么才能结合起来呢?
SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM 表1 a,表2 b WHERE a.id = b.id
) A WHERE ROWNUM <= 20)WHERE RN >= 0
public PageResult<AuthStat> getAuthStatByTimeType(int authId, Date startTime,
Date endTime, int timeType, int startIndex, int endIndex) {
// TODO Auto-generated method stub
PageResult<AuthStat> pageResult = new PageResult<AuthStat>();
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
StringBuffer sb = new StringBuffer();
ArrayList<AuthStat> list = new ArrayList<AuthStat>();
sb.append("select * from ");
sb.append(TABLE_A);
sb
.append("where authId = ? and timeType = ? and time between ? and ? and rownum <= ? and rownum >= ?");
String sql = "select b.*,ROWNUM from (select a.*,ROWNUM as rn from (" +
"select * from "+ TABLE_A +" where authId = ? and timeType = ? and ( time between ? and ? ) ) a where ROWNUM< " +endIndex
+") b where rn >= " + startIndex;
String sqlCount = "select count(*) num from "+TABLE_A;
try {
conn = dataSource.getConnection();
stat = conn.prepareStatement(sql);
stat.setInt(1, authId);
stat.setInt(2, timeType);
stat.setDate(3, startTime);
stat.setDate(4, endTime);
rs = stat.executeQuery();
while (rs.next()) {
AuthStat authStat = new AuthStat();
authStat.setId(rs.getInt("id"));
authStat.setAuthID(rs.getInt("authId"));
authStat.setExeNum(rs.getInt("exeNum"));
authStat.setExeMaxTime(rs.getInt("exeMaxTime"));
authStat.setExeMinTime(rs.getInt("exeMinTime"));
authStat.setExeAvgTime(rs.getInt("exeAvgTime"));
authStat.setUpdateTime(rs.getDate("updateTime"));
authStat.setTime(rs.getDate("time"));
authStat.setResSuccessNum(rs.getInt("resSuccessNum"));
authStat.setResFallNum(rs.getInt("resFallNum"));
list.add(authStat);
}
stat = conn.prepareStatement(sqlCount);
rs = stat.executeQuery();
if (rs.next()) {
pageResult.setTotal(rs.getLong("num"));
}
pageResult.setData(list);
} catch (SQLException e) {
logger.error(e);
} finally {
try {
if (rs != null)
rs.close(); if (stat != null)
stat.close();
if (conn != null)
conn.close(); } catch (SQLException e) {
logger.error(e);
}
}
return pageResult;
}
SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM t_apistaistical a, t_right b WHERE a.authid = b.id) A WHERE ROWNUM <= 20)WHERE RN >= 0下面这个是按楼上写的,但是没起作用,呵呵 求高手帮忙