{"id":2503,"date":"2014-07-11T08:45:00","date_gmt":"2014-07-11T00:45:00","guid":{"rendered":"http:\/\/kuki.idv.tw\/?p=2503"},"modified":"2014-07-11T08:45:00","modified_gmt":"2014-07-11T00:45:00","slug":"%e8%bd%89sql-server%e5%b9%be%e7%a8%ae%e5%88%86%e9%a0%81paging%e5%af%ab%e6%b3%95","status":"publish","type":"post","link":"https:\/\/www.kuki.idv.tw\/?p=2503","title":{"rendered":"[\u8f49][SQL Server]\u5e7e\u7a2e\u5206\u9801(Paging)\u5beb\u6cd5"},"content":{"rendered":"

\u4f86\u6e90\u00a0http:\/\/www.dotblogs.com.tw\/jimmyyu\/archive\/2009\/11\/09\/11499.aspx<\/p>\n

\n

\u4ee5\u4e0b\u4ecb\u7d39\u5e7e\u7a2e\u5728SQL Server\u4e0a\u7c21\u55ae\u7684\u5206\u9801\u505a\u6cd5\uff0c\u5927\u81f4\u5206\u70ba\u4e09\u7a2e\uff1a<\/p>\n

1.Order By\u642d\u914dnot in<\/p>\n

2.Temp Table<\/p>\n

3.ROW_NUMBER() function<\/p>\n

\n

\u4ee5\u4e0b\u4ee5Northwind\u8cc7\u6599\u5eab\u4f5c\u70ba\u7bc4\u4f8b\uff1a<\/p>\n

Order By\u642d\u914dnot in<\/span><\/strong><\/p>\n

\n
\n
\n
\n\n\n\n
1<\/code><\/td>\nSelect<\/code>\u00a0Top<\/code>\u00a010 CustomerID, CompanyName ,ContactName, Country\u00a0<\/code>from<\/code>\u00a0Customers\u00a0<\/code>where<\/code>\u00a0CustomerID\u00a0<\/code>Not<\/code>\u00a0in<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
\n\n\n\n
2<\/code><\/td>\n(<\/code>Select<\/code>\u00a0top<\/code>\u00a010 CustomerID\u00a0<\/code>from<\/code>\u00a0Customers\u00a0<\/code>order<\/code>\u00a0by<\/code>\u00a0CustomerID)<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
\n\n\n\n
3<\/code><\/td>\nOrder<\/code>\u00a0by<\/code>\u00a0CustomerID<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

\u6b64\u505a\u6cd5\u662f\u900f\u904eWhere\u689d\u4ef6\u4e2d\u7684not in\u6392\u9664\u6389\u524d\u5341\u7b46\u8cc7\u6599\uff0c\u4ee5\u4e0b\u662f\u524d20\u7b46\u8cc7\u6599\uff1a<\/p>\n

\u00a0\"image\"<\/a><\/p>\n

not in\u6703\u5e6b\u5fd9\u6392\u9664\u6389CustomerID\u70ba1-10\u7b46\u7684\u5167\u5bb9\uff0c\u56e0\u6b64\u6211\u5011\u53ef\u4ee5Select\u523011-20\u7b46\u7684\u8cc7\u6599\u7d50\u679c\uff1a<\/p>\n

\"image\"<\/a><\/p>\n

\n

Temp Table<\/span><\/strong><\/p>\n

\n

\n
\n
\n
\n\n\n\n
1<\/code><\/td>\nselect\u00a0\u00a0 identity(<\/code>int<\/code>,1,1) RowNum, CustomerID, CompanyName, ContactName, Country\u00a0 into TempTable from Customers<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

\u5c07Select\u51fa\u4f86\u7684\u6b04\u4f4d\u8cc7\u6599\u653e\u5230\u53e6\u4e00\u500bTemp Table\u4e2d\uff0c\u8a72Temp Table\u7684\u7b2c\u4e00\u500b\u6b04\u4f4d\u662fidentity\uff0c\u70ba\u81ea\u52d5\u905e\u589e\u7684\u6b04\u4f4d\uff0c\u63a5\u8457\u6211\u5011\u53ef\u5c0d\u8a72Temp Table\u7684RowNum(identity\u6b04\u4f4d)\u9032\u884c\u689d\u4ef6\u904e\u6ffe\uff1b<\/p>\n

\n
\n
\n
\n\n\n\n
1<\/code><\/td>\nSelect<\/code>\u00a0*\u00a0<\/code>from<\/code>\u00a0Temp<\/code>\u00a0where<\/code>\u00a0RowNum >= 11\u00a0<\/code>AND<\/code>\u00a0RowNum <= 20<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

\u7531\u65bc\u7b2c\u4e00\u500b\u6b04\u4f4d\u662f\u81ea\u52d5\u905e\u589e\uff0c\u6211\u5011\u5c31\u53ef\u4ee5\u5c07\u5b83\u7576\u6210\u662f\u6211\u5011\u6240\u8981\u7684RowNum\u4f86\u9032\u884c\u67e5\u8a62\uff0c\u5f97\u5230\u7684\u8cc7\u6599\u5982\u4e0b\uff1a<\/p>\n

\"image\"<\/a><\/p>\n

\n

ROW_NUMBER() Function<\/span><\/strong><\/p>\n

\u9019\u500b\u529f\u80fd\u662f\u5728SQL Server 2005\u5f8c\u7684\u7248\u672c\u624d\u6709\u63d0\u4f9b\u7684\uff0c\u6211\u5011\u53ef\u900f\u904e\u4ee5\u4e0b\u7684\u8a9e\u6cd5\u4f86\u9032\u884c\u8655\u7406\uff1a<\/p>\n

\n
\n
\n
\n\n\n\n
1<\/code><\/td>\nSELECT<\/code>\u00a0\u00a0*<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
\n\n\n\n
2<\/code><\/td>\nFROM<\/code>\u00a0(<\/code>SELECT<\/code>\u00a0\u00a0ROW_NUMBER() OVER (<\/code>ORDER<\/code>\u00a0BY<\/code>\u00a0CustomerID)<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
\n\n\n\n
3<\/code><\/td>\nAS<\/code>\u00a0RowNum, CustomerID, CompanyName, ContactName, Country\u00a0<\/code>FROM<\/code>\u00a0Customers)<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
\n\n\n\n
4<\/code><\/td>\nAS<\/code>\u00a0NewTable<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
\n\n\n\n
5<\/code><\/td>\nWHERE<\/code>\u00a0RowNum >= 11\u00a0<\/code>AND<\/code>\u00a0RowNum <= 20<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

Select \u51fa\u4f86\u7684\u8cc7\u6599\u5982\u4e0b\uff1a<\/p>\n

\"image\"<\/a><\/p>\n

\n

\u5728\u7ba1\u7406\u4fbf\u5229\u6027\u4e0a\uff0c\u6211\u500b\u4eba\u6bd4\u8f03\u4e0d\u50be\u5411\u9078\u64c7Temp Table\u7684\u65b9\u5f0f\uff0c1.3\u5169\u7a2e\u505a\u6cd5\u662f\u6211\u6bd4\u8f03\u6703\u53bb\u4f7f\u7528\u7684\uff0c\u800c\u5982\u679c\u53ef\u4ee5\u9078\u64c7\u7684\u8a71\u6211\u61c9\u8a72\u6703\u4ee5ROW_NUMBER()\u9019\u500bFunction\u7684\u89e3\u6cd5\u512a\u5148\uff0c\u6211\u5011\u4ee5\u9019\u5169\u53e5SQL\u7684\u57f7\u884c\u8a08\u756b\u4e2d\u4f86\u770b\u5c31\u53ef\u4ee5\u770b\u51fa\u4f7f\u7528ROW_NUMBER()\u7684\u57f7\u884c\u6210\u672c\u662f\u8f03\u4f4e\u7684\uff0c\u4e3b\u8981\u5dee\u7570\u61c9\u5728\u65bcROW_NUMBER()\u53ea\u505a\u4e86\u4e00\u6b21\u7684Clustered Index Scan\uff0c\u800c\u53e6\u4e00\u500b\u65b9\u6cd5\u5247\u662f\u5169\u6b21\uff1a<\/p>\n

\"image\"<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

\u4f86\u6e90\u00a0http:\/\/www.dotblogs.com.tw\/jimmyyu\/archive\/2009\/11\/0 … \u95b1\u8b80\u5168\u6587\u3008[\u8f49][SQL Server]\u5e7e\u7a2e\u5206\u9801(Paging)\u5beb\u6cd5\u3009<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=\/wp\/v2\/posts\/2503"}],"collection":[{"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2503"}],"version-history":[{"count":1,"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=\/wp\/v2\/posts\/2503\/revisions"}],"predecessor-version":[{"id":2504,"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=\/wp\/v2\/posts\/2503\/revisions\/2504"}],"wp:attachment":[{"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2503"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2503"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kuki.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2503"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}