{"id":2110,"date":"2020-07-29T23:57:31","date_gmt":"2020-07-29T15:57:31","guid":{"rendered":"http:\/\/39.97.231.10\/?p=2110"},"modified":"2020-07-29T23:57:31","modified_gmt":"2020-07-29T15:57:31","slug":"mysql-8-0-cte-%e9%80%92%e5%bd%92%e6%9f%a5%e8%af%a2","status":"publish","type":"post","link":"http:\/\/www.weijing.co\/?p=2110","title":{"rendered":"MySQL 8.0 CTE \u9012\u5f52\u67e5\u8be2"},"content":{"rendered":"
\n
\nMySQL 8.0 \u5f15\u5165CTE(Common Table Expression)\u529f\u80fd\uff0cCTE\u9664\u4e86\u66ff\u4ee3\u6d3e\u751f\u8868\u4ee5\u5916\uff0c\u8fd8\u6709\u4e00\u4e2a\u91cd\u8981\u7684\u529f\u80fd\uff0c\u5b9e\u73b0\u9012\u5f52\u67e5\u8be2\u3002\u5728CTE\u529f\u80fd\u5f15\u5165\u4e4b\u524d\uff0cMySQL\u5f88\u96be\u5728SQL\u8bed\u53e5\u5c42\u5b9e\u73b0\u9012\u5f52\u67e5\u8be2\uff0c\u4e00\u79cd\u95f4\u63a5\u7684\u65b9\u5f0f\u662f\u901a\u8fc7\u521b\u5efa\u5b58\u50a8\u8fc7\u7a0b\uff0c\u800c\u5f15\u5165CTE\u540e\uff0cSQL\u8bed\u53e5\u7ea7\u7684\u9012\u5f52\u67e5\u8be2\u5c06\u53d8\u5f97\u5f88\u5bb9\u6613\uff0c\u672c\u6587\u5c06\u7b80\u5355\u4ecb\u7ecdCTE\u9012\u5f52\u67e5\u8be2\u7684\u4f7f\u7528\u3002\n<\/div>\n<\/div>\n
\n
\n
<\/div>\n
\u4e00\u3001\u4ec0\u4e48\u662fCTE\u9012\u5f52\u67e5\u8be2\uff1f<\/div>\n
<\/div>\n
CTE\u9012\u5f52\u67e5\u8be2\u53ef\u4ee5\u770b\u4f5c\u662f\u4e00\u4e2a\u5b50\u67e5\u8be2\u5728\u91cd\u590d\u8c03\u7528\u81ea\u5df1\uff0c\u5b83\u7684\u4f7f\u7528\u573a\u666f\u5982\u4e0b\uff1a<\/div>\n
    \n
  1. \n
    \u751f\u6210\u5e8f\u5217<\/div>\n<\/li>\n
  2. \n
    \u904d\u5386\u5c42\u6b21\u6216\u6811\u5f62\u7ed3\u6784<\/div>\n<\/li>\n<\/ol>\n
    <\/div>\n
    CTE\u9012\u5f52\u8bed\u6cd5\uff1a<\/div>\n
    \n
    WITH\u00a0RECURSIVE\u00a0cte AS (<\/div>\n
    \u00a0\u00a0\u00a0initial_query\u00a0\u00a0\u00a0\u00a0— \\”seed\\” member<\/div>\n
    \u00a0\u00a0\u00a0UNION ALL<\/div>\n
    \u00a0\u00a0\u00a0recursive_query\u00a0\u00a0\u00a0\u00a0— recusive member that references to the same CTE name<\/div>\n
    )<\/div>\n
    SELECT * FROM cte;\u00a0\u00a0\u00a0\u00a0— main query<\/div>\n<\/div>\n
    <\/div>\n
    \u4e0a\u8ff0CTE\u9012\u5f52\u8bed\u6cd5\u4e2d\uff0cRECURSIVE\u5173\u952e\u5b57\u662f\u5fc5\u4e0d\u53ef\u5c11\u7684\uff0c\u9664\u6b64\u4e4b\u5916\uff0c\u8fd8\u6709\u4e24\u4e2a\u5fc5\u4e0d\u53ef\u5c11\u7684\u6210\u5458\uff0c\u4e00\u4e2a\u662fseed member\uff0c\u79cd\u5b50\u6210\u5458\uff0c\u5b83\u662f\u4e00\u4e2a\u521d\u59cb\u5316\u7684\u67e5\u8be2\uff0c\u5c06\u5728\u7b2c\u4e00\u6b21\u8fed\u4ee3\u65f6\u88ab\u6267\u884c\u3002\u53e6\u5916\u4e00\u4e2a\u662frecusive member\uff0c\u9012\u5f52\u6210\u5458\uff0c\u5b83\u662f\u9012\u5f52\u8fed\u4ee3\u7684\u4e3b\u8981\u8fc7\u7a0b\uff0c\u5b83\u5c06\u4f1a\u5728\u4e3b\u67e5\u8be2\u4e2d\u751f\u6210\u6240\u6709\u540e\u7eed\u7684item\u3002<\/div>\n
    <\/div>\n
    \u6574\u4e2a\u9012\u5f52\u8fc7\u7a0b\u5728\u4efb\u4f55\u4e00\u6b21\u8fed\u4ee3\u4e0d\u518d\u8fd4\u56de\u8bb0\u5f55\u65f6\u7ed3\u675f\uff0c\u8fd9\u91cc\u8981\u6ce8\u610f\uff0c\u907f\u514d\u7531\u4e8e\u8fed\u4ee3\u6b21\u6570\u8fc7\u591a\uff0c\u5bfc\u81f4\u5185\u5b58\u8017\u5c3d\u3002\u4e3a\u9012\u5f52\u6210\u5458\u8bbe\u7f6e\u4e00\u4e2a\u7ed3\u675f\u6761\u4ef6\u662f\u975e\u5e38\u91cd\u8981\u7684\uff0c\u5f53\u7136\u4e5f\u53ef\u4ee5\u901a\u8fc7\u8bbe\u7f6e\u9012\u5f52\u6df1\u5ea6\u3001\u6700\u5927\u6267\u884c\u65f6\u95f4\u6765\u9650\u5236\u9012\u5f52\u6267\u884c\uff0c\u4ee5\u4fbf\u5728\u8d85\u8fc7\u9650\u5236\u65f6\uff0c\u80fd\u591f\u5f3a\u5236\u7ed3\u675f\u9012\u5f52CTE\u3002<\/div>\n
    <\/div>\n
      \n
    • \n
      cte_max_recursion_depth \u53c2\u6570\u9ed8\u8ba4\u503c\u4e3a1000\uff0c\u9650\u5236CTE\u9012\u5f52\u6df1\u5ea6\uff0c\u8d85\u8fc7\u9608\u503c\uff0c\u5c06\u88ab\u5f3a\u5236\u7ec8\u6b62\u3002<\/div>\n<\/li>\n
    • \n
      max_execution_time \u53c2\u6570\u9650\u5236\u67e5\u8be2\u7684\u6700\u5927\u6267\u884c\u65f6\u95f4\uff0c\u8d85\u8fc7\u8be5\u65f6\u95f4\uff0c\u4e5f\u5c06\u88ab\u5f3a\u5236\u7ec8\u6b62\u3002<\/div>\n<\/li>\n<\/ul>\n
      <\/div>\n
      <\/div>\n
      \u4e8c\u3001CTE\u9012\u5f52\u7684\u7b80\u5355\u4f7f\u7528\u6848\u4f8b<\/div>\n
      <\/div>\n
      2.1 \u5355\u5c42\u6b21\u7684\u5e8f\u5217<\/div>\n
      <\/div>\n
      \u76ee\u6807\uff1a\u521b\u5efa\u4e00\u4e2a1\u523010\u7684\u6574\u6570\u5e8f\u5217\uff0c\u5982\u4e0b\uff1a<\/div>\n
      \n
      WITH RECURSIVE natural_sequence AS<\/div>\n
      \u00a0\u00a0( SELECT 1 AS n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0— seed member: our sequence starts from 1<\/div>\n
      \u00a0\u00a0\u00a0\u00a0UNION ALL<\/div>\n
      \u00a0\u00a0\u00a0\u00a0SELECT n + 1 FROM natural_sequence\u00a0\u00a0\u00a0\u00a0— recursive member: reference to itself<\/div>\n
      \u00a0\u00a0\u00a0\u00a0WHERE n < 10\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0— stop condition<\/div>\n
      \u00a0\u00a0)<\/div>\n
      SELECT * FROM natural_sequence;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0— main query<\/div>\n
      +——+<\/div>\n
      | n\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      +——+<\/div>\n
      |\u00a0\u00a0\u00a0\u00a01 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a02 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a03 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a04 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a05 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a06 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a07 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a08 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a09 |<\/div>\n
      |\u00a0\u00a0\u00a010 |<\/div>\n
      +——+<\/div>\n<\/div>\n
      <\/div>\n
      \u5982\u679c\u4e0d\u52a0\u7ed3\u675f\u6761\u4ef6\uff0c\u7531\u4e8e\u9012\u5f52\u6df1\u5ea6\u8d85\u8fc71000\uff0c\u88ab\u5f3a\u5236\u7ec8\u6b62\uff0c\u5982\u4e0b\uff1a<\/div>\n
      <\/div>\n
      \n
      mysql> WITH RECURSIVE natural_sequence AS ( SELECT 1 AS n\u00a0\u00a0\u00a0UNION ALL SELECT n + 1 FROM natural_sequence\u00a0\u00a0\u00a0) SELECT * FROM natural_sequence;<\/div>\n
      ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.<\/div>\n<\/div>\n
      <\/div>\n
      \u53e6\u5916\u4e00\u4e2a\u9636\u4e58\u7684\u4f8b\u5b50\uff0c\u5982\u4e0b\uff1a<\/div>\n
      \n
      mysql> WITH RECURSIVE factorial(n, fact) AS (<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT 0, 1<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0UNION ALL<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT n + 1, fact * (n+1)<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM factorial<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE n < 20 )<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT * from factorial;<\/div>\n
      +——+———————+<\/div>\n
      | n\u00a0\u00a0\u00a0\u00a0| fact\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      +——+———————+<\/div>\n
      |\u00a0\u00a0\u00a0\u00a00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a02 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a03 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a04 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a024 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a05 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0120 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a06 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0720 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a07 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a05040 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a08 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a040320 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a09 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0362880 |<\/div>\n
      |\u00a0\u00a0\u00a010 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03628800 |<\/div>\n
      |\u00a0\u00a0\u00a011 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a039916800 |<\/div>\n
      |\u00a0\u00a0\u00a012 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0479001600 |<\/div>\n
      |\u00a0\u00a0\u00a013 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06227020800 |<\/div>\n
      |\u00a0\u00a0\u00a014 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a087178291200 |<\/div>\n
      |\u00a0\u00a0\u00a015 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01307674368000 |<\/div>\n
      |\u00a0\u00a0\u00a016 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a020922789888000 |<\/div>\n
      |\u00a0\u00a0\u00a017 |\u00a0\u00a0\u00a0\u00a0\u00a0355687428096000 |<\/div>\n
      |\u00a0\u00a0\u00a018 |\u00a0\u00a0\u00a0\u00a06402373705728000 |<\/div>\n
      |\u00a0\u00a0\u00a019 |\u00a0\u00a0121645100408832000 |<\/div>\n
      |\u00a0\u00a0\u00a020 | 2432902008176640000 |<\/div>\n
      +——+———————+<\/div>\n<\/div>\n
      <\/div>\n
      2.2\u00a0\u53cc\u5c42\u6b21\u7684\u5e8f\u5217<\/div>\n
      <\/div>\n
      \u5b9e\u73b0\u4e00\u79cd\u5e8f\u5217\uff0cN+2\u7684\u503c\u7531\u524d\u4e24\u4e2a\u503cN+1\u4e0eN\u8ba1\u7b97\u800c\u6765\uff0c\u6700\u5178\u578b\u7684\u4f8b\u5b50\u5c31\u662f\u6590\u6ce2\u90a3\u5951\u6570\u5217\uff0c\u6700\u5f00\u59cb\u7684\u4e24\u4e2a\u6570\u662f0\uff0c1\uff0c\u540e\u9762\u7684\u6570\u90fd\u662f\u524d\u4e24\u4e2a\u6570\u4e4b\u548c\u3002\u4f7f\u7528\u9012\u5f52CTE\u5b9e\u73b0\uff0c\u5982\u4e0b\uff1a<\/div>\n
      \n
      mysql> WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS (<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT 1, 0, 1<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0UNION ALL<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT n + 1, next_fib_n, fib_n + next_fib_n<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM fibonacci<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE n < 20 )<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT * FROM fibonacci;<\/div>\n
      +——+——-+————+<\/div>\n
      | n\u00a0\u00a0\u00a0\u00a0| fib_n | next_fib_n |<\/div>\n
      +——+——-+————+<\/div>\n
      |\u00a0\u00a0\u00a0\u00a01 |\u00a0\u00a0\u00a0\u00a0\u00a00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a02 |\u00a0\u00a0\u00a0\u00a0\u00a01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a03 |\u00a0\u00a0\u00a0\u00a0\u00a01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a04 |\u00a0\u00a0\u00a0\u00a0\u00a02 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a05 |\u00a0\u00a0\u00a0\u00a0\u00a03 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a05 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a06 |\u00a0\u00a0\u00a0\u00a0\u00a05 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a08 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a07 |\u00a0\u00a0\u00a0\u00a0\u00a08 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a013 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a08 |\u00a0\u00a0\u00a0\u00a013 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a021 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a09 |\u00a0\u00a0\u00a0\u00a021 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a034 |<\/div>\n
      |\u00a0\u00a0\u00a010 |\u00a0\u00a0\u00a0\u00a034 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a055 |<\/div>\n
      |\u00a0\u00a0\u00a011 |\u00a0\u00a0\u00a0\u00a055 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a089 |<\/div>\n
      |\u00a0\u00a0\u00a012 |\u00a0\u00a0\u00a0\u00a089 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0144 |<\/div>\n
      |\u00a0\u00a0\u00a013 |\u00a0\u00a0\u00a0144 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0233 |<\/div>\n
      |\u00a0\u00a0\u00a014 |\u00a0\u00a0\u00a0233 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0377 |<\/div>\n
      |\u00a0\u00a0\u00a015 |\u00a0\u00a0\u00a0377 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0610 |<\/div>\n
      |\u00a0\u00a0\u00a016 |\u00a0\u00a0\u00a0610 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0987 |<\/div>\n
      |\u00a0\u00a0\u00a017 |\u00a0\u00a0\u00a0987 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01597 |<\/div>\n
      |\u00a0\u00a0\u00a018 |\u00a0\u00a01597 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02584 |<\/div>\n
      |\u00a0\u00a0\u00a019 |\u00a0\u00a02584 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a04181 |<\/div>\n
      |\u00a0\u00a0\u00a020 |\u00a0\u00a04181 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06765 |<\/div>\n
      +——+——-+————+<\/div>\n<\/div>\n
      <\/div>\n
      <\/div>\n
      \u53e6\u5916\u4e00\u4e2a\u4f8b\u5b50\uff0c\u65e5\u671f\u5e8f\u5217\u3002\u6709\u4e00\u4e2a\u9700\u6c42\uff0c\u9700\u8981\u6309\u5929\u5206\u7ec4\uff0c\u67e5\u8be2\u6bcf\u5929\u7684\u9500\u552e\u603b\u989d\uff0c\u4f20\u7edf\u67e5\u8be2\u65b9\u6cd5\uff0c\u4f7f\u7528group by\uff0c\u5982\u4e0b\uff1a<\/div>\n
      \n
      SELECT order_date, SUM(price) AS sales<\/div>\n
      FROM sales<\/div>\n
      GROUP BY order_date;<\/div>\n
      +————+———+<\/div>\n
      | order_date | sales\u00a0\u00a0\u00a0|<\/div>\n
      +————+———+<\/div>\n
      | 2020-02-01 |\u00a0\u00a0500.49 |<\/div>\n
      | 2020-02-02 | 1249.00 |<\/div>\n
      | 2020-02-04 | 1199.00 |<\/div>\n
      | 2020-02-06 | 1319.40 |<\/div>\n
      | 2020-02-07 |\u00a0\u00a0609.00 |<\/div>\n
      +————+———+<\/div>\n<\/div>\n
      <\/div>\n
      \u8fd9\u4e2a\u65b9\u5f0f\u6709\u4e00\u4e2a\u95ee\u9898\uff0c\u5047\u5982\u6709\u4e00\u5929\u6ca1\u6709\u5356\u51fa\u5546\u54c1\uff0c\u90a3\u4e48\u90a3\u5929\u7684\u8bb0\u5f55\u5c31\u6ca1\u6709\uff0c\u6bd4\u5982\u4e0a\u97622020-02-03\uff0c2020-02-05\u8fd9\u4e24\u5929\u5c31\u6ca1\u6709\u6570\u636e\u3002<\/div>\n
      <\/div>\n
      \u4f7f\u7528\u9012\u5f52CTE\uff0c\u5c31\u4e0d\u4f1a\u6709\u8fd9\u4e2a\u95ee\u9898\uff0c\u5982\u4e0b\uff1a<\/div>\n
      \n
      WITH RECURSIVE dates(date) AS (<\/div>\n
      \u00a0\u00a0\u00a0SELECT \\’2020-02-01\\’<\/div>\n
      \u00a0\u00a0\u00a0UNION ALL<\/div>\n
      \u00a0\u00a0\u00a0SELECT date + INTERVAL 1 DAY<\/div>\n
      \u00a0\u00a0\u00a0FROM dates<\/div>\n
      \u00a0\u00a0\u00a0WHERE date < \\’2020-02-07\\’ )<\/div>\n
      SELECT dates.date, COALESCE(SUM(price), 0) sales<\/div>\n
      FROM dates LEFT JOIN sales ON dates.date = sales.order_date<\/div>\n
      GROUP BY dates.date;<\/div>\n
      +————+———+<\/div>\n
      | date\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| sales\u00a0\u00a0\u00a0|<\/div>\n
      +————+———+<\/div>\n
      | 2020-02-01 |\u00a0\u00a0500.49 |<\/div>\n
      | 2020-02-02 | 1249.00 |<\/div>\n
      | 2020-02-03 |\u00a0\u00a0\u00a0\u00a00.00 |<\/div>\n
      | 2020-02-04 | 1199.00 |<\/div>\n
      | 2020-02-05 |\u00a0\u00a0\u00a0\u00a00.00 |<\/div>\n
      | 2020-02-06 | 1319.40 |<\/div>\n
      | 2020-02-07 |\u00a0\u00a0609.00 |<\/div>\n
      +————+———+<\/div>\n<\/div>\n
      <\/div>\n
      2.3 \u5c42\u6b21\u6570\u636e\u904d\u5386<\/div>\n
      <\/div>\n
      \u516c\u53f8\u7684\u7ec4\u7ec7\u67b6\u6784\u3001\u6587\u4ef6\u5939\u76ee\u5f55\u3001\u5bb6\u65cf\u6210\u5458\u5173\u7cfb\u7b49\u7b49\uff0c\u90fd\u662f\u5c42\u6b21\u5173\u7cfb\u7684\u6570\u636e\u3002\u4ee5\u516c\u53f8\u5458\u5de5\u4e0a\u4e0b\u7ea7\u5173\u7cfb\u4e3a\u4f8b\uff0c\u6765\u8bf4\u660e\u4f7f\u7528\u9012\u5f52CTE\u5b9e\u73b0\u5c42\u6b21\u6570\u636e\u7684\u904d\u5386\u3002\u539f\u59cb\u6570\u636e\u5982\u4e0b\uff1a<\/div>\n
      \n
      # create the table<\/div>\n
      CREATE TABLE orgchart(<\/div>\n
      id INT PRIMARY KEY,<\/div>\n
      name VARCHAR(20),<\/div>\n
      role VARCHAR(20),<\/div>\n
      manager_id INT,<\/div>\n
      FOREIGN KEY (manager_id) REFERENCES orgchart(id));<\/div>\n
      <\/div>\n
      <\/div>\n
      # insert the rows<\/div>\n
      INSERT INTO orgchart VALUES(1,\\’Matthew\\’,\\’CEO\\’,NULL),<\/div>\n
      (2,\\’Caroline\\’,\\’CFO\\’,1),(3,\\’Tom\\’,\\’CTO\\’,1),<\/div>\n
      (4,\\’Sam\\’,\\’Treasurer\\’,2),(5,\\’Ann\\’,\\’Controller\\’,2),<\/div>\n
      (6,\\’Anthony\\’,\\’Dev Director\\’,3),(7,\\’Lousie\\’,\\’Sys Admin\\’,3),<\/div>\n
      (8,\\’Travis\\’,\\’Senior DBA\\’,3),(9,\\’John\\’,\\’Developer\\’,6),<\/div>\n
      (10,\\’Jennifer\\’,\\’Developer\\’,6),(11,\\’Maria\\’,\\’Junior DBA\\’,8);<\/div>\n
      <\/div>\n
      <\/div>\n
      # let\\’s see the table, The CEO has no manager, so the manager_id is set to NULL<\/div>\n
      SELECT * FROM orgchat;<\/div>\n
      +—-+———-+————–+————+<\/div>\n
      | id | name\u00a0\u00a0\u00a0\u00a0\u00a0| role\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| manager_id |<\/div>\n
      +—-+———-+————–+————+<\/div>\n
      |\u00a0\u00a01 | Matthew\u00a0\u00a0| CEO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NULL |<\/div>\n
      |\u00a0\u00a02 | Caroline | CFO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01 |<\/div>\n
      |\u00a0\u00a03 | Tom\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| CTO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01 |<\/div>\n
      |\u00a0\u00a04 | Sam\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| Treasurer\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02 |<\/div>\n
      |\u00a0\u00a05 | Ann\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| Controller\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02 |<\/div>\n
      |\u00a0\u00a06 | Anthony\u00a0\u00a0| Dev Director |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03 |<\/div>\n
      |\u00a0\u00a07 | Lousie\u00a0\u00a0\u00a0| Sys Admin\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03 |<\/div>\n
      |\u00a0\u00a08 | Travis\u00a0\u00a0\u00a0| Senior DBA\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03 |<\/div>\n
      |\u00a0\u00a09 | John\u00a0\u00a0\u00a0\u00a0\u00a0| Developer\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06 |<\/div>\n
      | 10 | Jennifer | Developer\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06 |<\/div>\n
      | 11 | Maria\u00a0\u00a0\u00a0\u00a0| Junior DBA\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a08 |<\/div>\n
      +—-+———-+————–+————+<\/div>\n<\/div>\n
      <\/div>\n
      \u4f7f\u7528CTE\u9012\u5f52\u904d\u5386\u8fd9\u79cd\u5c42\u6b21\u7ed3\u6784\uff0c\u5982\u4e0b\uff1a<\/div>\n
      \n
      # find the reporting chain for all the employees<\/div>\n
      mysql> WITH RECURSIVE reporting_chain(id, name, path) AS (<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT id, name, CAST(name AS CHAR(100))<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM org_chart<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE manager_id IS NULL<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0UNION ALL<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT oc.id, oc.name, CONCAT(rc.path,\\’ -> \\’,oc.name)<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM reporting_chain rc JOIN org_chart oc ON rc.id=oc.manager_id)<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT * FROM reporting_chain;<\/div>\n
      +——+———-+—————————————+<\/div>\n
      | id\u00a0\u00a0\u00a0| name\u00a0\u00a0\u00a0\u00a0\u00a0| path\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      +——+———-+—————————————+<\/div>\n
      |\u00a0\u00a0\u00a0\u00a01 | Matthew\u00a0\u00a0| Matthew\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      |\u00a0\u00a0\u00a0\u00a02 | Caroline | Matthew -> Caroline\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      |\u00a0\u00a0\u00a0\u00a03 | Tom\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| Matthew -> Tom\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      |\u00a0\u00a0\u00a0\u00a04 | Sam\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| Matthew -> Caroline -> Sam\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      |\u00a0\u00a0\u00a0\u00a05 | Ann\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| Matthew -> Caroline -> Ann\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      |\u00a0\u00a0\u00a0\u00a06 | Anthony\u00a0\u00a0| Matthew -> Tom -> Anthony\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      |\u00a0\u00a0\u00a0\u00a07 | Lousie\u00a0\u00a0\u00a0| Matthew -> Tom -> Lousie\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      |\u00a0\u00a0\u00a0\u00a08 | Travis\u00a0\u00a0\u00a0| Matthew -> Tom -> Travis\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      |\u00a0\u00a0\u00a0\u00a09 | John\u00a0\u00a0\u00a0\u00a0\u00a0| Matthew -> Tom -> Anthony -> John\u00a0\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      |\u00a0\u00a0\u00a010 | Jennifer | Matthew -> Tom -> Anthony -> Jennifer |<\/div>\n
      |\u00a0\u00a0\u00a011 | Maria\u00a0\u00a0\u00a0\u00a0| Matthew -> Tom -> Travis -> Maria\u00a0\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      +——+———-+—————————————+<\/div>\n<\/div>\n
      <\/div>\n
      \u8fd9\u91cc\u6bd4\u8f83\u5173\u952e\u7684\u4e00\u70b9\u662f\u4f7f\u7528\u4e86 CAST \u51fd\u6570\u5728CTE\u7684\u79cd\u5b50\u6210\u5458\u91cc\uff0c\u5982\u679c\u4e0d\u4f7f\u7528CAST\u51fd\u6570\uff0c\u5219\u4f1a\u62a5\u9519\uff0c\u5982\u4e0b\uff1a<\/div>\n
      \n
      mysql> WITH RECURSIVE reporting_chain(id, name, path) AS (<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT id, name, name<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM org_chart<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE manager_id IS NULL<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0UNION ALL<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT oc.id, oc.name, CONCAT(rc.path,\\’ -> \\’,oc.name)<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM reporting_chain rc JOIN org_chart oc ON rc.id=oc.manager_id)<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT * FROM reporting_chain;<\/div>\n
      ERROR 1406 (22001): Data too long for column \\’path\\’ at row 1<\/div>\n<\/div>\n
      \u4e0a\u9762\u8fd9\u4e2aSQL\u8bed\u6cd5\u4e0a\u662f\u6b63\u786e\u7684\uff0c\u4f46\u662f\u95ee\u9898\u5728\u4e8epath\u5b57\u6bb5\u7684\u7c7b\u578b\u7531\u975e\u9012\u5f52\u7684SELECT\u51b3\u5b9a\uff0c\u6240\u4ee5\u5b83\u662fCHAR(7)\uff0c\u4e5f\u5c31\u662f\u5b57\u7b26\u4e32Matthew\u7684\u957f\u5ea6\uff0c\u6240\u4ee5\u5728CTE\u9012\u5f52\u8c03\u7528\u4e2d\uff0c\u5c06\u4f1a\u5bfc\u81f4\u4e00\u4e2a\u5b57\u7b26\u4e32\u622a\u65ad\u7684\u62a5\u9519\u3002<\/div>\n
      <\/div>\n
      \u66f4\u8fdb\u4e00\u6b65\uff0c\u6211\u4eec\u6253\u5370\u5c42\u7ea7\u7684\u6df1\u5ea6level\uff0c\u5982\u4e0b\uff1a<\/div>\n
      \n
      mysql> WITH RECURSIVE reporting_chain(id, name, path, level) AS (<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT id, name, CAST(name AS CHAR(100)), 1<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM org_chart<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE manager_id IS NULL<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0UNION ALL<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT oc.id, oc.name, CONCAT(rc.path,\\’ -> \\’,oc.name), rc.level+1<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM reporting_chain rc JOIN org_chart oc ON rc.id=oc.manager_id)<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT * FROM reporting_chain ORDER BY level;<\/div>\n
      +——+———-+—————————————+——-+<\/div>\n
      | id\u00a0\u00a0\u00a0| name\u00a0\u00a0\u00a0\u00a0\u00a0| path\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| level |<\/div>\n
      +——+———-+—————————————+——-+<\/div>\n
      |\u00a0\u00a0\u00a0\u00a01 | Matthew\u00a0\u00a0| Matthew\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a01 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a02 | Caroline | Matthew -> Caroline\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a02 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a03 | Tom\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| Matthew -> Tom\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a02 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a04 | Sam\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| Matthew -> Caroline -> Sam\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a03 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a05 | Ann\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| Matthew -> Caroline -> Ann\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a03 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a06 | Anthony\u00a0\u00a0| Matthew -> Tom -> Anthony\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a03 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a07 | Lousie\u00a0\u00a0\u00a0| Matthew -> Tom -> Lousie\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a03 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a08 | Travis\u00a0\u00a0\u00a0| Matthew -> Tom -> Travis\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a03 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a09 | John\u00a0\u00a0\u00a0\u00a0\u00a0| Matthew -> Tom -> Anthony -> John\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a04 |<\/div>\n
      |\u00a0\u00a0\u00a010 | Jennifer | Matthew -> Tom -> Anthony -> Jennifer |\u00a0\u00a0\u00a0\u00a0\u00a04 |<\/div>\n
      |\u00a0\u00a0\u00a011 | Maria\u00a0\u00a0\u00a0\u00a0| Matthew -> Tom -> Travis -> Maria\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a04 |<\/div>\n
      +——+———-+—————————————+——-+<\/div>\n<\/div>\n
      <\/div>\n
      <\/div>\n
      \u6765\u770b\u4e00\u4e2a\u66f4\u590d\u6742\u7684\u6811\u5f62\u7ed3\u6784\uff0c\u5bb6\u8c31\u3002\u6570\u636e\u4e2d\u5305\u542b\u7956\u7236\u6bcd\u3001\u7236\u6bcd\u548c\u5b69\u5b50\uff0c\u539f\u59cb\u6570\u636e\u5982\u4e0b\u3002<\/div>\n
      \n
      CREATE TABLE genealogy(<\/div>\n
      id INT PRIMARY KEY,<\/div>\n
      name VARCHAR(20),<\/div>\n
      father_id INT,<\/div>\n
      mother_id INT,<\/div>\n
      FOREIGN KEY(father_id) REFERENCES genealogy(id),<\/div>\n
      FOREIGN KEY(mother_id) REFERENCES genealogy(id));<\/div>\n
      <\/div>\n
      <\/div>\n
      # populate the table<\/div>\n
      INSERT INTO genealogy VALUES(1,\\’Maria\\’,NULL,NULL),<\/div>\n
      (2,\\’Tom\\’,NULL,NULL),(3,\\’Robert\\’,NULL,NULL),<\/div>\n
      (4,\\’Claire\\’,NULL,NULL),(5,\\’John\\’,2,1),<\/div>\n
      (6,\\’Jennifer\\’,2,1),(7,\\’Sam\\’,3,4),<\/div>\n
      (8,\\’James\\’,7,6);<\/div>\n
      <\/div>\n
      <\/div>\n
      SELECT * FROM genealogy;<\/div>\n
      +—-+———-+———–+———–+<\/div>\n
      | id | name\u00a0\u00a0\u00a0\u00a0\u00a0| father_id | mother_id |<\/div>\n
      +—-+———-+———–+———–+<\/div>\n
      |\u00a0\u00a01 | Maria\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NULL |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NULL |<\/div>\n
      |\u00a0\u00a02 | Tom\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NULL |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NULL |<\/div>\n
      |\u00a0\u00a03 | Robert\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NULL |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NULL |<\/div>\n
      |\u00a0\u00a04 | Claire\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NULL |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NULL |<\/div>\n
      |\u00a0\u00a05 | John\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01 |<\/div>\n
      |\u00a0\u00a06 | Jennifer |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01 |<\/div>\n
      |\u00a0\u00a07 | Sam\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a04 |<\/div>\n
      |\u00a0\u00a08 | James\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a07 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06 |<\/div>\n
      +—-+———-+———–+———–+<\/div>\n<\/div>\n
      <\/div>\n
      \u901a\u8fc7CTE\u9012\u5f52\uff0c\u6211\u4eec\u53ef\u4ee5\u67e5\u8be2\u67d0\u4e00\u4e2a\u4eba\u7684\u7956\u5148\u53ca\u4e0e\u4ed6\u7684\u5173\u7cfb\uff0c\u5982\u4e0b\uff1a<\/div>\n
      \n
      mysql> WITH RECURSIVE ancestors AS (<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT *, CAST(\\’son\\’ AS CHAR(20)) AS relationship, 0 level<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM genealogy<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE name=\\’James\\’<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0UNION ALL<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT g.*, CASE WHEN g.id=a.father_id AND level=0 THEN \\’father\\’<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHEN g.id=a.mother_id AND level=0 THEN \\’mother\\’<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHEN g.id=a.father_id AND level=1 THEN \\’grandfather\\’<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHEN g.id=a.mother_id AND level=1 THEN \\’grandmother\\’<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0END,<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0level+1<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM genealogy g, ancestors a<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE g.id=a.father_id OR g.id=a.mother_id)<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT * FROM ancestors;<\/div>\n
      +——+———-+———–+———–+————–+——-+<\/div>\n
      | id\u00a0\u00a0\u00a0| name\u00a0\u00a0\u00a0\u00a0\u00a0| father_id | mother_id | relationship | level |<\/div>\n
      +——+———-+———–+———–+————–+——-+<\/div>\n
      |\u00a0\u00a0\u00a0\u00a08 | James\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a07 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06 | son\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a00 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a06 | Jennifer |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01 | mother\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a01 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a07 | Sam\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a04 | father\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a01 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a01 | Maria\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NULL |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NULL | grandmother\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a02 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a02 | Tom\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NULL |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NULL | grandfather\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a02 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a03 | Robert\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NULL |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NULL | grandfather\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a02 |<\/div>\n
      |\u00a0\u00a0\u00a0\u00a04 | Claire\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NULL |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NULL | grandmother\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a02 |<\/div>\n
      +——+———-+———–+———–+————–+——-+<\/div>\n<\/div>\n
      <\/div>\n
      2.4 \u56fe\u7ed3\u6784\u904d\u5386<\/div>\n
      <\/div>\n
      \u770b\u4e00\u4e2a\u4ea4\u901a\u7ebf\u8def\u7684\u4f8b\u5b50\uff0c\u521b\u5efa\u4e00\u4e2a\u8868\uff0c\u5305\u542b\u5404\u4e2a\u7ad9\u70b9\u4e4b\u95f4\u7684\u7ebf\u8def\uff0c\u4ee5\u53ca\u5b83\u4eec\u4e4b\u95f4\u7684\u8ddd\u79bb\uff0c\u539f\u59cb\u6570\u636e\u5982\u4e0b\uff1a<\/div>\n
      \n
      CREATE TABLE train_route(<\/div>\n
      id INT PRIMARY KEY,<\/div>\n
      origin VARCHAR(20),<\/div>\n
      destination VARCHAR(20),<\/div>\n
      distance INT);<\/div>\n
      <\/div>\n
      <\/div>\n
      # populate the table<\/div>\n
      INSERT INTO train_route VALUES(1,\\’MILAN\\’,\\’TURIN\\’,150),<\/div>\n
      (2,\\’TURIN\\’,\\’MILAN\\’,150),(3,\\’MILAN\\’,\\’VENICE\\’,250),<\/div>\n
      (4,\\’VENICE\\’,\\’MILAN\\’,250),(5,\\’MILAN\\’,\\’GENOA\\’,200),<\/div>\n
      (6,\\’MILAN\\’,\\’ROME\\’,600),(7,\\’ROME\\’,\\’MILAN\\’,600),<\/div>\n
      (8,\\’MILAN\\’,\\’FLORENCE\\’,380),(9,\\’TURIN\\’,\\’GENOA\\’,160),<\/div>\n
      (10,\\’GENOA\\’,\\’TURIN\\’,160),(11,\\’FLORENCE\\’,\\’VENICE\\’,550),<\/div>\n
      (12,\\’FLORENCE\\’,\\’ROME\\’,220),(13,\\’ROME\\’,\\’FLORENCE\\’,220),<\/div>\n
      (14,\\’GENOA\\’,\\’ROME\\’,500),(15,\\’ROME\\’,\\’NAPLES\\’,210),<\/div>\n
      (16,\\’NAPLES\\’,\\’VENICE\\’,800);<\/div>\n
      <\/div>\n
      <\/div>\n
      SELECT * FROM train_route;<\/div>\n
      +—-+———-+————-+———-+<\/div>\n
      | id | origin\u00a0\u00a0\u00a0| destination | distance |<\/div>\n
      +—-+———-+————-+———-+<\/div>\n
      |\u00a0\u00a01 | MILAN\u00a0\u00a0\u00a0\u00a0| TURIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0150 |<\/div>\n
      |\u00a0\u00a02 | TURIN\u00a0\u00a0\u00a0\u00a0| MILAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0150 |<\/div>\n
      |\u00a0\u00a03 | MILAN\u00a0\u00a0\u00a0\u00a0| VENICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0250 |<\/div>\n
      |\u00a0\u00a04 | VENICE\u00a0\u00a0\u00a0| MILAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0250 |<\/div>\n
      |\u00a0\u00a05 | MILAN\u00a0\u00a0\u00a0\u00a0| GENOA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0200 |<\/div>\n
      |\u00a0\u00a06 | MILAN\u00a0\u00a0\u00a0\u00a0| ROME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0600 |<\/div>\n
      |\u00a0\u00a07 | ROME\u00a0\u00a0\u00a0\u00a0\u00a0| MILAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0600 |<\/div>\n
      |\u00a0\u00a08 | MILAN\u00a0\u00a0\u00a0\u00a0| FLORENCE\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0380 |<\/div>\n
      |\u00a0\u00a09 | TURIN\u00a0\u00a0\u00a0\u00a0| GENOA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0160 |<\/div>\n
      | 10 | GENOA\u00a0\u00a0\u00a0\u00a0| TURIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0160 |<\/div>\n
      | 11 | FLORENCE | VENICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0550 |<\/div>\n
      | 12 | FLORENCE | ROME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0220 |<\/div>\n
      | 13 | ROME\u00a0\u00a0\u00a0\u00a0\u00a0| FLORENCE\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0220 |<\/div>\n
      | 14 | GENOA\u00a0\u00a0\u00a0\u00a0| ROME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0500 |<\/div>\n
      | 15 | ROME\u00a0\u00a0\u00a0\u00a0\u00a0| NAPLES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0210 |<\/div>\n
      | 16 | NAPLES\u00a0\u00a0\u00a0| VENICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0800 |<\/div>\n
      +—-+———-+————-+———-+<\/div>\n<\/div>\n
      <\/div>\n
      \u8fd4\u56de\u8d77\u70b9\u4e3a Milan\uff0c\u53ef\u5230\u8fbe\u7684\u6240\u6709\u76ee\u7684\u5730\uff0c\u5982\u4e0b\uff1a<\/div>\n
      \n
      mysql> WITH RECURSIVE train_destination AS (<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT origin AS dest<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM train_route<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE origin=\\’MILAN\\’<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0UNION<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT tr.destination<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM train_route tr<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0JOIN train_destination td ON td.dest=tr.origin)<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT * from train_destination;<\/div>\n
      +———-+<\/div>\n
      | dest\u00a0\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      +———-+<\/div>\n
      | MILAN\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      | TURIN\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      | VENICE\u00a0\u00a0\u00a0|<\/div>\n
      | GENOA\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      | ROME\u00a0\u00a0\u00a0\u00a0\u00a0|<\/div>\n
      | FLORENCE |<\/div>\n
      | NAPLES\u00a0\u00a0\u00a0|<\/div>\n
      +———-+<\/div>\n<\/div>\n
      <\/div>\n
      \u4ece\u4e00\u4e2a\u5730\u70b9\u51fa\u53d1\uff0c\u5230\u8fbe\u53e6\u5916\u4e00\u4e2a\u5730\u70b9\u53ef\u4ee5\u6709\u591a\u6761\u8def\u5f84\uff0c\u6bd4\u5982\u8d77\u70b9\u4e3aMilan\uff0c\u7ec8\u70b9\u4efb\u610f\uff0c\u6211\u4eec\u770b\u4e0b\u6709\u591a\u5c11\u6761\u8def\u7ebf\uff0c\u6bcf\u6761\u8def\u7ebf\u7684\u8ddd\u79bb\u662f\u591a\u5c11\uff0c\u5982\u4e0b\uff1a<\/div>\n
      \n
      mysql> WITH RECURSIVE paths (cur_path, cur_dest, tot_distance) AS (<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT CAST(origin AS CHAR(100)), CAST(origin AS CHAR(100)), 0<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM train_route<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE origin=\\’MILAN\\’<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0UNION<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT CONCAT(paths.cur_path, \\’ -> \\’, train_route.destination), train_route.destination, paths.tot_distance+train_route.distance<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM paths, train_route<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE paths.cur_dest = train_route.origin<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND\u00a0\u00a0NOT FIND_IN_SET(train_route.destination, REPLACE(paths.cur_path,\\’ -> \\’,\\’,\\’) ) )<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT * FROM paths;<\/div>\n
      +——————————————————-+———-+————–+<\/div>\n
      | cur_path\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| cur_dest | tot_distance |<\/div>\n
      +——————————————————-+———-+————–+<\/div>\n
      | MILAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| MILAN\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00 |<\/div>\n
      | MILAN -> TURIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| TURIN\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0150 |<\/div>\n
      | MILAN -> VENICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| VENICE\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0250 |<\/div>\n
      | MILAN -> GENOA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| GENOA\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0200 |<\/div>\n
      | MILAN -> ROME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| ROME\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0600 |<\/div>\n
      | MILAN -> FLORENCE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| FLORENCE |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0380 |<\/div>\n
      | MILAN -> TURIN -> GENOA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| GENOA\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0310 |<\/div>\n
      | MILAN -> GENOA -> TURIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| TURIN\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0360 |<\/div>\n
      | MILAN -> GENOA -> ROME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| ROME\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0700 |<\/div>\n
      | MILAN -> ROME -> FLORENCE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| FLORENCE |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0820 |<\/div>\n
      | MILAN -> ROME -> NAPLES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| NAPLES\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0810 |<\/div>\n
      | MILAN -> FLORENCE -> VENICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| VENICE\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0930 |<\/div>\n
      | MILAN -> FLORENCE -> ROME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| ROME\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0600 |<\/div>\n
      | MILAN -> TURIN -> GENOA -> ROME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| ROME\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0810 |<\/div>\n
      | MILAN -> GENOA -> ROME -> FLORENCE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| FLORENCE |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0920 |<\/div>\n
      | MILAN -> GENOA -> ROME -> NAPLES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| NAPLES\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0910 |<\/div>\n
      | MILAN -> ROME -> FLORENCE -> VENICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| VENICE\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01370 |<\/div>\n
      | MILAN -> ROME -> NAPLES -> VENICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| VENICE\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01610 |<\/div>\n
      | MILAN -> FLORENCE -> ROME -> NAPLES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| NAPLES\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0810 |<\/div>\n
      | MILAN -> TURIN -> GENOA -> ROME -> FLORENCE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| FLORENCE |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01030 |<\/div>\n
      | MILAN -> TURIN -> GENOA -> ROME -> NAPLES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| NAPLES\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01020 |<\/div>\n
      | MILAN -> GENOA -> ROME -> FLORENCE -> VENICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| VENICE\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01470 |<\/div>\n
      | MILAN -> GENOA -> ROME -> NAPLES -> VENICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| VENICE\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01710 |<\/div>\n
      | MILAN -> FLORENCE -> ROME -> NAPLES -> VENICE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| VENICE\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01610 |<\/div>\n
      | MILAN -> TURIN -> GENOA -> ROME -> FLORENCE -> VENICE | VENICE\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01580 |<\/div>\n
      | MILAN -> TURIN -> GENOA -> ROME -> NAPLES -> VENICE\u00a0\u00a0\u00a0| VENICE\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01820 |<\/div>\n
      +——————————————————-+———-+————–+<\/div>\n<\/div>\n
      <\/div>\n
      \u4e5f\u53ef\u4ee5\u627e\u51fa\u4e00\u4e2a\u8d77\u70b9\u4e0e\u4e00\u4e2a\u7ec8\u70b9\u4e4b\u95f4\u7684\u6700\u77ed\u8def\u5f84\uff0c\u6bd4\u5982\u8d77\u70b9\u4e3aMILAN\uff0c\u7ec8\u70b9\u4e3aNAPLES\uff0c\u5982\u4e0b\uff1a<\/div>\n
      \n
      # shortest path from MILAN to NAPLES<\/div>\n
      mysql> WITH RECURSIVE paths (cur_path, cur_dest, tot_distance) AS (<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT CAST(origin AS CHAR(100)), CAST(origin AS CHAR(100)), 0 FROM train_route WHERE origin=\\’MILAN\\’<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0UNION<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT CONCAT(paths.cur_path, \\’ -> \\’, train_route.destination), train_route.destination, paths.tot_distance+train_route.distance<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM paths, train_route<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE paths.cur_dest = train_route.origin AND NOT FIND_IN_SET(train_route.destination, REPLACE(paths.cur_path,\\’ -> \\’,\\’,\\’) ) )<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT * FROM paths<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE cur_dest=\\’NAPLES\\’<\/div>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ORDER BY tot_distance ASC LIMIT 1<\/div>\n
      +————————-+———-+————–+<\/div>\n
      | cur_path\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| cur_dest | tot_distance |<\/div>\n
      +————————-+———-+————–+<\/div>\n
      | MILAN -> ROME -> NAPLES | NAPLES\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0810 |<\/div>\n
      +————————-+———-+————–+<\/div>\n<\/div>\n
      <\/div>\n
      <\/div>\n
      \u4e09\u3001\u9012\u5f52CTE\u4f7f\u7528\u9650\u5236<\/div>\n
      <\/div>\n
      \u4e4b\u95f4\u63d0\u5230\u8fc7\uff0cCTE\u9012\u5f52\u6709\u9012\u5f52\u6df1\u5ea6\u548c\u6267\u884c\u65f6\u95f4\u7684\u9650\u5236\uff0c\u9664\u6b64\u4e4b\u5916\uff0c\u9012\u5f52CTE\u7684SELECT\u4e0d\u80fd\u5305\u542b\u5982\u4e0b\u8bed\u53e5\uff1a<\/div>\n