知方可补不足~SQL2005使用ROW_NUMBER() OVER()进行数据分页

回到目录

数据分页是这个经常说的东西,无论在WEBForm还是WinForm中它都会被单独拿出来,或者是公用组件,或者是公用类库,反正对于数据分页这个东西,总是我们关注的一个话题,但事实上,数据分页归根结底是数据库的东西,更直接的说,它是数据表的范畴,对于一个SQL请求来说,你要取数据,要取多少条,从哪条开始取,这事实上就是分页实现的原理。

SQL更新到2005版之后,为开发者提供了不少函数,ROW_NUMBER() OVER ()就是其中之一,它可以为你的结果集生成一个行号,并可以快速的主位到第几条数据。

MSDN上对它的应用

USE AdventureWorks2012;
GO
SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,
FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

结果会把集合加上行号,如下:

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAZQAAAEZCAIAAAA/iUTxAAAgAElEQVR4nO2d3YGDIBCEU5cFUY/V2IzF5B5MdGFn+VFzsjrf052i4gIjoExeb0IIccjr6gwQQsgeKF6EEJdQvAghLqF4EUJc0qt4zePweoXp6mwQch1TeC2wIUDqxWuN5Ov1Gsb5nMvLk0ZldEi85nGIj52CyLTa2xPzOBwLruN7fwrzOKAqn0ndXmTzOEStdPl/ElfeWnLUCD3Vjibx+kQjDcwBpvCLaKEGPAyDyH63RfQT8XJy74+gufHsLTJ5ofSicbPbWrazAc8e8YriKR4jYXobjccsLChesLc8hVcY12uBqy8XmdNnyzDOnzxM45DkUD5yli3zOAzjuPRVxiCulN7mr4DipfN5y3t/BIZ44SL+HhBtgcWhKoM8UjVBU7xO7Zn8nD3iNQVR35W6f9N9ApR/cEQ91ihm6XFT2BKssTc6boaAzksLRjn6bvs+eT5X+0qJvE5WjA9T6HmJvN/v3h/CR2esQKZFlPyPi8OoDFN4LY/8ZGdGvDyV8a45L2MGZQnJp9JPYRiG7V/7pIayAfHSKT/1wGqO8uBPQ1xapRCA+IH33fc54JP5tEvz7+KFZiXud++PIpUwc+IpLlCjOIzK8Dmt2vxE8RrG+ROnbZyiHwpTeIVpHsMyVsnPah0Ur22f6kPjoet38Jn2pmXvQzfgfyxPJF7GgH3deZd7fx7rGCZTxPH/+eJIK8PbnpjJDRu9TA7snbDfJrjUpOA8DkMIYZzf8xhCyA6DzhEvlTwt4ijvw7A1VjEQzjRgMVD+NUC8UD5Vgjvc++OQTcYsYt1DaGgIreKFK1iv7JqwlxNQW3cX7C2+vEChTXvG9vRWlNKY1NwmrWX/PJm0HsYx5BpwfKlf9kSSm7fyect7fwTGx0HFIn6l8wWyOOzKUClePsu3149UCSEkC8WLEOISihchxCUUL0KISyhehBCXULwIIS6heBFCXELxIoS4hOJFCHEJxYsQ4hKKFyHEJRQvQohLKF6EEJe0iBewI2zZ+ItzetlYT4eZ58bdGyEd5tPnRva8CCEuoXgRQlxC8SKEuITiRQhxCcWLEOISihchxCUUL0KISyhehBCXULwIIS6heBFCXELxIoS4hOJFCHFJr+I1j0P6w+XF9A3J/dEakA34g+9m0gV5ANxIyOU0idc8DidX4nkc8BL8Z4rXFLYl9EkEQEAq77lBvOzT3iO+HbG0paR8Yw+FbdMLJYUPmNTSYt2xbhZJsyVqXR1l/iLqxWsKr2GcT67EpnjtONH1wTzKPA7D8AnIPIYQ8sGheLllCq8hhC2k8zh8/pzH4Ss0MOaFZigOj/4Wh6/HLaeysmiXeJr5y2gdNv6HeKFxyhReYfw+C7ZDosfDJ3nto6U/5nEYxmkM4/yex3Fag6MDEj8WLcujJfkUXmH6boWhq+jQUbxOJCtAU/gWSCbmWfHa2oFuW2ufCSlb1SV+0onZR4/ihS40BdkY1V/3aHHL83cewziGpX6J4OibAbcnIhJtQ6GznsC3CGXHfMONQyq2yidU0kr0serp9HkSque9OG+2QPHV85n/ZxyJl+hiL3/LRieTy67X9RFu4HOT33tpFy/4sEWhS3tuFK//YisNFFL08HmDCc/smE4U8bdc14qxnWkKWhMx0TG5zsJ/cz/xElu7iHAD6exU9P+p4pWb7qB4/Q413H+Zb2jUccUHzILoG4kjPpUgEkdDKc2r5zJ/CZ7FaytR8fZDFLMYL/mgUby0BMH6j0KXq7gUr/8hCmn+7bp8C62PTZPq+f61UWTm8+3Woq6ezcA/0vS2MeKUrCdiDoc0SYcVzgqEaUsg3g6PjS/arkVVCDGMxJEvvF9X01vyCmBOA17Ivjo5hDG7BSMP38dErUOnxO9kdP2Q28UWeHWU+evo9SNVQgjJQvEihLiE4kUIcQnFixDiEooXIcQlFC9CiEsoXoQQl1C8CCEuoXgRQlxC8XJKF584E3IhLeIFFqMcRS52uHSNZyfUG2JQvHxjLcfRa+9BTajxR03XEaXrwsqVB7b3H4jAburFa0Zmj6eRd3V8DMk6xF1Gl6R/UjNS6PCHbf9QvSj6o64b660EYXv/rQi0smvYWO2l0XTKgss2WmV6O2QtjC0g0PNz+Vs9w0nnWApS4+exxx9VnbfxyQfb+w9EoJU94vWLhz7ueUXtN/9suQfiNpERSSJZYcKpSM/YZqQV4rXHH1W3l7YW3K1HUrt4/UZxowDrmZ+c8+edMOYAwVTYNyJXVyDSRs6MtE68Gv1R0UlblKfXbte7VbzylmlHEOKFrFBv3NmKgL/0A71hl7/6qESklrwZaW3Pq8EftXIcmcsuUr4+Kl2DeP0g0/M4bgaCeiYQ/nTErUHihQPyrX/9VCXSxr45rzZ/1IwnPnyDqewJ+1Wud4N4YbPHw+if5LKsUIHz5/2APS8YEFH/6n9KgXSEVJCSP6pscfX+qKDbZfohp699YHv/kQjshR+pEkJcQvEihLiE4kUIcQnFixDiEooXIcQlFC9CiEsoXoQQl1C8CCEu6VW8qr7kxZ901rN+sdfLJ8MGx/J5NEqE9EmDeImPc0/6nFtaIiRq9S/itV6pm8adMSOszKdORvHqC9COTONA4DtoNENlMWidExtOQarPeRF7LXHOkK95HIZh+K6LDyEMjWe9pXjJFSPpUjOK150w2tG2Dj/f0OI1r3mLQWlGWGktVX/Oy7jSz+vjTRTG+T2P47QumLdGSUL3hcmXWh3Z6GXYsXjZ1iZxtf78p1aeifqq15A+wtyxa0rrsAvqUG8xmCzmztgW1uUxu+MfaRIvtIr6AIun7DyGcQxLOBOvbBkctD6+5DpR5WXYQymsZDz9bUdNJWuq56Wj9BBzxz7JtKO4WLHvoHW4VZPRY7D2oVV7zku40gb604CEuYstXpYzt/ScgEv0i16GvYmXvKNcPqUTXUb0cZSeYu7YN6odqccQMrkzD8c1GZgZbp4VxXKvOedl7HvbeI7uppMx0f+7xavVy7CTglhIPOyzIvvZn0SxTry6eHCSuBiSxxX0HcwcbnqmJp022eMu1vyKc17H3p7XGZlvES/49tHqUzR5GfYqXmm+QD6nEDmeraeIC8eS+H7u+qnERZXrh0G9yM6K4nPatoXajLD2nNdRL16nfymh4iKGkRLZ8uJtuKlXexlaF7oQOOdl5xNWuC25esEkQ/4Ic8cOyXzqAH9vJSl0eHjGthBNjKEKn5oRtpzzKnr9SJXUwNEfeTAUL7/0038n5AIoXi5Z+vTsdZEnQ/EihLiE4kUIcQnFixDiEooXIcQlFC9CiEsoXoQQl/QqXmo5EPTJMS1Gq7wMCSGOaRYvtYzgEKYFDFafkgNS4XAfnO9YS3qi1go1Thqvp848stWK4JpzNuQTmLteRaN4TeE1hPCDlcx1C12qxcsrnkWXNFKyQkWea4n1k2wzadusPmdDPrta2thsRph3ht2PDKQ5GKwTr/wA8xWtcQ0jMHu7DiO01gN4yz1abp01Te3v3p9HyQoVuZ5GzitJo4Fts+KcDfnsazFt2w9wKPE/DRWV+k5WY3cstoXpyiGmXKES8wxhVad8NtBJt7+7u/cn0WCFmrqeisKSZoV226w4Z30+TXPXa6gWr61Z+BQvba+aM8+6BlO8QOahOx2Y5DCGDd3d+xMpWKFi19N5047x02hybbPqnLX5LJq7/i+V4pVaSp0uvD8WL2Sv2mEDxp0gmHksXuI8or5RvDola4VadD39JM+3zcZz5vNZNnf9V3Z8KnFiZZ/HUfWGc1fZu9G0V+2tAaMJe5z5UtVB1rLxsLG3e38eeSvUwjy56V6Ya0fNTqpJPkvmrv/LteIFh//QOPTgRmiv2mcDBl+PQG9YJF7RvcfP2yTMfd77E2iwQkWup9s2VGbJy8jKc27b4z66zid8+XMZvX6kSgghWShehBCXULwIIS6heBFCXELxIoS4hOJFCHEJxYsQ4hKKFyHEJbcQL/1d+kOtZf7te9NdK0MKuTsj83UeluQe1ItX/OX6OXXB9CJso0q8+v+QHC0mb4vKr+5RfW19uXgZifFDq6+ibzAj3NpcKeU7WocxN6eszifO0jW0idfZNSBeNPXbaPRVgyH2avJ9JzgtU6j6dyleJyT+P+JFq9qNax4HvY4Q+3ah5lOfsj6fMEuX0Yt4pU5VaQ8PmufhpXx6pKDW3V8ddYsoxltwqs0It+OjpWpqPVpc87L1EBf7FF5hStelGuslQSnBlDjz+t6t0mz0sLwelS07nwXznMxj7vADMVMFrg7qvmHjWY0/bp86FJF9TcY8r2hjBLd0SCzhOsyJxKey8dktdxnOv/ixgTMEShsWB5SkxD2vLF5x5vG925mu9x25kAYzQmOH8oowPQLrU9bnM5vXf2TPhP15s+HG8L/ZOPA24rXlMtKuWjPC7wNG2wWgfsry1xx5NKH8YPGCOqU2ytuoEC/QcUL37ly8vhT6U1YylXIebY/A+pT1+eyi2/Xe+bbxtPGudK9FMS1U9yhD9xAv0f9I7Mnl3s9Wo5XGNcuqoh/RKs1emf6IPxGvNPNmD/EW4pUXmnVTdkjy+c/0CKxPWZvP8/ouR9kjXmbHfs+ZpLntOvRpNQ6skqo9w/0LmMfhFULS5ivNCL93HdUv8yk5BekPlsuPSmOJ19qZe6nSTCay9EaUeePe32ZpuhKvvBlhTiaSlOLftGNRnbLKjLAj5XrvnPM6TQOS/oX8cYjlOpXGgeb8cFQe256uRSyaa3+/341mhGJKTNmeo+F5RU3UX7Tg4livI/O5bgzTpDeKlDjz8N7jm5I6mJS7XRkuo9qMUL2Y+HZl0R2JxPKhVZdy2xz3eVU+cZYu4xYfqXb7aO0cJ31RQiC3EK8+pg+9wagR33gWL6MDTIosQwL2uohrPIsXIeTBULwIIS6heBFCXELxIoS4hOJFCHEJxYsQ4pJ7iJf8nJwgulrWQcgZNIpXnQfjjvMdOOV5Sy17QK7AOFFsKF79YC1T043LXtAWr+aJl+3oVb47Fsmli4vSa/VQlxrE6zQvCQRXqrzfPw4x6YIZmpGikodWaN89QwgDWl+akKa0z5kjsYHrZTVevXj9Vl606Ub6FIELw2/2kb3tnqUfq8hJ1UyZbNGGAskq6JvEs3MiH0dV7qmXgxSgREEsNVEpzXPmUGd3J15zkwdjM7E5DngeIRMF6BHqm48aV5j6IidVnBL9n/h5uHGRuRORlZlqXNISbbHUiD1wLSeVyKoiSWmdM4tuWb1Ujhbx2uHBWE3k8BQ3si3uL+Ti8QLF5p1UwmqdVC3f0beucDjg5uHkbOS6eNS4Em/Gz9bYVQi7FK4HqJT4nFnQZTyK1x4PxlqK4rX9/8r1tW8EqmNxzwt1RS1netT3/7SRgmUrOZ309QlsXNEr9KU8lJ+WfmR/BitGSnDOYkZ1G+ulcuz69aDT5pXncdzGQJXTmLF1ZxdB/AnrvRtuosZQEfuOogr3HauULFvJuaAXv7BxiWcJqOlYQdCrd5nSPicqcqOB+ROv38yOf8cpUcC3wQv2cISz+HfohcXvp/VgruSkClPabqKqvpq2peQsLDPSwkuqfPcn73Ica41xTjB/D7oOhjvrRdzjI1VCyOOgeBFCXELxIoS4hOJFCHEJxYsQ4hKKFyHEJRQvQohLKF6EEJd0KF6nLz0ihNyQavEyzcmOAbz3nixeSZQjD5vHBuVuHLQYBL5FOKVIiFeqlKsUMB/1aUYoOGlJNF4j+WTxWtARoHjdBmxG+H5XWgxCG6hC9RAXaqhIhj+iSzPCjdPynhGvdM2jKLNeQvc7IvFSi+FiN7kOlpiRnUTrnistBtfOWWpgUBCvrJGOkbkKV7lL2SFeZzrRIO89sVw4spp8qHi932/DzDLrvUG6B7k9lC0G5Y5SSm17UO1kkDMf7aUFNovXLzIeSxjUKYpXzuCGI21/yG5XvcVg7DZozJqhsSb0T8rUmpz5aC8tsFG8fviQl957FC+K152JhaPeYlCPNHOebgI0Wsq35Zz5aC8tsE28fuj+F3mwQfHa5ibvPskDtCitfRw2uiXb5SlZDJbKHZkRmr/voYeYWKHSC3kUr9MbCfbew52s9fF0a58861MJ9H4dWDaS7rHMCMXuvMUg+LgIp0RfSti2hYYZoW6bmcz/Nx1+pEoIIWUoXoQQl1C8CCEuoXgRQlxC8SKEuITiRQhxCcWLEOISihchxCUUL0KIS/yLV2GB6a158r2Tx9MiXsDC8RB6QeqedS63asAyxBXBAPfey7ozYoKcVOFaHmRbalmhIoPTas9Vk7s4qUILx1OgJ8LKFti9C0kpXp1jO6mmG6FtqWWvqlPu8Vw1cpK/0GXUixe0cDwFJV5q9WhyybUo1gdDD6E8A/FUMJboaitz21ol57k6hVcY1+13iZ8vLEsbtTy6zo4GpGz1XI0zdy8nVWXheAqpeKGemAxlsr+XUJ7BdpuiYmf6vPre9RZ8uHREYdf3EuKi0q6nKFXOChU0hGrPVZS5GzmpFiwcD5C2HTiqtju+vYTyDMo/+KLnRAriZRxOwboYyxsv3W5WbzXhiWSu1XM13n0XJ9WiheN+jHa0NGQ5KBrGOXF4/OzoIpRnIK3M46kPnL5CvIzDKV4XkhWOaqXIewTu9FwVe+/jpPpD6067HUVR+nZkC/1q18QT9puLbOU0R3yKbQM257xL0JxR6PIgz1yYWpmmxilbPVfv7aSKLBxPAcxh4Quh+Ep6COgxkqm9SMniYZ997/pNPJrooHhdA3ZShV9KQNvSkmmqqAzVnqvbDjqpEkLIz6F4EUJcQvEihLiE4kUIcQnFixDiEooXIcQlFC9CiEsoXoQQl7gWr4YPLW/nP1G6o16+gibkV+wzIzyrVTR674HDm3LSeYOuXZaYO6RiD7maSuNAtFgi/cRdmwzqtWFqM9yYYF3IpRmhbAyneREe9N67mXil+asIM8XLHU3GgTnbwsTXTiStNDtszbOV+ctocZWIVqyfol7Ye8+ymxbb1xXLYdI+SMh7L71Gn0QZjL23Ku8ILgwFAUmW2HUdlTtSYxwoyNsWJs1oWy+pz7Sn5XZrQrXHEiey+jkE9N4TJIqWJhALtbfdTdZ9vRHfsI5xegf6Aa6fwAX/7imcaotLqqgxDsymTkYt6/LuYRyXHdBNMGcxaKHrTC/taI+rxBqfwxg/OxCNt9XDJTpc9iTSVffqvL0EPYOsiIZrgC1e8piqgIiRCflXWowDS27Ry99rqmkVL+0mmLMYrMqoue0Kdr1tPC3zpZjCnnF0uBKvNuu+/vjkMfHGsSzIK8QrExDa4lxGrXEgNv9KBpeR4H3PDN0EcxaDVjbL/f+r2CFeJ1oRIu89cXoxLISFiMSr0bqvP+ZxeIWQyJAOiEgM/OekO5PtN8zx4mVUGQdatoWqRMUDatsHp+7t+XxVt9CFUOavo/XXg86d3cXee+JN7hiSh1A6ltTi1Wjd1x/aFg4FBN+RmDdJgpMPCGXs36g2DsS2hZmXiKoo4Zsr43WWYUaYXIhmhIQQchiKFyHEJRQvQohLKF6EEJdQvAghLqF4EUJcQvEihLiE4kUIcQnFixDiEooXIcQlTeKFTBQtC8f609WuUullRdUP2ZZI2Qvb/iMPXC70U3Q7ipvCtibVWIuTWqGiw3HKpgabd3y9vi3Wi5flAAksHBupNDe4v3jN4zAMw3cFbQhh+H8RmcIwTuMFF34KsB0V63becxgfbqTMLZEs5FP+d5qd8n5ah42mBwtY2FmLEi+1dlT10eKrdvIgOMzHKi6M83sex2nzLwG3OYVXGLWrnEgZLXmvjdJiNSGdU+CF8NXRhYx8Pp028Wr1qLRTxvvLjUa1d2zAdA2HxAtbODaTiJfl/KlLqOAR6o7lsTiPYRxDmFYJgbcJXWRLndiKB/zqnBL95EN6IXx1dKFSyoeCzAjN0VzcRwBWqOjwkmlq3UDG9B2LLPgu4gTxSi0cm4lbnOn8qaKd9Qj1yOYYJ53j8G1iOyA0S2EYsQIX2/hEm4cYNE1DZkTYAhelfDpmLPRUp2pyOSvU2BfPTln7IEHul586c5ad8n4ODxth1W8jFi9TAtX579DZikj7Tcv/+DazorCIiB5o5IuoQSWtjdACl+KlsWORzEXpf/NWqGt6O2XLq6B8Pi8uzWPihS0cW9HDRnyi3Y8PL2DxwrdZEoV1o6j9yCwTHLH+mw5a85KEL0TxgpixkC+c36DsbSvU9HCc0lQuXDmsfJam+/+FpreNEfFA5cioLTdhr35AozzYdwvqWuoXFrnRXNR3kvNkoqtvyQe6+qfjVztsRBeieCXAdoTes7yLpqlRZLPtRbfWtJKoF26FfPZQkPxIlRDiEooXIcQlFC9CiEsoXoQQl1C8CCEuoXgRQlxC8SKEuITiRQhxiX/xyix24FeR73e149Anqf4EEW4k5HIOmxEaa4GrgAuGW7mdeIEV00fP1xSE/GojchzdZOIv34Ufh/4aHlcPvNAFtc36JTHId/DsynmI42aEemPjKd/vd9ti0Wr8tbhfhIHi1RVHzQijY8RaUrEASK6T1xfKrIxMzi7MQuAipavl6+DC7MzGCqKgbNHQnnZJpGJTq8yzxdtYxwgkDMgwjounx7hZSJjGgdP3FNC2UHelKV4/pk28CkWS8wS1z5y3NSj5DvZQIfoRr3yE5M6kK6E7g/op5IPy00xYfX1XTX8er18HCK331ka5cDsda1C8fgwQL3s4lhRQ+iBaz4U8QbPGYZkSNX0H9WPwMroULzQXtu5VF1MWYxnv1b4xxUsF5Htnn7sF9jVRlVYbsXVXcmCaNU+h7J2spuTqt+CrL0sCwxM005kvlOdaR7DvYAd+VP2Il2xsyDxvXn1FkzjeR7yMCgECclS8cj6OFK9/wI5n8gTL9ca/pZjzBAUXap5YxZm93gu0F/HaxjbJPGTU8OAP28RX3w5Xw38HoHqFAlIWr00HrY1WaChe/0C2O5UzI4yTbvXC8gRNL2Qql+lUacjn9dp11IzQcCjcccqkwLb+ajKdZb9gVj81FKbWF209AIKiA2KLlw6oEWUwzQLjiYNM9nLQjND4VgF8AIEulBZm3Cc3GlfaAvXVL8L/R6pkw6NYE7ITitedoHiRB0HxuhMUL/IgKF6EEJdQvAghLqF4EUJcQvEihLiE4kUIcQnFixDiEooXIcQlh51U640Zs+f8/dKT/pfsqcx0sHyM/ADUjsQOYUUAFvKAFTqG5yq+EPJHLeYVn+HqynnQSbXamBHTvL79AP2LF3Amu7p6kNOxzYen8BpCiHxUcpWztGAaXUj+V1O90ix1YaC6coqrxPv93uXvYxyijUPfiUmoMPnSG7VHqFqO+lnMrDdeTBRd7G+WD0jyELacab/X6ufOn4ZuR1prjtmrwg0lf9QYnaW+nqenideOPsx6iPCBME8qrULWIkAbLY9QBz2v9IZ1PSkEJLZGKR3e040/jbTifYsrFS/0fMl4mepSV+L1/S/2RwWALH1dqTp55p0kXrtsFc0ereGkCv3QkN7B/pQL8YKWjPUBMcQLHW7Ou5D/wOoQ4QqJp1dUo0MHQz+vpWEgf1R5cp2lSPCu74WdIF77J65anFRrxcsMqQ/x+uYo0Z6GgCjVNpxp5QF9heAJWD0sYySPp5q04XO2s129x8zSHFmBXt55Pypex6bc5dDne2bDSbVWvOxOIJS16x8fKfM4vEJIxrt1AZm1zWzGmTa6INXrf7GCbjouF+uuUe9zF4pOmXVSBdNwHUzdH3NStY0Zd53WNA59N4iXPVcAv+o4+qnH+eiX000BgQNMdfi/faFCEvLmw0aPDM4g1HuubheC/qjbxcp6qt6GXQg/Ur0R8cO3v04lIWdC8boRkXjteodCiB8oXnciGgyy20XuDcWLEOISihchxCUUL0KISyhehBCXULwIIS6heBFCXELxuh/pooH1U2u4iJ3fghGnHHVSNVYrNJyRnyOt6OWwe4KDv6y3HAcoXv9PpZOqtXYt748KjlfWE3WVC3qu9rSc7qCT6sa+dZoUL8Sh5fpGCXH5dSc0OanqRdDwcOhhZ6XMu+98wRaEMEuXcaUZ4RuJVxyU9b/IN1R68KmNN0CJF1yiWzaMTe0/YJfZ6Efjtbg3CvK11Dipxjtzpbn22LSkKOMw6eKS6XcXPVc7WH52XLwyvo5Vp0NR0f5TwrBjixrceANS8TI8F2WUgC+aPqDBDYeeq7+lxknVTm3a6unRaJRSeuYV3AhLnqs99OQv9bA3ho2reom9ogVFigaMhvyTyoWeusgMGw6JFz1X/wnVIcqM5UDLQj2vVZXUBFlSN9bz5X1HVjUEKtdHX+E88dplwYLnvD5bk5H8c8Vr2/wVkd+IFz1X/w3Dt2slGtyVumI5NxGdMlPGFZnt6TXPqT2v8ybspyB9994Urw/rbf5i2BhPC9Nz9ZdYoVQdpZoZZrsy5J5PicplnVQjW99u6sAxJ9WjX0qkD53kEYH7Fo8TryhISYh06OH8iAoy3EjP1X+g2kkVv3/Bh8u04h0hupDxrYOav9/SwVc3PdSHXj9SpQ0oISRLn+LVx3wgIaRjuhOvpbPLXhchJE934kUIITVQvAghLqF4EUJcQvEihLiE4kUIcckNxcv03iM/xo58Ty5Q5C4cNSOUO9rFAq0DPgfPX9yrxbUXUmFwkWA5VfVxP10A2hFaqhJ/zx4vGiqnTD+wf4XJ2Hg0n5dxhhlhaqJWT7K+58RwOBavKQzj1ItJYyo7u8TLcWGcT72pp7UcVazzyaXUF63ZuCOfl3GKn1fuJrNgNwRtiTePwzCOS4dklO4GypDPyie22euRKQzjHK1YNzwXlUegFSV077U+junK+QksecxE3lwMZx7+ELKikLHHkWzrUAopG73cWvN5GUfFK2+iVkKI12KPljSi71m/a9k/ib4tCrrf4nzad9AZi3alXmbAcwIWF3QAAAGNSURBVFH3gYwoJSmEoKlzatJQr9Jaa2MAN2UK7iFYlkPG06nwqwT5lDu6XU35vIxj4pU3USsj+wQiGGoq7Hv2T8Q/LSp9rGeb0O+m184krpCbPw0wz9ATEjhKb3jvhiGHntJYEk4hhBCmzzm1zQrIBb4rmfVcy7w7WWsptQcY0RiPHJ3yQLerOZ//zBHxUmOC5noIZ1CAJR5ulrmHh21j1HPPC7fqrPMPcChMxAvee7Wb0BReYZrHMM5TCNM54vXMzlZEJugoOnH3NmeqlXSE4dRUw3xVYz7/l7PMCI/PeckzpZZ4drPMleG2C52zQ5IggtFx9jFq90/1vbeI1xBCWPIRxmW6+IRhY69l8E9kezTwxy4K3qog5dsINI5+q//k9dp11Ixw5TzxQpZ45oAIDPbT3ksyaZ04tPaE1cah0ES3GU/bplFC914tXvM4iBcCLznllna2ceTxFQrzOTem3tRTvZF56xhvfW4QzJZuV1S4bfm8jBt+pEoIeQIUL0KISyhehBCXULwIIS75AwRETZp+mWB9AAAAAElFTkSuQmCC" alt="" />

在项目中,分页功能的应用,代码如下:

SELECT  *FROM (SELECT ROW_NUMBER() OVER ( ORDER BY a.orderid ) AS row ,*
FROM dbo.Order_Info_View AS a
where CONVERT(CHAR(7),a.buytime,20)='2010-04'and a.agentid=3455) AS b
WHERE b.row BETWEEN 1 AND 20

事实上,把上面的代码改一下,就可以方例的实现对某页数据的统计了,呵呵

SELECT  sum(totalfee)
FROM (SELECT ROW_NUMBER() OVER ( ORDER BY a.orderid ) AS row ,*
FROM dbo.Order_Info_View AS a
where CONVERT(CHAR(7),a.buytime,20)='2010-04'and a.agentid=3455) AS b
WHERE b.row BETWEEN 1 AND 20

回到目录

上一篇:SQLserver中用convert函数转换日期格式


下一篇:【转载】Sqlserver使用Convert函数进行数据类型转换