\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<\/p>\n
ROW_NUMBER ( )
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))
INSERT INTO #test ( id, texttt)
VALUES
(1,N’\u4e00’)
,(1,N’\u4e8c’)
,(1,N’\u4e09’)
,(1,N’\u56db’)
,(1,N’\u4e00\u4e8c’)
,(1,N’\u4e09\u56db’)
,(2,N’\u4e00’)
,(2,N’\u4e8c’)
,(2,N’\u4e09’)
,(2,N’\u56db’)
,(2,N’\u4e00\u4e8c’)
,(2,N’\u4e09\u56db’)
,(3,N’\u4e00’)
,(3,N’\u4e8c’)
,(3,N’\u4e09’)
,(3,N’\u56db’)
,(3,N’\u4e00\u4e8c’)
,(3,N’\u4e09\u56db’)
,(4,N’\u4e00’)
,(4,N’\u4e8c’)
,(4,N’\u4e09’)
,(4,N’\u56db’)
,(4,N’\u4e00\u4e8c’)
,(4,N’\u4e09\u56db’)
,(5,N’\u4e00’)
,(5,N’\u4e8c’)
,(5,N’\u4e09’)
,(5,N’\u56db’)
,(5,N’\u4e00\u4e8c’)
,(5,N’\u4e09\u56db’)<\/p>\n
SELECT * FROM #test<\/p>\n
SELECT id,texttt
,ROW_NUMBER()
OVER (PARTITION BY id ORDER BY texttt ASC\/*\u964d\u5e8f\u6392\u5217DESC*\/) num
FROM #test<\/p>\n
\u4ee5\u6bcf\u4e2aid\u5206\u7ec4\uff0c\u6309texttt \u6392\u5347\u5e8f\u8fdb\u884c\u7f16\u53f7
PARTITION 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_NU … \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":[7,25,24],"tags":[],"class_list":["post-74","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-server","category-sql"],"_links":{"self":[{"href":"https:\/\/yeslq.com\/index.php?rest_route=\/wp\/v2\/posts\/74","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=74"}],"version-history":[{"count":0,"href":"https:\/\/yeslq.com\/index.php?rest_route=\/wp\/v2\/posts\/74\/revisions"}],"wp:attachment":[{"href":"https:\/\/yeslq.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=74"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yeslq.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=74"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yeslq.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=74"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}