{"id":222,"date":"2018-07-20T08:39:00","date_gmt":"2018-07-20T00:39:00","guid":{"rendered":"https:\/\/yeslq.com\/201807222.html"},"modified":"2019-05-07T10:39:13","modified_gmt":"2019-05-07T02:39:13","slug":"microsoft-sql-server_20","status":"publish","type":"post","link":"https:\/\/yeslq.com\/?p=222","title":{"rendered":"Microsoft SQL Server-\u6392\u5e8f\u6dfb\u52a0\u5e8f\u53f7"},"content":{"rendered":"

\n\u7ecf\u5e38\u4f1a\u9047\u5230\u5206\u7ec4\u83b7\u53d6Top n\u5185\u5bb9\u7684\u6570\u636e\uff0c\u6216\u8005\u5206\u7ec4\u6392\u5e8f
\n\u8fd9\u5c31\u9700\u8981\u7528\u5230ROW_NUMBER()\u8fd9\u4e2a\u51fd\u6570<\/p>\n

ROW_NUMBER ( ) 
\n    OVER ( [ PARTITION BY \u5b57\u6bb51 , \u5b57\u6bb52… [ \u5b57\u6bb5n ] ] ORDER BY \u5b57\u6bb5x) <\/p>\n

\u793a\u4f8b\u5982\u4e0b<\/p>\n

CREATE TABLE #test (id INT,texttt NVARCHAR(50))
\nINSERT INTO #test ( id, texttt)
\nVALUES
\n(1,N’\u4e00’)
\n,(1,N’\u4e8c’)
\n,(1,N’\u4e09’)
\n,(1,N’\u56db’)
\n,(1,N’\u4e00\u4e8c’)
\n,(1,N’\u4e09\u56db’)
\n,(2,N’\u4e00’)
\n,(2,N’\u4e8c’)
\n,(2,N’\u4e09’)
\n,(2,N’\u56db’)
\n,(2,N’\u4e00\u4e8c’)
\n,(2,N’\u4e09\u56db’)
\n,(3,N’\u4e00’)
\n,(3,N’\u4e8c’)
\n,(3,N’\u4e09’)
\n,(3,N’\u56db’)
\n,(3,N’\u4e00\u4e8c’)
\n,(3,N’\u4e09\u56db’)
\n,(4,N’\u4e00’)
\n,(4,N’\u4e8c’)
\n,(4,N’\u4e09’)
\n,(4,N’\u56db’)
\n,(4,N’\u4e00\u4e8c’)
\n,(4,N’\u4e09\u56db’)
\n,(5,N’\u4e00’)
\n,(5,N’\u4e8c’)
\n,(5,N’\u4e09’)
\n,(5,N’\u56db’)
\n,(5,N’\u4e00\u4e8c’)
\n,(5,N’\u4e09\u56db’)<\/p>\n

SELECT * FROM #test<\/p>\n

SELECT id,texttt
\n,ROW_NUMBER()
\n         OVER (PARTITION BY id ORDER BY texttt ASC\/*\u964d\u5e8f\u6392\u5217DESC*\/) num
\nFROM #test<\/p>\n

\u4ee5\u6bcf\u4e2aid\u5206\u7ec4\uff0c\u6309texttt \u6392\u5347\u5e8f\u8fdb\u884c\u7f16\u53f7
\nPARTITION BY\u53ef\u4ee5\u591a\u4e2a\u5b57\u6bb5\u5206\u7ec4\uff0cORDER BY\u540c\u7406<\/p>\n

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

\u7ecf\u5e38\u4f1a\u9047\u5230\u5206\u7ec4\u83b7\u53d6Top n\u5185\u5bb9\u7684\u6570\u636e\uff0c\u6216\u8005\u5206\u7ec4\u6392\u5e8f \u8fd9\u5c31\u9700\u8981\u7528\u5230ROW_NUMBER()\u8fd9\u4e2a\u51fd\u6570 ROW_N … \u9605\u8bfb\u66f4\u591a<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[54,68,69],"class_list":["post-222","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-microsoft","tag-server","tag-sql"],"_links":{"self":[{"href":"https:\/\/yeslq.com\/index.php?rest_route=\/wp\/v2\/posts\/222","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/yeslq.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/yeslq.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/yeslq.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/yeslq.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=222"}],"version-history":[{"count":1,"href":"https:\/\/yeslq.com\/index.php?rest_route=\/wp\/v2\/posts\/222\/revisions"}],"predecessor-version":[{"id":276,"href":"https:\/\/yeslq.com\/index.php?rest_route=\/wp\/v2\/posts\/222\/revisions\/276"}],"wp:attachment":[{"href":"https:\/\/yeslq.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=222"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yeslq.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=222"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yeslq.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=222"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}