{"id":763,"date":"2012-04-18T23:55:33","date_gmt":"2012-04-18T15:55:33","guid":{"rendered":"https:\/\/yangtingkun.net\/?p=763"},"modified":"2012-04-20T23:56:05","modified_gmt":"2012-04-20T15:56:05","slug":"%e5%9b%9b%e4%ba%ba%e8%bf%87%e6%a1%a5%e9%97%ae%e9%a2%98%e7%9a%84sql%e8%a7%a3%e6%b3%95","status":"publish","type":"post","link":"https:\/\/yangtingkun.net\/?p=763","title":{"rendered":"\u56db\u4eba\u8fc7\u6865\u95ee\u9898\u7684SQL\u89e3\u6cd5"},"content":{"rendered":"<p>\u770b\u4e86\u5f00\u53d1\u7248\u7684\u5e16\u5b50\uff0c\u53c8\u6ca1\u6709\u5fcd\u4f4f\uff0c\u7b80\u5355\u5199\u4e86\u4e00\u4e2aSQL\u8bed\u53e5\u3002\u5173\u4e8e\u95ee\u9898\u7684\u8be6\u7ec6\u63cf\u8ff0\u53ef\u4ee5\u53c2\u8003<a href=\"http:\/\/www.itpub.net\/thread-1595264-1-1.html\">http:\/\/www.itpub.net\/thread-1595264-1-1.html<\/a><br \/>\n\u4f20\u8bf4\u4e2d\u7684\u5fae\u8f6f\u9762\u8bd5\u9898\uff1a<br \/>\n\u6709\u4e00\u7fa4\u4ebaA,B,C,D (\u4eba\u6570>=2)\u8981\u5728\u591c\u91cc\u8d70\u8fc7\u72ec\u6728\u6865\u8fc7\u6cb3\uff0c\u4ed6\u4eec\u53ea\u6709\u4e00\u628a\u624b\u7535\u7b52\u3002\u56db\u4e2a\u4eba\u7684\u901f\u5ea6\u4e0d\u540c\uff0c\u8fc7\u6cb3\u5206\u522b\u9700\u89811,2,5,10\u5206\u949f\uff0c\u6865\u4e0a\u6700\u591a\u8d70\u4e24\u4e2a\u4eba\uff0c\u4e24\u4e2a\u4eba\u4e00\u8d77\u8d70\u65f6\u6309\u901f\u5ea6\u6162\u7684\u8ba1\u7b97\u3002\u8fc7\u6cb3\u4e00\u5b9a\u8981\u7528\u624b\u7535\u7b52\u3002\u8bf7\u95ee\u6700\u5feb\u7684\u65b9\u6cd5\u662f\u5982\u4f55\u5b89\u6392\uff0c\u9700\u8981\u51e0\u5206\u949f\uff1f<br \/>\n\u4f8b\u5b50\u8f93\u51fa\uff1a<br \/>\nA B,A,A C,A,A D  19<br \/>\n\u76f4\u63a5\u7ed9\u51fa\u6700\u7ec8\u7ed3\u679c\uff1a<\/p>\n<pre lang='SQL'>SQL> WITH C AS\r\n  2  (SELECT NAME, TIME, POWER(2, ROWNUM - 1) POS FROM BRIDGE_CROSSING), \r\n  3  A AS\r\n  4  (SELECT 0 RN, A.NAME || ' ' || B.NAME NAME, GREATEST(A.TIME, B.TIME) TIME, A.POS + B.POS POS\r\n  5  FROM C A, C B\r\n  6  WHERE A.NAME < B.NAME\r\n  7  UNION ALL\r\n  8  SELECT 1, NAME, TIME, POS FROM C),\r\n  9  B (RN, NAME, TIME, POS) AS\r\n 10  (SELECT 0 RN, CAST ('' AS VARCHAR2(4000)) NAME, 0, 0 POS FROM DUAL\r\n 11  UNION ALL\r\n 12  SELECT B.RN + 1, \r\n 13     B.NAME || ',' || A.NAME, \r\n 14     B.TIME + A.TIME, \r\n 15     DECODE(MOD(B.RN, 2), 0, B.POS + A.POS, B.POS - A.POS)\r\n 16  FROM B, A\r\n 17  WHERE MOD(B.RN, 2) = A.RN\r\n 18  AND DECODE(MOD(B.RN, 2), 0, BITAND(B.POS, A.POS), BITAND(B.POS - A.POS, A.POS)) = 0)\r\n 19  SELECT LTRIM(NAME, ',') NAME, TIME FROM \r\n 20  (SELECT NAME, TIME, RANK() OVER(ORDER BY TIME) RN \r\n 21  FROM B\r\n 22  WHERE POS = POWER(2, 4) - 1)\r\n 23  WHERE RN = 1; \r\nNAME                                 TIME\r\n------------------------------ ----------\r\nA B,A,C D,B,A B                        17\r\nA B,B,C D,A,A B                        17<\/pre>\n<p>\u7b80\u5355\u63cf\u8ff0\u4e00\u4e0b\u601d\u8def\uff0c\u901a\u8fc7\u6784\u9020\u4e00\u4e2aPOS\u5217\u6807\u8bc6\u6bcf\u4e2a\u4eba\u7684\u4f4d\u7f6e\uff0c\u6bd4\u59821101\u8868\u660eABD\u4e09\u4e2a\u4eba\u90fd\u5df2\u7ecf\u8fc7\u6865\u4e86\uff0c\u5982\u679cPOS\u7684\u503c\u8fbe\u523015\uff0c\u5219\u8bf4\u660e\u6240\u6709\u4eba\u90fd\u8fc7\u6865\u3002<br \/>\n\u8fd9\u4e2a\u9012\u5f52WITH\u548c\u4ee5\u5f80\u7684\u533a\u522b\u5728\u4e8e\uff0c\u4e24\u4e2a\u4eba\u4e00\u8d77\u8fc7\u6865\u548c\u4e00\u4e2a\u4eba\u56de\u6765\u9001\u624b\u7535\u7684\u5904\u7406\u89c4\u5219\u662f\u4e0d\u540c\u7684\uff0c\u751a\u81f3\u8fde\u63a5\u6570\u636e\u90fd\u662f\u4e0d\u540c\u7684\u3002\u8fd9\u91cc\u91c7\u7528\u5c06\u4e24\u4e2a\u4eba\u8fc7\u6865\u548c\u4e00\u4e2a\u4eba\u56de\u6765\u4f5c\u4e3a\u4e0d\u540c\u7684\u8bb0\u5f55\u63d2\u5165\u5230\u540c\u4e00\u4e2a\u8868\u4e2d\uff0c\u7136\u540e\u7528\u4e0d\u540c\u7684ID\u8fdb\u884c\u533a\u522b\u3002\u968f\u540e\u7684\u9012\u5f52\u8c03\u7528\uff0c\u65e0\u8bba\u662f\u67e5\u8be2\u5217\uff0c\u8fd8\u662f\u8fdb\u884c\u5173\u8054\u7684\u65f6\u5019\u90fd\u8981\u8fdb\u884c\u6761\u4ef6\u7684\u5224\u65ad\uff0c\u5224\u65ad\u5f53\u524d\u662f\u4e24\u4e2a\u4eba\u8fc7\u6865\uff0c\u8fd8\u662f\u4e00\u4e2a\u4eba\u56de\u6765\uff0c\u4ece\u800c\u8fdb\u884c\u4e0d\u540c\u7684\u64cd\u4f5c\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u770b\u4e86\u5f00\u53d1\u7248\u7684\u5e16\u5b50\uff0c\u53c8\u6ca1\u6709\u5fcd\u4f4f\uff0c\u7b80\u5355\u5199\u4e86\u4e00\u4e2aSQL\u8bed\u53e5\u3002\u5173\u4e8e\u95ee\u9898\u7684\u8be6\u7ec6\u63cf\u8ff0\u53ef\u4ee5\u53c2\u8003http:\/\/www.itpub.net\/thread-1595264-1-1.html \u4f20\u8bf4\u4e2d\u7684\u5fae\u8f6f\u9762\u8bd5\u9898\uff1a \u6709\u4e00\u7fa4\u4ebaA,B,C,D (\u4eba\u6570>=2)\u8981\u5728\u591c\u91cc\u8d70\u8fc7\u72ec\u6728\u6865\u8fc7\u6cb3\uff0c\u4ed6\u4eec\u53ea\u6709\u4e00\u628a\u624b\u7535\u7b52\u3002\u56db\u4e2a\u4eba\u7684\u901f\u5ea6\u4e0d\u540c\uff0c\u8fc7\u6cb3\u5206\u522b\u9700\u89811,2,5,10\u5206\u949f\uff0c\u6865\u4e0a\u6700\u591a\u8d70\u4e24\u4e2a\u4eba\uff0c\u4e24\u4e2a\u4eba\u4e00\u8d77\u8d70\u65f6\u6309\u901f\u5ea6\u6162\u7684\u8ba1\u7b97\u3002\u8fc7\u6cb3\u4e00\u5b9a\u8981\u7528\u624b\u7535\u7b52\u3002\u8bf7\u95ee\u6700\u5feb\u7684\u65b9\u6cd5\u662f\u5982\u4f55\u5b89\u6392\uff0c\u9700\u8981\u51e0\u5206\u949f\uff1f \u4f8b\u5b50\u8f93\u51fa\uff1a A B,A,A C,A,A D 19 \u76f4\u63a5\u7ed9\u51fa\u6700\u7ec8\u7ed3\u679c\uff1a SQL> WITH C AS 2 (SELECT NAME, TIME, POWER(2, ROWNUM &#8211; 1) POS FROM BRIDGE_CROSSING), 3 A AS 4 (SELECT 0 RN, A.NAME || &#8216; &#8216; || B.NAME NAME, &hellip; <a href=\"https:\/\/yangtingkun.net\/?p=763\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3],"tags":[634,228,633],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2yiY3-cj","_links":{"self":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/763"}],"collection":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=763"}],"version-history":[{"count":1,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/763\/revisions"}],"predecessor-version":[{"id":765,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/763\/revisions\/765"}],"wp:attachment":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=763"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=763"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=763"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}