{"id":232,"date":"2025-03-10T12:20:19","date_gmt":"2025-03-10T04:20:19","guid":{"rendered":"https:\/\/zhoujibin.com\/?p=232"},"modified":"2025-03-10T12:22:18","modified_gmt":"2025-03-10T04:22:18","slug":"sql%e5%b8%b8%e8%a7%81%e9%9d%a2%e8%af%95%e9%a2%98","status":"publish","type":"post","link":"https:\/\/zhoujibin.com\/?p=232","title":{"rendered":"SQL\u5e38\u89c1\u9762\u8bd5\u9898"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">\u67e5\u8be2\u6bcf\u4e2a\u73ed\u7ea7\u4e2d\u603b\u5206\u524d3\u7684\u5b66\u751f\u4fe1\u606f<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">\u793a\u4f8b<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">\u73ed\u7ea7\u8868<\/h4>\n\n\n\n<p>t_class<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>\u5217\u540d<\/th><th>\u6ce8\u91ca<\/th><\/tr><\/thead><tbody><tr><td>id<\/td><td>\u4e3b\u952eid<\/td><\/tr><tr><td>name<\/td><td>\u73ed\u7ea7\u540d\u79f0<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">\u5b66\u751f\u8868<\/h4>\n\n\n\n<p>t_student<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>\u5217\u540d<\/th><th>\u6ce8\u91ca<\/th><\/tr><\/thead><tbody><tr><td>id<\/td><td>\u4e3b\u952eid<\/td><\/tr><tr><td>name<\/td><td>\u5b66\u751f\u540d\u79f0<\/td><\/tr><tr><td>score<\/td><td>\u603b\u5206<\/td><\/tr><tr><td>class_id<\/td><td>\u73ed\u7ea7id<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">\u5efa\u8868\u8bed\u53e5<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>-- \u521b\u5efa\u73ed\u7ea7\u8868\nCREATE TABLE t_class (\n    id INT PRIMARY KEY,\n    name VARCHAR(255) NOT NULL\n);\n\n-- \u521b\u5efa\u5b66\u751f\u8868\nCREATE TABLE t_student (\n    id INT PRIMARY KEY,\n    name VARCHAR(255) NOT NULL,\n    score INT,\n    class_id INT,\n    FOREIGN KEY (class_id) REFERENCES t_class(id)\n);\n\n-- \u6a21\u62df\u6570\u636e\n-- \u63d2\u5165\u73ed\u7ea7\u6570\u636e\nINSERT INTO t_class (id, name) VALUES\n(1, 'Class A'),\n(2, 'Class B'),\n(3, 'Class C');\n\n-- \u63d2\u5165\u5b66\u751f\u6570\u636e\nINSERT INTO t_student (id, name, score, class_id) VALUES\n-- Class A\n(1, 'Alice', 85, 1),\n(2, 'Bob', 90, 1),\n(3, 'Charlie', 78, 1),\n(4, 'David', 92, 1),\n(5, 'Eva', 88, 1),\n(6, 'Frank', 80, 1),\n(7, 'Grace', 87, 1),\n(8, 'Hank', 91, 1),\n(9, 'Ivy', 89, 1),\n(10, 'Jack', 84, 1),\n-- Class B\n(11, 'Kim', 86, 2),\n(12, 'Leo', 81, 2),\n(13, 'Mia', 93, 2),\n(14, 'Nina', 79, 2),\n(15, 'Oscar', 90, 2),\n(16, 'Paul', 82, 2),\n(17, 'Quincy', 89, 2),\n(18, 'Rachel', 91, 2),\n(19, 'Steve', 88, 2),\n(20, 'Tina', 85, 2),\n-- Class C\n(21, 'Uma', 87, 3),\n(22, 'Victor', 92, 3),\n(23, 'Wendy', 81, 3),\n(24, 'Xander', 89, 3),\n(25, 'Yara', 90, 3),\n(26, 'Zack', 84, 3),\n(27, 'Abby', 88, 3),\n(28, 'Brian', 80, 3),\n(29, 'Cindy', 86, 3),\n(30, 'Derek', 82, 3);<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">SQL<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT\n    t3.NAME class_name,\n    t1.NAME student_name,\n    t1.score \nFROM\n    (\n    SELECT\n        s.ID,\n        s.NAME,\n        s.score,\n        s.class_id,\n        -- \u6839\u636e\u73ed\u7ea7\u5206\u7ec4\u7136\u540e\u518d\u6839\u636e\u5206\u6570\u964d\u5e8f\u6392\u5217\u5f97\u5230\u5206\u7ec4\u540e\u7684\u5e8f\u53f7\n        ROW_NUMBER ( ) OVER ( PARTITION BY s.class_id ORDER BY s.score DESC ) AS RANK \n    FROM\n        t_student s \n    ) t1\n    JOIN t_student t2 ON t1.ID = t2.ID \n    AND t1.RANK &lt;= 3\n    JOIN t_class t3 ON t1.class_id = t3.ID \nORDER BY\n    t3.NAME,\n    t1.score DESC,\n    t1.NAME<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">\u7ed3\u679c\u5c55\u793a<\/h3>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>class_name<\/th><th>student_name<\/th><th>score<\/th><\/tr><\/thead><tbody><tr><td>Class A<\/td><td>David<\/td><td>92<\/td><\/tr><tr><td>Class A<\/td><td>Hank<\/td><td>91<\/td><\/tr><tr><td>Class A<\/td><td>Bob<\/td><td>90<\/td><\/tr><tr><td>Class B<\/td><td>Mia<\/td><td>93<\/td><\/tr><tr><td>Class B<\/td><td>Rachel<\/td><td>91<\/td><\/tr><tr><td>Class B<\/td><td>Oscar<\/td><td>90<\/td><\/tr><tr><td>Class C<\/td><td>Victor<\/td><td>92<\/td><\/tr><tr><td>Class C<\/td><td>Yara<\/td><td>90<\/td><\/tr><tr><td>Class C<\/td><td>Xander<\/td><td>89<\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>\u67e5\u8be2\u6bcf\u4e2a\u73ed\u7ea7\u4e2d\u603b\u5206\u524d3\u7684\u5b66\u751f\u4fe1\u606f \u793a\u4f8b \u73ed\u7ea7\u8868 t_class \u5217\u540d \u6ce8\u91ca id \u4e3b\u952eid name \u73ed\u7ea7\u540d\u79f0 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[26],"class_list":["post-232","post","type-post","status-publish","format-standard","hentry","category-database","tag-sql"],"_links":{"self":[{"href":"https:\/\/zhoujibin.com\/index.php?rest_route=\/wp\/v2\/posts\/232"}],"collection":[{"href":"https:\/\/zhoujibin.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhoujibin.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhoujibin.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhoujibin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=232"}],"version-history":[{"count":2,"href":"https:\/\/zhoujibin.com\/index.php?rest_route=\/wp\/v2\/posts\/232\/revisions"}],"predecessor-version":[{"id":235,"href":"https:\/\/zhoujibin.com\/index.php?rest_route=\/wp\/v2\/posts\/232\/revisions\/235"}],"wp:attachment":[{"href":"https:\/\/zhoujibin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=232"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhoujibin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=232"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhoujibin.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=232"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}