Re: [SQL ] M$SQL 實作 limit 疑問

看板Database (資料庫)作者JoeHorn (獅子男)時間18年前 (2007/02/06 04:24)推噓1(1推 0噓 4→)

留言5則, 2人參與討論串3/3 (看更多)

※ 引述《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