db2 部分sql

上一篇 / 下一篇 2011-06-15 11:33:23 / 个人分类：敏捷测试

查看( 361 ) / 评论( 0 ) / 评分( 0 / 0 )

FETCH FIRST clause

Since ISO SQL:2008 results limits can be specified as in the following example using theFETCH FIRSTclause.

C!f"?@5Uc0SELECT * FROM TFETCH FIRST 10 ROWS ONLY

This clause currently is supported by IBM DB2, Sybase SQL Anywhere, PostgreSQL, EffiProz and HSQLDB version 2.0.

Result limits

Not all DBMSes support the mentioned window functions, and non-standard syntax has to be used. Below, variants of thesimple limitquery for different DBMSes are listed:51Testing软件测试网D8HDrZ%|

`SELECT * FROM TLIMIT 10 OFFSET 20`	Netezza, MySQL, PostgreSQL (also supports the standard, since version 8.4), SQLite, HSQLDB, H2
`SELECT * from TWHERE ROWNUM <= 10`	Oracle (also supports the standard, since Oracle8i)
`SELECTFIRST 10* from T`	Ingres
`SELECTFIRST 10* FROM T order by a`	Informix
`SELECTSKIP 20 FIRST 10* FROM T order by c, d`	Informix (row numbers are filtered after order by is evaluated. SKIP clause was introduced in a v10.00.xC4 fixpack)
`SELECTTOP 10* FROM T`	MS SQL Server, Sybase ASE, MS Access
`SELECTTOP 10 START AT 20* FROM T`	Sybase SQL Anywhere (also supports the standard, since version 9.0.1)
`SELECTFIRST 10 SKIP 20* FROM T`	Interbase, Firebird
`SELECT * FROM TROWS 20 TO 30`	Firebird (since version 2.1)	-S.oJ7w?!Q0
`SELECT * FROM T ;{c*^-v tr\|9]I\0WHERE ID_T > 10 FETCH FIRST 10 ROWS ONLY`	DB2
`SELECT * FROM T51Testing软件测试网(u-c5T2[O@&y{ WHERE ID_T > 20 FETCH FIRST 10 ROWS ONLY`	DB2 (new rows are filtered after comparing with key column of table T)