Paging SQL

Perfil de Zhong简单做人FotosBlogListasMás

Herramientas

Ayuda

< Anterior Siguiente >

14 mayo

前些时候面试，有个问题是问我Web Paging SQL，我若干年前解决过n遍，可是记性真差，居然答不上来，具汗。从网上看到整理如下：

Oracle:

select * from (select row_.*, rownum rownum_ from (Your_SQL ) row_ where rownum <= max) where rownum_ >= min

SQL Server:

select top @pagesize * from tablename where id not in (select top @pagesize*(@page-1) id from tablename order by id) order by id

MySQL:

select * from tablename limit position, counter

DB2

select * from (select *, rownumber() as ROW_NEXT from tablename) where ROW_NEXT between min and max

这个SQL Server的Paging SQL比较难看，我相信SQL2005已经支持类似rownumber的功能。

21:31 | Agregar al blog | 软件开发（Java, .Net, Oracle, etc.）

Comentarios (4)

Para agregar un comentario, inicia sesión con tu cuenta de Windows Live ID (si utilizas Hotmail, Messenger o Xbox LIVE, ya tienes una cuenta de Windows Live ID). Iniciar sesión

¿No tienes una cuenta de Windows Live ID? Regístrate

	Zhongescribió: 再增加一个对postgres： http://microjet.ath.cx/WebWiki/ResultPaginationWithPostgresql.html 27 Sep
	Zhongescribió: http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html 7 Julio
	Zhongescribió: For SQL Server, if cannot count on ID to sort (i.e. order by other columns), then have to follow the "standard" solution - create a temp table to store the result first 6 Junio
	Zhongescribió: Yes, MS SQL2005 now has build-in Row_Numer function. But DataGrid is doing its paging (Microsoft always try its best to provide "user" feature out of box and hide "technical" feature), so a customized paging code has to be provided to DataGrid if rownum is used. In fact, paging can be done in different tier (database, ADO, DataSet, DataGrid, client/javascript), but database tier is the best for larget data set. 26 May

Vínculos de referencia

La dirección URL del vínculo de referencia de esta entrada es:

http://zlit.spaces.live.com/blog/cns!8861E8E763F023DA!155.trak

Weblogs que hacen referencia a esta entrada

Ninguno