※ 引述《jsu (右持滑鼠左打键盘)》之铭言： : 在不动用 store procedure 的情况下，想请教有什麽正规的做法吗？ * SQL Server 2005 开始支援 ANSI 92 SQL 标准，支援 LIMIT 了。 * 如果不想换，而且资料更动幅度不大的话，可以善用 .NET 里面的 Cache ， 或是丢到 client 的 session 。 * 直接使用 .NET 的 DataGrid ，支援换页。 另外， 在 http://en.wikipedia.org/wiki/Select_%28SQL%29 里面有这段： Limiting result rows In ISO SQL:2003, result sets may be limited by using * cursors, or * By introducing window functions to the SELECT-statement ============================================================================= ROW_NUMBER() window function Several window functions exist. ROW_NUMBER() OVER may be used for a simple limit on the returned rows. E.g., to return no more than ten rows: SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber, columns FROM tablename ) AS foo WHERE rownumber <= 10 ROW_NUMBER can be non-deterministic: if key is not unique, each time you run the query it is possible to get different row numbers assigned to any rows where key is the same. When key is unique, each row will always get a unique row number. ============================================================================= RANK() window function The RANK() OVER window function acts like ROW_NUMBER, but may return more than n rows in case of tie conditions. E.g., to return the top-10 youngest persons: SELECT * FROM ( SELECT RANK() OVER (ORDER BY age ASC) AS ranking, person_id, person_name, age FROM person ) AS foo WHERE ranking <= 10 The above code could return more than ten rows, e.g. if there are two people of the same age, it could return eleven rows. ============================================================================= Non-standard syntax Not all DBMSes support the mentioned window functions, and non-standard syntax has to be used. Below, variants of the simple limit query for different DBMSes are listed: Vendor Limit Syntax -------------------------------------------------- DB2 SELECT * FROM T FETCH FIRST 10 ROWS ONLY Firebird SELECT FIRST 10 * FROM T Informix SELECT FIRST 10 * FROM T Interbase SELECT * FROM T ROWS 10 Microsoft (Supports the standard, since SQL Server 2005) Also SELECT TOP 10 [PERCENT] * FROM T ORDER BY col MySQL SELECT * FROM T LIMIT 10 SQLite SELECT * FROM T LIMIT 10 PostgreSQL SELECT * FROM T LIMIT 10 Oracle (Supports the standard, since Oracle8i) Also SELECT * from T WHERE ROWNUM <= 10 -- ╓╥╥╖╓─╥╖ ╓─╥╖ ╓ ╓╖ ╓─╥╖ ╓─╥╖ ╓╖╓╖ ╟╢ ║ ╟╢ ║ ╙╜ ║ ╟╢ ║ ╟╢ ║ ╟╢ ║║╟╢ ╟╢ ║ ╟╢ ╟─ ╟─╫╢ ║ ╟╢ ╟─╫╜ ║║╟╢ ╟╢ ║ ╟╢ ║ ╓╖ ║ ╟╢ ║ ╟╢ ║ ║ ║║╟╢ ╙╨╜ ╙─╨╜ ╙─╨╜ ╙ ╙╜ ╙─╨╜ ╙ ╙╜ ╙╙╨╜ 狮子男 --

※ 发信站: 批踢踢实业坊(ptt.cc) ◆ From: 59.113.173.186