{"id":3783,"date":"2021-01-25T17:08:48","date_gmt":"2021-01-25T09:08:48","guid":{"rendered":"https:\/\/www.gbase8.cn\/?p=3783"},"modified":"2021-07-19T15:51:05","modified_gmt":"2021-07-19T07:51:05","slug":"sql%e9%9d%a2%e8%af%95%e9%a2%98%ef%bc%8c%e6%9f%a5%e8%af%a2%e5%87%ba%e6%af%8f%e9%97%a8%e8%af%be%e9%83%bd%e5%a4%a7%e4%ba%8e80-%e5%88%86%e7%9a%84%e5%ad%a6%e7%94%9f%e5%a7%93%e5%90%8d","status":"publish","type":"post","link":"https:\/\/www.gbase8.cn\/en\/3783","title":{"rendered":"\u6570\u636e\u5e93SQL\u9762\u8bd5\u9898\uff0c\u67e5\u8be2\u51fa\u6bcf\u95e8\u8bfe\u90fd\u5927\u4e8e80 \u5206\u7684\u5b66\u751f\u59d3\u540d"},"content":{"rendered":"<p>\u672c\u6587\u6839\u636e\u7f51\u4e0a\u5e38\u89c1\u7684SQL\u9762\u8bd5\u9898\uff0c\u67e5\u8be2\u51fa\u6bcf\u95e8\u8bfe\u90fd\u5927\u4e8e80\u00a0\u5206\u7684\u5b66\u751f\u59d3\u540d\uff0c\u7ed9\u51fa\u5206\u6790\u548c\u5728GBase 8a\u6570\u636e\u5e93\u96c6\u7fa4\u4e0a\u7684\u6267\u884c\u7ed3\u679c\u3002<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Directory Navigation<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a2aa15a30a19\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a2aa15a30a19\"  aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.gbase8.cn\/en\/3783\/#%E9%A2%98%E7%9B%AE\" >\u9898\u76ee<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.gbase8.cn\/en\/3783\/#%E5%88%86%E6%9E%90\" >Analyse<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.gbase8.cn\/en\/3783\/#%E7%AD%94%E6%A1%88%E6%A0%B7%E4%BE%8B\" >\u7b54\u6848\u6837\u4f8b<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.gbase8.cn\/en\/3783\/#%E9%80%9A%E8%BF%87group\" >\u901a\u8fc7group<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.gbase8.cn\/en\/3783\/#%E9%80%9A%E8%BF%87not_exists\" >\u901a\u8fc7not exists<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.gbase8.cn\/en\/3783\/#%E6%80%BB%E7%BB%93\" >Summary<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E9%A2%98%E7%9B%AE\"><\/span>\u9898\u76ee<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>\u5df2\u77e5\u5b66\u751f\u6210\u7ee9\u5982\u4e0b\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>gbase> select * from exam_1;\r\n+--------+--------+-------+\r\n| name   | class  | score |\r\n+--------+--------+-------+\r\n| \u5f20\u4e09   | \u8bed\u6587   |    81 |\r\n| \u5f20\u4e09   | \u6570\u5b66   |    75 |\r\n| \u674e\u56db   | \u8bed\u6587   |    76 |\r\n| \u674e\u56db   | \u6570\u5b66   |    90 |\r\n| \u738b\u4e94   | \u8bed\u6587   |    81 |\r\n| \u738b\u4e94   | \u6570\u5b66   |   100 |\r\n| \u738b\u4e94   | \u82f1\u8bed   |    90 |\r\n+--------+--------+-------+\r\n7 rows in set (Elapsed: 00:00:00.00)\r<\/code><\/pre>\n\n\n\n<p>\u67e5\u8be2\u51fa\u6bcf\u95e8\u8bfe\u90fd\u5927\u4e8e80 \u5206\u7684\u5b66\u751f\u59d3\u540d<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%88%86%E6%9E%90\"><\/span>Analyse<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>1\u3001\u9898\u76ee\u4e2d\uff0c\u8bfe\u7a0b\u6570\u91cf\u4e0d\u662f\u76f8\u540c\u7684\uff0c\u6240\u4ee5\u4e0d\u80fd\u63d0\u524d\u5047\u8bbe\u53ea\u67093\u95e8\u6210\u7ee9\uff0c\u89e3\u7b54\u65b9\u6848\u5fc5\u987b\u80fd\u9002\u5e94\u4efb\u610f\u8bfe\u7a0b\u6570\u91cf\u3002<\/p>\n\n\n\n<p>2\u3001\u9898\u76ee\u53ea\u8981\u6c42\u5b66\u751f\u59d3\u540d\uff0c\u6240\u4ee5\u8bfe\u7a0b\u5217\u51e0\u4e4e\u5c31\u662f\u591a\u4f59\u7684\uff0c\u7b97\u5e72\u6270\u56e0\u7d20\u4e86\u3002<\/p>\n\n\n\n<p>3\u3001\u5927\u4e8e80\u5206\uff0c\u6307\u6700\u4f4e\u5206\u662f>80\u7684\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E7%AD%94%E6%A1%88%E6%A0%B7%E4%BE%8B\"><\/span>\u7b54\u6848\u6837\u4f8b<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E9%80%9A%E8%BF%87group\"><\/span>\u901a\u8fc7group<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>\u4ee5name\u4f5c\u4e3a\u7ef4\u5ea6\uff0c\u6700\u5c0f\u5206\u6570min(score)\u505a\u805a\u5408\u8fd0\u7b97\uff0c\u7136\u540e\u9009\u62e9\u90a3\u4e9b\u6700\u5c0f\u5206\u6570\u5927\u4e8e80\u5206\u7684<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>gbase> select name from exam_1 group by name having(min(score)>80);\r\n+--------+\r\n| name   |\r\n+--------+\r\n| \u738b\u4e94   |\r\n+--------+\r\n1 row in set (Elapsed: 00:00:00.03)\r\n<\/code><\/pre>\n\n\n\n<p>\u6267\u884c\u8ba1\u5212\u5982\u4e0b<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>gbase> explain select name from exam_1 group by name having(min(score)>80);\r\n+----+----------------+-----------+-------------+-------------------------------+\r\n| ID | MOTION         | OPERATION | TABLE       | CONDITION                     |\r\n+----+----------------+-----------+-------------+-------------------------------+\r\n| 01 | &#91;RESULT]       |  Step     | &lt;00>        |                               |\r\n|    |                |  GROUP    |             | GROUP BY name                 |\r\n|    |                |  HAVING   |             | HAVING (MIN(MIN(score)) > 80) |\r\n| 00 | &#91;REDIST(name)] |  Table    | exam_1&#91;DIS] |                               |\r\n|    |                |  GROUP    |             | GROUP BY name                 |\r\n+----+----------------+-----------+-------------+-------------------------------+\r\n5 rows in set (Elapsed: 00:00:00.01)\r\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E9%80%9A%E8%BF%87not_exists\"><\/span>\u901a\u8fc7not exists<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>\u5c06\u90a3\u4e9b\u5b58\u5728\u5206\u6570&lt;=80\u7684name \u6392\u9664\u5728\u5916\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>gbase> select distinct name from exam_1 a where not exists (select 1 from exam_1 b where score&lt;=80 and a.name=b.name);\r\n+--------+\r\n| name   |\r\n+--------+\r\n| \u738b\u4e94   |\r\n+--------+\r\n1 row in set (Elapsed: 00:00:00.08)\r\n<\/code><\/pre>\n\n\n\n<p>\u6267\u884c\u8ba1\u5212\u5982\u4e0b<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>gbase> explain select distinct name from exam_1 a where not exists (select 1 from exam_1 b where score&lt;=80 and a.name=b.name);\r\n+----+----------------+------------+-------------+--------------------------+\r\n| ID | MOTION         | OPERATION  | TABLE       | CONDITION                |\r\n+----+----------------+------------+-------------+--------------------------+\r\n| 02 | &#91;RESULT]       |  Step      | &lt;00>        |                          |\r\n|    |                |  WHERE     |             | NOT EXISTS (&#91;SubQuery1]) |\r\n|    |                |  AGG       |             |                          |\r\n| 01 | &#91;REDIST(name)] |  SubQuery1 | placeholder |                          |\r\n|    |                |   SCAN     | b&#91;DIS]      | (score{S} &lt;= 80)         |\r\n| 00 | &#91;REDIST(name)] |  Table     | a&#91;DIS]      |                          |\r\n+----+----------------+------------+-------------+--------------------------+\r\n6 rows in set (Elapsed: 00:00:00.00)\r\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%80%BB%E7%BB%93\"><\/span>Summary<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>2\u4e2a\u65b9\u6848\uff0c\u4ece\u6267\u884c\u8ba1\u5212\u770b\uff0c\u65b9\u68481\u7684group\u6027\u80fd\u9884\u8ba1\u66f4\u4f18\u4e00\u4e9b\u3002\u65b9\u68482\u5b58\u5728join\u7684\u60c5\u51b5\u3002<\/p>","protected":false},"excerpt":{"rendered":"<p>\u672c\u6587\u6839\u636e\u7f51\u4e0a\u5e38\u89c1\u7684SQL\u9762\u8bd5\u9898\uff0c\u67e5\u8be2\u51fa\u6bcf\u95e8\u8bfe\u90fd\u5927\u4e8e80 \u5206\u7684\u5b66\u751f\u59d3\u540d\uff0c\u7ed9\u51fa\u5206\u6790\u548c\u5728GBase 8a\u6570\u636e\u5e93\u96c6\u7fa4\u4e0a\u7684\u6267\u884c\u7ed3\u679c\u3002<\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[81],"class_list":["post-3783","post","type-post","status-publish","format-standard","hentry","category-gbase8a","tag-81"],"_links":{"self":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/3783","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/comments?post=3783"}],"version-history":[{"count":2,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/3783\/revisions"}],"predecessor-version":[{"id":7565,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/3783\/revisions\/7565"}],"wp:attachment":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/media?parent=3783"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/categories?post=3783"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/tags?post=3783"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}