如何在分页上加关联表查询哇？

[lihao1129]

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
但是这怎么才能结合起来呢？

[007-x]

要先关联查询,再实现分页才合理.

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

[lihao1129]

嗯。。但是如何去实现这个呢？

[lihao1129]

	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;
	}

[gelyon]

String sql 你不会拼接啊

[lihao1129]

[Quote=引用 1 楼 tx2730 的回复:]

要先关联查询,再实现分页才合理.
SQL code
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
[/Quote]

好像并不行，报错说 为明确定义

[lihao1129]

[Quote=引用 4 楼 gelyon 的回复:]

String sql 你不会拼接啊
[/Quote]

求赐教

[lihao1129]

主要是这个SQL语句都还木有搞定，怎么拼接呢？

[007-x]

呵, java不懂,帮顶!

[lihao1129]

[Quote=引用 8 楼 tx2730 的回复:]

呵, java不懂,帮顶!
[/Quote]
呵呵， 我就是 想 要一个 SQL 就是 关联分页查询的作用的
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

下面这个是按楼上写的，但是没起作用，呵呵 求高手帮忙