{"id":1586,"date":"2022-11-18T23:30:22","date_gmt":"2022-11-18T15:30:22","guid":{"rendered":"https:\/\/qaqaq.top\/?p=1586"},"modified":"2022-11-27T12:40:58","modified_gmt":"2022-11-27T04:40:58","slug":"mysql8-0%e6%96%b0%e7%89%b9%e6%80%a7_%e7%aa%97%e5%8f%a3%e5%87%bd%e6%95%b0%e7%9a%84%e4%bd%bf%e7%94%a8%e3%80%81%e5%85%ac%e7%94%a8%e8%a1%a8%e8%a1%a8%e8%be%be%e5%bc%8f","status":"publish","type":"post","link":"https:\/\/qaqaq.top\/?p=1586","title":{"rendered":"MySQL8.0\u65b0\u7279\u6027_\u7a97\u53e3\u51fd\u6570\u7684\u4f7f\u7528\u3001\u516c\u7528\u8868\u8868\u8fbe\u5f0f"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>#  \u7b2c18\u7ae0_MySQL8.0\u7684\u5176\u5b83\u65b0\u7279\u6027\r\n\r\nCREATE DATABASE dbtest18;\r\n\r\nUSE dbtest18;\r\n\r\n# \u4e00\u3001 \u7a97\u53e3\u51fd\u6570\r\n\r\n# 1.1 \u6f14\u793a\u7a97\u53e3\u51fd\u6570\u7684\u6548\u679c\r\nCREATE TABLE sales(\r\n    id INT PRIMARY KEY AUTO_INCREMENT,\r\n    city VARCHAR(15),\r\n    county VARCHAR(15),\r\n    sales_value DECIMAL\r\n);\r\n\r\nINSERT INTO sales(city,county,sales_value)\r\nVALUES\r\n    ('\u5317\u4eac','\u6d77\u6dc0',10.00),\r\n    ('\u5317\u4eac','\u671d\u9633',20.00),\r\n    ('\u4e0a\u6d77','\u9ec4\u57d4',30.00),\r\n    ('\u4e0a\u6d77','\u957f\u5b81',10.00);\r\n\r\nSELECT * FROM sales;\r\n\r\n# \u9700\u6c42\uff1a\u73b0\u5728\u8ba1\u7b97\u8fd9\u4e2a\u7f51\u7ad9\u5728\u6bcf\u4e2a\u57ce\u5e02\u7684\u9500\u552e\u603b\u989d\u3001\u5728\u5168\u56fd\u7684\u9500\u552e\u603b\u989d\u3001\r\n# \u6bcf\u4e2a\u533a\u7684\u9500\u552e\u989d\u5360\u6240\u5728\u57ce\u5e02\u9500\u552e\u989d\u4e2d\u7684\u6bd4\u7387\uff0c\u4ee5\u53ca\u5360\u603b\u9500\u552e\u989d\u4e2d\u7684\u6bd4\u7387\u3002\r\n\r\n#\u5b9e\u73b0\u65b9\u5f0f1\uff1a\r\n\r\nCREATE TEMPORARY TABLE a        -- \u521b\u5efa\u4e34\u65f6\u8868\r\nSELECT SUM(sales_value) AS sales_value      -- \u8ba1\u7b97\u603b\u8ba1\u91d1\u989d\r\nFROM sales;\r\n\r\nSELECT * FROM a;\r\n\r\nCREATE TEMPORARY TABLE b        -- \u521b\u5efa\u4e34\u65f6\u8868\r\nSELECT city,SUM(sales_value) AS sales_value     -- \u8ba1\u7b97\u57ce\u5e02\u9500\u552e\u5408\u8ba1\r\nFROM sales\r\nGROUP BY city;\r\n\r\nSELECT * FROM b;\r\n\r\nSELECT s.city AS \u57ce\u5e02,s.county AS \u533a,s.sales_value AS \u533a\u9500\u552e\u989d,\r\n       b.sales_value AS \u5e02\u9500\u552e\u989d,s.sales_value\/b.sales_value AS \u5e02\u6bd4\u7387,\r\n       a.sales_value AS \u603b\u9500\u552e\u989d,s.sales_value\/a.sales_value AS \u603b\u6bd4\u7387\r\nFROM sales s\r\n    JOIN b ON (s.city=b.city)    -- \u8fde\u63a5\u5e02\u7edf\u8ba1\u7ed3\u679c\u4e34\u65f6\u8868\r\n    JOIN a                   -- \u8fde\u63a5\u603b\u8ba1\u91d1\u989d\u4e34\u65f6\u8868\r\nORDER BY s.city,s.county;\r\n\r\n#\u65b9\u5f0f2\uff1a\r\nSELECT city AS \u57ce\u5e02,county AS \u533a,sales_value AS \u533a\u9500\u552e\u989d,\r\n  SUM(sales_value) OVER(PARTITION BY city) AS \u5e02\u9500\u552e\u989d,     -- \u8ba1\u7b97\u5e02\u9500\u552e\u989d\r\n  sales_value\/SUM(sales_value) OVER(PARTITION BY city) AS \u5e02\u6bd4\u7387,\r\n  SUM(sales_value) OVER() AS \u603b\u9500\u552e\u989d,      -- \u8ba1\u7b97\u603b\u9500\u552e\u989d\r\n       sales_value\/SUM(sales_value) OVER() AS \u603b\u6bd4\u7387\r\nFROM sales\r\nORDER BY city,county;\r\n\r\n# 2. \u4ecb\u7ecd\u7a97\u53e3\u51fd\u6570\r\n\r\nCREATE TABLE employees\r\n    AS\r\n    SELECT * FROM atguigudb.employees;\r\n\r\nSELECT * FROM employees;\r\n\r\n#\u51c6\u5907\u5de5\u4f5c\r\n\r\nCREATE TABLE goods(\r\n    id INT PRIMARY KEY AUTO_INCREMENT,\r\n    category_id INT,\r\n    category VARCHAR(15),\r\n    NAME VARCHAR(30),\r\n    price DECIMAL(10,2),\r\n    stock INT,\r\n    upper_time DATETIME\r\n);\r\n\r\nINSERT INTO goods(category_id,category,NAME,price,stock,upper_time)\r\nVALUES\r\n    (1, '\u5973\u88c5\/\u5973\u58eb\u7cbe\u54c1', 'T\u6064', 39.90, 1000, '2020-11-10 00:00:00'),\r\n    (1, '\u5973\u88c5\/\u5973\u58eb\u7cbe\u54c1', '\u8fde\u8863\u88d9', 79.90, 2500, '2020-11-10 00:00:00'),\r\n    (1, '\u5973\u88c5\/\u5973\u58eb\u7cbe\u54c1', '\u536b\u8863', 89.90, 1500, '2020-11-10 00:00:00'),\r\n    (1, '\u5973\u88c5\/\u5973\u58eb\u7cbe\u54c1', '\u725b\u4ed4\u88e4', 89.90, 3500, '2020-11-10 00:00:00'),\r\n    (1, '\u5973\u88c5\/\u5973\u58eb\u7cbe\u54c1', '\u767e\u8936\u88d9', 29.90, 500, '2020-11-10 00:00:00'),\r\n    (1, '\u5973\u88c5\/\u5973\u58eb\u7cbe\u54c1', '\u5462\u7ed2\u5916\u5957', 399.90, 1200, '2020-11-10 00:00:00'),\r\n    (2, '\u6237\u5916\u8fd0\u52a8', '\u81ea\u884c\u8f66', 399.90, 1000, '2020-11-10 00:00:00'),\r\n    (2, '\u6237\u5916\u8fd0\u52a8', '\u5c71\u5730\u81ea\u884c\u8f66', 1399.90, 2500, '2020-11-10 00:00:00'),\r\n    (2, '\u6237\u5916\u8fd0\u52a8', '\u767b\u5c71\u6756', 59.90, 1500, '2020-11-10 00:00:00'),\r\n    (2, '\u6237\u5916\u8fd0\u52a8', '\u9a91\u884c\u88c5\u5907', 399.90, 3500, '2020-11-10 00:00:00'),\r\n    (2, '\u6237\u5916\u8fd0\u52a8', '\u8fd0\u52a8\u5916\u5957', 799.90, 500, '2020-11-10 00:00:00'),\r\n    (2, '\u6237\u5916\u8fd0\u52a8', '\u6ed1\u677f', 499.90, 1200, '2020-11-10 00:00:00');\r\n\r\nSELECT * FROM goods;\r\n\r\n# \u5e8f\u53f7\u51fd\u6570\r\n\r\n# 1.1 ROW_NUMBER()\u51fd\u6570\r\n# \u4e3e\u4f8b\uff1a\u67e5\u8be2 goods \u6570\u636e\u8868\u4e2d\u6bcf\u4e2a\u5546\u54c1\u5206\u7c7b\u4e0b\u4ef7\u683c\u964d\u5e8f\u6392\u5217\u7684\u5404\u4e2a\u5546\u54c1\u4fe1\u606f\u3002\r\n\r\nSELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,\r\n       id, category_id, category, NAME, price, stock\r\nFROM goods;\r\n\r\n# \u4e3e\u4f8b\uff1a\u67e5\u8be2 goods \u6570\u636e\u8868\u4e2d\u6bcf\u4e2a\u5546\u54c1\u5206\u7c7b\u4e0b\u4ef7\u683c\u6700\u9ad8\u76843\u79cd\u5546\u54c1\u4fe1\u606f\u3002\r\nSELECT *\r\nFROM (\r\n    SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,\r\n           id, category_id, category, NAME, price, stock\r\n    FROM goods) t\r\nWHERE row_num &lt;= 3;\r\n\r\n# 1.2 RANK()\u51fd\u6570\r\n# \u4e3e\u4f8b\uff1a\u4f7f\u7528RANK()\u51fd\u6570\u83b7\u53d6 goods \u6570\u636e\u8868\u4e2d\u5404\u7c7b\u522b\u7684\u4ef7\u683c\u4ece\u9ad8\u5230\u4f4e\u6392\u5e8f\u7684\u5404\u5546\u54c1\u4fe1\u606f\u3002\r\nSELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,\r\n       id, category_id, category, NAME, price, stock\r\nFROM goods;\r\n\r\n# 1.3 DENSE_RANK()\u51fd\u6570\r\n\r\n# \u4e3e\u4f8b\uff1a\u4f7f\u7528DENSE_RANK()\u51fd\u6570\u83b7\u53d6 goods \u6570\u636e\u8868\u4e2d\u5404\u7c7b\u522b\u7684\u4ef7\u683c\u4ece\u9ad8\u5230\u4f4e\u6392\u5e8f\u7684\u5404\u5546\u54c1\u4fe1\u606f\u3002\r\n\r\nSELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,\r\n       id, category_id, category, NAME, price, stock\r\nFROM goods;\r\n\r\n# 2. \u5206\u5e03\u51fd\u6570\r\n# 2.1 PERCENT_RANK()\u51fd\u6570\r\n\r\n# \u4e3e\u4f8b\uff1a\u8ba1\u7b97 goods \u6570\u636e\u8868\u4e2d\u540d\u79f0\u4e3a\u201c\u5973\u88c5\/\u5973\u58eb\u7cbe\u54c1\u201d\u7684\u7c7b\u522b\u4e0b\u7684\u5546\u54c1\u7684PERCENT_RANK\u503c\u3002\r\n\r\n#\u65b9\u5f0f1\uff1a\r\nSELECT RANK() OVER w AS r,\r\n       PERCENT_RANK() OVER w AS pr,\r\n       id, category_id, category, NAME, price, stock\r\nFROM goods\r\nWHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price DESC);\r\n\r\n#\u65b9\u5f0f2\uff1a\r\nSELECT RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r,\r\n       PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS pr,\r\n       id, category_id, category, NAME, price, stock\r\nFROM goods\r\nWHERE category_id = 1;\r\n\r\n# 2.2 CUME_DIST()\u51fd\u6570\r\n# \u4e3e\u4f8b\uff1a\u67e5\u8be2goods\u6570\u636e\u8868\u4e2d\u5c0f\u4e8e\u6216\u7b49\u4e8e\u5f53\u524d\u4ef7\u683c\u7684\u6bd4\u4f8b\u3002\r\nSELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd,\r\n       id, category, NAME, price\r\nFROM goods;\r\n\r\n# 3. \u524d\u540e\u51fd\u6570\r\n# 3.1 LAG(expr,n)\u51fd\u6570\r\n\r\n# \u4e3e\u4f8b\uff1a\u67e5\u8be2goods\u6570\u636e\u8868\u4e2d\u524d\u4e00\u4e2a\u5546\u54c1\u4ef7\u683c\u4e0e\u5f53\u524d\u5546\u54c1\u4ef7\u683c\u7684\u5dee\u503c\u3002\r\nSELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price\r\nFROM (\r\n    SELECT id, category, NAME, price,LAG(price,1) OVER w AS pre_price\r\n    FROM goods\r\n    WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;\r\n\r\n#\u5176\u4e2d\uff0c\u5b50\u67e5\u8be2\u5982\u4e0b\uff1a\r\nSELECT id, category, NAME, price,LAG(price,1) OVER (PARTITION BY category_id ORDER BY price) AS pre_price\r\nFROM goods;\r\n\r\n# 3.2 LEAD(expr,n)\u51fd\u6570\r\n\r\n# \u4e3e\u4f8b\uff1a\u67e5\u8be2goods\u6570\u636e\u8868\u4e2d\u540e\u4e00\u4e2a\u5546\u54c1\u4ef7\u683c\u4e0e\u5f53\u524d\u5546\u54c1\u4ef7\u683c\u7684\u5dee\u503c\u3002\r\n\r\nSELECT id, category, NAME, behind_price, price,behind_price- price AS diff_price\r\nFROM(\r\n    SELECT id, category, NAME, price,LEAD(price, 1) OVER w AS behind_price\r\n    FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;\r\n\r\n#\u5176\u4e2d\uff0c\u5b50\u67e5\u8be2\u4e3a\uff1a\r\nSELECT id, category, NAME, price,LEAD(price, 1) OVER (PARTITION BY category_id ORDER BY price) AS behind_price\r\n    FROM goods;\r\n\r\n# 4. \u9996\u5c3e\u51fd\u6570\r\n# 4.1 FIRST_VALUE(expr)\u51fd\u6570\r\n# \u4e3e\u4f8b\uff1a\u6309\u7167\u4ef7\u683c\u6392\u5e8f\uff0c\u67e5\u8be2\u7b2c1\u4e2a\u5546\u54c1\u7684\u4ef7\u683c\u4fe1\u606f\u3002\r\nSELECT id, category, NAME, price, stock,FIRST_VALUE(price) OVER (PARTITION BY category_id ORDER BY price) AS first_price\r\nFROM goods;\r\n\r\n# 4.2 LAST_VALUE(expr)\u51fd\u6570\r\n\r\n# 5. \u5176\u4ed6\u51fd\u6570\r\n# 5.1 NTH_VALUE(expr,n)\u51fd\u6570\r\n\r\n# \u4e3e\u4f8b\uff1a\u67e5\u8be2goods\u6570\u636e\u8868\u4e2d\u6392\u540d\u7b2c2\u548c\u7b2c3\u7684\u4ef7\u683c\u4fe1\u606f\u3002\r\nSELECT id, category, NAME, price,\r\n       NTH_VALUE(price,2) OVER (PARTITION BY category_id ORDER BY price) AS second_price,\r\n       NTH_VALUE(price,3) OVER (PARTITION BY category_id ORDER BY price) AS third_price\r\nFROM goods ;\r\n\r\n# 5.2 NTILE(n)\u51fd\u6570\r\n# \u4e3e\u4f8b\uff1a\u5c06goods\u8868\u4e2d\u7684\u5546\u54c1\u6309\u7167\u4ef7\u683c\u5206\u4e3a3\u7ec4\u3002\r\nSELECT NTILE(3) OVER (PARTITION BY category_id ORDER BY price) AS nt,id, category, NAME, price\r\nFROM goods;\r\n\r\n# \u4e8c\u3001\u65b0\u7279\u60272\uff1a\u5171\u7528\u8868\u8868\u8fbe\u5f0f\r\n\r\n# 2.1 \u666e\u901a\u5171\u7528\u8868\u8868\u8fbe\u5f0f\r\n# \u4e3e\u4f8b\uff1a\u67e5\u8be2\u5458\u5de5\u6240\u5728\u7684\u90e8\u95e8\u7684\u8be6\u7ec6\u4fe1\u606f\u3002\r\n\r\nCREATE TABLE departments\r\n    AS\r\n    SELECT * FROM atguigudb.departments;\r\n\r\n#\u5b50\u67e5\u8be2\u5b9e\u73b0\r\nSELECT * FROM departments\r\nWHERE department_id IN (\r\n    SELECT DISTINCT department_id\r\n    FROM employees\r\n    );\r\n\r\n#CTE\u5b9e\u73b0\r\nWITH cte_emp\r\nAS ( SELECT DISTINCT department_id FROM employees )\r\n\r\nSELECT *\r\nFROM departments d JOIN cte_emp e\r\nON d.department_id = e.department_id;\r\n\r\n# 2.2 \u9012\u5f52\u5171\u7528\u8868\u8868\u8fbe\u5f0f\r\n\r\n#\r\nSELECT * FROM employees;\r\n\r\n#\u4e3e\u4f8b\uff1a\u627e\u51fa\u516c\u53f8employees\u8868\u4e2d\u6240\u6709\u7684\u4e0b\u4e0b\u5c5e\u3002\r\nWITH RECURSIVE cte\r\nAS\r\n(\r\nSELECT employee_id,last_name,manager_id,1 AS n FROM employees WHERE employee_id = 100   -- \u79cd\u5b50\u67e5\u8be2\uff0c\u627e\u5230\u7b2c\u4e00\u4ee3\u9886\u5bfc\r\nUNION ALL\r\nSELECT a.employee_id,a.last_name,a.manager_id,n+1 FROM employees AS a JOIN cte\r\nON (a.manager_id = cte.employee_id)     -- \u9012\u5f52\u67e5\u8be2\uff0c\u627e\u51fa\u4ee5\u9012\u5f52\u516c\u7528\u8868\u8868\u8fbe\u5f0f\u7684\u4eba\u4e3a\u9886\u5bfc\u7684\u4eba\r\n)\r\nSELECT employee_id,last_name FROM cte WHERE n >= 3;\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-1586","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\/1586"}],"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=1586"}],"version-history":[{"count":1,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1586\/revisions"}],"predecessor-version":[{"id":1587,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1586\/revisions\/1587"}],"wp:attachment":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1586"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1586"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1586"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}