{"id":12741,"date":"2023-09-20T09:25:01","date_gmt":"2023-09-20T01:25:01","guid":{"rendered":"https:\/\/92it.top\/?p=12741"},"modified":"2023-09-20T09:25:01","modified_gmt":"2023-09-20T01:25:01","slug":"postgresql-row_number%e7%94%a8%e6%b3%95%e5%8f%8a%e4%bb%a3%e7%a0%81%e7%a4%ba%e4%be%8b","status":"publish","type":"post","link":"https:\/\/92it.top\/?p=12741","title":{"rendered":"PostgreSQL ROW_NUMBER\u7528\u6cd5\u53ca\u4ee3\u7801\u793a\u4f8b"},"content":{"rendered":"\n<p>\u5728PostgreSQL\u4e2d\uff0cROW_NUMBER()\u51fd\u6570\u7528\u4e8e\u4e3a\u7ed3\u679c\u96c6\u4e2d\u7684\u6bcf\u4e00\u884c\u5206\u914d\u4e00\u4e2a\u552f\u4e00\u7684\u6574\u6570\u503c\u3002<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">\u7528\u6cd5\uff1a\nROW_NUMBER() OVER(\n    [PARTITION BY column_1, column_2, \u2026]\n    [ORDER BY column_3, column_4, \u2026]\n)<\/pre>\n\n\n\n<p>ROW_NUMBER()\u4e3a\u8fd4\u56de\u7684\u8bb0\u5f55\u5b9a\u4e49\u4e2a\u884c\u7f16\u53f7\uff0c PARTITION BY column_1 \u662f\u6839\u636ecolumn_1\u5206\u7ec4\uff0cORDER BY column_3[ DESC ]\u662f\u6839\u636ecolumn_3\u8fdb\u884c\u6392\u5e8f\u3002<\/p>\n\n\n\n<p><strong>\u4e3e\u4f8b\uff1a<\/strong><\/p>\n\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">postgres=# create table student(id serial,name character varying,course character varying,score integer);\nCREATE TABLE\npostgres=# \npostgres=# \\d student\n                              Table \"public.student\"\n Column |       Type        |                      Modifiers                       \n--------+-------------------+----------------------------------------------\n id     | integer           | not null default nextval('student_id_seq'::regclass)\n name   | character varying | \n course | character varying | \n score  | integer           | \n<\/pre>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\"> insert into student (name,course,score) values('\u5468\u6da6\u53d1','\u8bed\u6587',89);\n\n insert into student (name,course,score) values('\u5468\u6da6\u53d1','\u6570\u5b66',99);\n\n insert into student (name,course,score) values('\u5468\u6da6\u53d1','\u5916\u8bed',67);\n\n insert into student (name,course,score) values('\u5468\u6da6\u53d1','\u7269\u7406',77);\n\n insert into student (name,course,score) values('\u5468\u6da6\u53d1','\u5316\u5b66',87);\n\n insert into student (name,course,score) values('\u5468\u661f\u9a70','\u8bed\u6587',91);\n\n insert into student (name,course,score) values('\u5468\u661f\u9a70','\u6570\u5b66',81);\n\n insert into student (name,course,score) values('\u5468\u661f\u9a70','\u5916\u8bed',88);\n\n insert into student (name,course,score) values('\u5468\u661f\u9a70','\u7269\u7406',68);\n\n insert into student (name,course,score) values('\u5468\u661f\u9a70','\u5316\u5b66',83);\n\n insert into student (name,course,score) values('\u9ece\u660e','\u8bed\u6587',85);\n\n insert into student (name,course,score) values('\u9ece\u660e','\u6570\u5b66',65);\n                                                                \n insert into student (name,course,score) values('\u9ece\u660e','\u5916\u8bed',95);\n\n insert into student (name,course,score) values('\u9ece\u660e','\u7269\u7406',90);\n\n insert into student (name,course,score) values('\u9ece\u660e','\u5316\u5b66',78);\n\n<\/pre>\n\n\n\n<ul><li><strong>\u6839\u636e\u5206\u6570\u6392\u5e8f<\/strong><\/li><\/ul>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">postgres=# select *,row_number() over(order by score desc)rn from student;\n id |  name  | course | score | rn \n----+--------+--------+-------+----\n  2 | \u5468\u6da6\u53d1 | \u6570\u5b66   |    99 |  1\n 13 | \u9ece\u660e   | \u5916\u8bed   |    95 |  2\n  6 | \u5468\u661f\u9a70 | \u8bed\u6587   |    91 |  3\n 14 | \u9ece\u660e   | \u7269\u7406   |    90 |  4\n  1 | \u5468\u6da6\u53d1 | \u8bed\u6587   |    89 |  5\n  8 | \u5468\u661f\u9a70 | \u5916\u8bed   |    88 |  6\n  5 | \u5468\u6da6\u53d1 | \u5316\u5b66   |    87 |  7\n 11 | \u9ece\u660e   | \u8bed\u6587   |    85 |  8\n 10 | \u5468\u661f\u9a70 | \u5316\u5b66   |    83 |  9\n  7 | \u5468\u661f\u9a70 | \u6570\u5b66   |    81 | 10\n 15 | \u9ece\u660e   | \u5316\u5b66   |    78 | 11\n  4 | \u5468\u6da6\u53d1 | \u7269\u7406   |    77 | 12\n  9 | \u5468\u661f\u9a70 | \u7269\u7406   |    68 | 13\n  3 | \u5468\u6da6\u53d1 | \u5916\u8bed   |    67 | 14\n 12 | \u9ece\u660e   | \u6570\u5b66   |    65 | 15\n(15 rows)\n<\/pre>\n\n\n\n<p>rn\u662f\u7ed9\u6211\u4eec\u7684\u4e00\u4e2a\u6392\u5e8f\u3002<\/p>\n\n\n\n<ul><li><strong>\u6839\u636e\u79d1\u76ee\u5206\u7ec4\uff0c\u6309\u5206\u6570\u6392\u5e8f<\/strong><\/li><\/ul>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">postgres=# select *,row_number() over(partition by course order by score desc)rn from student;\n id |  name  | course | score | rn \n----+--------+--------+-------+----\n  5 | \u5468\u6da6\u53d1 | \u5316\u5b66   |    87 |  1\n 10 | \u5468\u661f\u9a70 | \u5316\u5b66   |    83 |  2\n 15 | \u9ece\u660e   | \u5316\u5b66   |    78 |  3\n 13 | \u9ece\u660e   | \u5916\u8bed   |    95 |  1\n  8 | \u5468\u661f\u9a70 | \u5916\u8bed   |    88 |  2\n  3 | \u5468\u6da6\u53d1 | \u5916\u8bed   |    67 |  3\n  2 | \u5468\u6da6\u53d1 | \u6570\u5b66   |    99 |  1\n  7 | \u5468\u661f\u9a70 | \u6570\u5b66   |    81 |  2\n 12 | \u9ece\u660e   | \u6570\u5b66   |    65 |  3\n 14 | \u9ece\u660e   | \u7269\u7406   |    90 |  1\n  4 | \u5468\u6da6\u53d1 | \u7269\u7406   |    77 |  2\n  9 | \u5468\u661f\u9a70 | \u7269\u7406   |    68 |  3\n  6 | \u5468\u661f\u9a70 | \u8bed\u6587   |    91 |  1\n  1 | \u5468\u6da6\u53d1 | \u8bed\u6587   |    89 |  2\n 11 | \u9ece\u660e   | \u8bed\u6587   |    85 |  3\n(15 rows)\n<\/pre>\n\n\n\n<ul><li><strong>3. \u83b7\u53d6\u6bcf\u4e2a\u79d1\u76ee\u7684\u6700\u9ad8\u5206<\/strong><\/li><\/ul>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">postgres=# select * from(select *,row_number() over(partition by course order by score desc)rn from student)t where rn=1;\n id |  name  | course | score | rn \n----+--------+--------+-------+----\n  5 | \u5468\u6da6\u53d1 | \u5316\u5b66   |    87 |  1\n 13 | \u9ece\u660e   | \u5916\u8bed   |    95 |  1\n  2 | \u5468\u6da6\u53d1 | \u6570\u5b66   |    99 |  1\n 14 | \u9ece\u660e   | \u7269\u7406   |    90 |  1\n  6 | \u5468\u661f\u9a70 | \u8bed\u6587   |    91 |  1\n(5 rows)\n<\/pre>\n\n\n\n<ul><li><strong>4. \u6bcf\u4e2a\u79d1\u76ee\u7684\u6700\u4f4e\u5206\u4e5f\u662f\u4e00\u6837\u7684<\/strong><\/li><\/ul>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">postgres=# select * from(select *,row_number() over(partition by course order by score)rn from student)t where rn=1;\n id |  name  | course | score | rn \n----+--------+--------+-------+----\n 15 | \u9ece\u660e   | \u5316\u5b66   |    78 |  1\n  3 | \u5468\u6da6\u53d1 | \u5916\u8bed   |    67 |  1\n 12 | \u9ece\u660e   | \u6570\u5b66   |    65 |  1\n  9 | \u5468\u661f\u9a70 | \u7269\u7406   |    68 |  1\n 11 | \u9ece\u660e   | \u8bed\u6587   |    85 |  1\n(5 rows)\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u5728PostgreSQL\u4e2d\uff0cROW_NUMBER()\u51fd\u6570\u7528\u4e8e\u4e3a\u7ed3\u679c\u96c6\u4e2d\u7684\u6bcf\u4e00\u884c\u5206\u914d\u4e00\u4e2a\u552f\u4e00\u7684\u6574\u6570\u503c\u3002 \u7528\u6cd5\uff1a R [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[],"_links":{"self":[{"href":"https:\/\/92it.top\/index.php?rest_route=\/wp\/v2\/posts\/12741"}],"collection":[{"href":"https:\/\/92it.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/92it.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/92it.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/92it.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=12741"}],"version-history":[{"count":1,"href":"https:\/\/92it.top\/index.php?rest_route=\/wp\/v2\/posts\/12741\/revisions"}],"predecessor-version":[{"id":12742,"href":"https:\/\/92it.top\/index.php?rest_route=\/wp\/v2\/posts\/12741\/revisions\/12742"}],"wp:attachment":[{"href":"https:\/\/92it.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=12741"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/92it.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=12741"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/92it.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=12741"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}