请教使用SQL如果数据库里有很多数据, 由于内存有限, 每次我只取固定数目的数据, 比如每次取100行, 下一次再取接着的100行, 应该怎么写? 谢谢.
table name: table
Select name from table
?
?
?[我心为谁动 (12-20 10:01, Long long ago)]
[ 传统版 |
sForum ][登录后回复]1楼
you can write a SQL such thatthe top 100 records are return by using an estimated cutoff.
if you are using MSSQL+ASP.NET, you can query with a method rank(int numberofrecords) or somthing similar. not sure about other databases/data access technology[SmellsLikeTeenSpirit (12-20 10:17, Long long ago)]
[ 传统版 |
sForum ][登录后回复]2楼
(引用 SmellsLikeTeenSpirit:you can write a SQL such thatthe top 100 records are return by using an estimated cutoff. if you are using MSSQL+ASP.NET, you c...)ok, thank you. buti am using java with a oracle database. the rank method, is it a standard sql function or only provided by MSsql+ASP.net?[我心为谁动 (12-20 10:23, Long long ago)] [ 传统版 | sForum ][登录后回复]3楼
(引用 我心为谁动:ok, thank you. buti am using java with a oracle database. the rank method, is it a standard sql function or only provided by MSs...)No need ASP.NET lah.Try to create a cursor on your select query and retreat data entry by entry.[Flying (12-20 10:55, Long long ago)] [ 传统版 | sForum ][登录后回复]4楼
(引用 Flying:No need ASP.NET lah.Try to create a cursor on your select query and retreat data entry by entry.)can you demonstrate how to do that? thank you[我心为谁动 (12-20 11:06, Long long ago)] [ 传统版 | sForum ][登录后回复]5楼
这个问题挺有意思,我们研究研究如果你不能改变table schema的话, 我想只能选取100行,而接着的100行就不能选. 我想你用的是JDBC driver. Statement class 里有个 setFetchSize(int rows) method, 你可以设rows 为100 in your case.
如果你能改变database table schema 的话, 你可以给那个table 多加一个 column是row 的index. 这样每次你选index从当前值到增加100, 然后更新当前index值.
你试试.:)[恐龙特急克塞号 (12-20 11:14, Long long ago)]
[ 传统版 |
sForum ][登录后回复]6楼
(引用 Flying:No need ASP.NET lah.Try to create a cursor on your select query and retreat data entry by entry.)hmm....cursor, another advanced non-SQL thingy?[SmellsLikeTeenSpirit (12-20 11:37, Long long ago)] [ 传统版 | sForum ][登录后回复]7楼
(引用 恐龙特急克塞号:这个问题挺有意思,我们研究研究如果你不能改变table schema的话, 我想只能选取100行,而接着的100行就不能选. 我想你用的是JDBC driver. S...)enn...如果我有选择条件, 那么它去数据时是先取100行在根据条件过滤, 还是先过滤再取100行呢? 好像后者的可能性比较大[我心为谁动 (12-20 12:10, Long long ago)] [ 传统版 | sForum ][登录后回复]8楼
(引用 我心为谁动:enn...如果我有选择条件, 那么它去数据时是先取100行在根据条件过滤, 还是先过滤再取100行呢? 好像后者的可能性比较大)其实我没试过:)[恐龙特急克塞号 (12-20 13:17, Long long ago)] [ 传统版 | sForum ][登录后回复]9楼
(引用 SmellsLikeTeenSpirit:hmm....cursor, another advanced non-SQL thingy?)dynamic sql[恐龙特急克塞号 (12-20 13:18, Long long ago)] [ 传统版 | sForum ][登录后回复]10楼