{"id":1588,"date":"2022-11-18T23:30:46","date_gmt":"2022-11-18T15:30:46","guid":{"rendered":"https:\/\/qaqaq.top\/?p=1588"},"modified":"2022-11-27T12:40:58","modified_gmt":"2022-11-27T04:40:58","slug":"%e7%ac%ac18%e7%ab%a0_mysql8-0%e7%9a%84%e5%85%b6%e5%ae%83%e6%96%b0%e7%89%b9%e6%80%a7%e8%af%be%e5%90%8e%e7%bb%83%e4%b9%a0","status":"publish","type":"post","link":"https:\/\/qaqaq.top\/?p=1588","title":{"rendered":"\u7b2c18\u7ae0_MySQL8.0\u7684\u5176\u5b83\u65b0\u7279\u6027\u8bfe\u540e\u7ec3\u4e60"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code># \u7b2c18\u7ae0_MySQL8.0\u7684\u5176\u5b83\u65b0\u7279\u6027\u8bfe\u540e\u7ec3\u4e60\r\n\r\n# 1. \u521b\u5efastudents\u6570\u636e\u8868\uff0c\u5982\u4e0b\r\n\r\nCREATE DATABASE test18_mysql8;\r\n\r\nUSE test18_mysql8;\r\n\r\nCREATE TABLE students(\r\n    id INT PRIMARY KEY AUTO_INCREMENT,\r\n    student VARCHAR(15),\r\n    points TINYINT\r\n);\r\n\r\n#2. \u5411\u8868\u4e2d\u6dfb\u52a0\u6570\u636e\u5982\u4e0b\r\nINSERT INTO students(student,points)\r\nVALUES\r\n    ('\u5f20\u4e09',89),\r\n    ('\u674e\u56db',77),\r\n    ('\u738b\u4e94',88),\r\n    ('\u8d75\u516d',90),\r\n    ('\u5b59\u4e03',90),\r\n    ('\u5468\u516b',88);\r\n\r\nSELECT * FROM students;\r\n\r\n#3. \u5206\u522b\u4f7f\u7528RANK()\u3001DENSE_RANK() \u548c ROW_NUMBER()\u51fd\u6570\u5bf9\u5b66\u751f\u6210\u7ee9\u964d\u5e8f\u6392\u5217\u60c5\u51b5\u8fdb\u884c\u663e\u793a\r\n#\u65b9\u5f0f1\uff1a\r\nSELECT ROW_NUMBER() over (ORDER BY points DESC) AS \"\u6392\u5e8f1\",\r\n       RANK() over (ORDER BY points DESC) AS \"\u6392\u5e8f2\",\r\n       DENSE_RANK() over (ORDER BY points DESC) AS \"\u6392\u5e8f3\",\r\n       student,points\r\nFROM students;\r\n\r\n#\u65b9\u5f0f2\uff1a\r\nSELECT ROW_NUMBER() over w AS \"\u6392\u5e8f1\",\r\n       RANK() over w AS \"\u6392\u5e8f2\",\r\n       DENSE_RANK() over w AS \"\u6392\u5e8f3\",\r\n       student,points\r\nFROM students WINDOW w AS (ORDER BY points DESC);\r\n\r\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[47],"class_list":["post-1588","post","type-post","status-publish","format-standard","hentry","category-mysql-code","tag-mysql"],"_links":{"self":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1588"}],"collection":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1588"}],"version-history":[{"count":1,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1588\/revisions"}],"predecessor-version":[{"id":1589,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1588\/revisions\/1589"}],"wp:attachment":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1588"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1588"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1588"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}