这个我知道,我想问的是, select top n,好象应该是标准吧。 为何mysql会不支持国际标准呢?自搞一套呢?
select top n 从来就没有成为sql的官方标准。建议你不要道听途说,最好查查相关文献再说。The SQL standard provides three ways of performing a 'simple limit': * Using FETCH FIRST :(since SQL:2008) Non-core feature IDs F856, F857, F858, and F859 describe using SELECT ... FROM ... WHERE ... ORDER BY ... FETCH FIRST n ROWS ONLY You may write ROW instead of ROWS . * Using a Window function :(since SQL:2003) Non-core Feature ID T611 specifies window functions , of which one is ROW_NUMBER() OVER :
SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber , columns FROM tablename ) AS foo WHERE rownumber <= n * Using a cursor :If your application is stateful (in contrast to web applications which normally have to be seen as stateless), then you might look at cursors (core feature ID E121) instead. This involves: * DECLARE cursor-name CURSOR FOR ... * OPEN cursor-name * FETCH ... * CLOSE cursor-name
为何mysql会不支持国际标准呢?自搞一套呢?
SELECT ... FROM ... WHERE ... ORDER BY ... FETCH FIRST n ROWS ONLY You may write ROW instead of ROWS .
* Using a Window function :(since SQL:2003) Non-core Feature ID T611 specifies window functions , of which one is ROW_NUMBER() OVER :
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber ,
columns
FROM tablename
) AS foo
WHERE rownumber <= n * Using a cursor :If your application is stateful (in contrast to web applications which normally have to be seen as stateless), then you might look at cursors (core feature ID E121) instead. This involves: * DECLARE cursor-name CURSOR FOR ...
* OPEN cursor-name
* FETCH ...
* CLOSE cursor-name