{"id":1527,"date":"2022-11-08T23:02:46","date_gmt":"2022-11-08T15:02:46","guid":{"rendered":"https:\/\/qaqaq.top\/?p=1527"},"modified":"2022-11-27T12:40:59","modified_gmt":"2022-11-27T04:40:59","slug":"%e7%ac%ac8%e7%ab%a0%e8%81%9a%e5%90%88%e5%87%bd%e6%95%b0%e8%af%be%e5%90%8e%e7%bb%83%e4%b9%a0","status":"publish","type":"post","link":"https:\/\/qaqaq.top\/?p=1527","title":{"rendered":"\u7b2c8\u7ae0\u805a\u5408\u51fd\u6570\u8bfe\u540e\u7ec3\u4e60"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code># \u7b2c08\u7ae0_\u805a\u5408\u51fd\u6570\u8bfe\u540e\u7ec3\u4e60\r\n\r\n#1.where\u5b50\u53e5\u53ef\u5426\u4f7f\u7528\u7ec4\u51fd\u6570\u8fdb\u884c\u8fc7\u6ee4?  NO !\r\n\r\n#2.\u67e5\u8be2\u516c\u53f8\u5458\u5de5\u5de5\u8d44\u7684\u6700\u5927\u503c\uff0c\u6700\u5c0f\u503c\uff0c\u5e73\u5747\u503c\uff0c\u603b\u548c\r\nSELECT MAX(salary) max_sal,MIN(salary) min_sal,AVG(salary) avg_sal,SUM(salary) sum_sal\r\nFROM employees;\r\n\r\n#3.\u67e5\u8be2\u5404job_id\u7684\u5458\u5de5\u5de5\u8d44\u7684\u6700\u5927\u503c\uff0c\u6700\u5c0f\u503c\uff0c\u5e73\u5747\u503c\uff0c\u603b\u548c\r\nSELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)\r\nFROM employees\r\nGROUP BY job_id;\r\n\r\n#4.\u9009\u62e9\u5177\u6709\u5404\u4e2ajob_id\u7684\u5458\u5de5\u4eba\u6570\r\nSELECT job_id,COUNT(*)\r\nFROM employees\r\nGROUP BY job_id;\r\n\r\n# 5.\u67e5\u8be2\u5458\u5de5\u6700\u9ad8\u5de5\u8d44\u548c\u6700\u4f4e\u5de5\u8d44\u7684\u5dee\u8ddd\uff08DIFFERENCE\uff09\r\nSELECT MAX(salary) - MIN(salary) AS \"DIFFERENCE\"\r\nFROM employees;\r\n\r\n# 6.\u67e5\u8be2\u5404\u4e2a\u7ba1\u7406\u8005\u624b\u4e0b\u5458\u5de5\u7684\u6700\u4f4e\u5de5\u8d44\uff0c\u5176\u4e2d\u6700\u4f4e\u5de5\u8d44\u4e0d\u80fd\u4f4e\u4e8e6000\uff0c\u6ca1\u6709\u7ba1\u7406\u8005\u7684\u5458\u5de5\u4e0d\u8ba1\u7b97\u5728\u5185\r\nSELECT manager_id,MIN(salary)\r\nFROM employees\r\nWHERE manager_id IS NOT NULL\r\nGROUP BY manager_id\r\nHAVING MIN(salary) >= 6000;\r\n\r\n# 7.\u67e5\u8be2\u6240\u6709\u90e8\u95e8\u7684\u540d\u5b57\uff0clocation_id\uff0c\u5458\u5de5\u6570\u91cf\u548c\u5e73\u5747\u5de5\u8d44\uff0c\u5e76\u6309\u5e73\u5747\u5de5\u8d44\u964d\u5e8f\r\nSELECT d.department_name,d.location_id,COUNT(employee_id),AVG(salary)\r\nFROM employees e RIGHT JOIN departments d\r\nON e.department_id = d.department_id\r\nGROUP BY department_name,location_id\r\nORDER BY AVG(salary) DESC;\r\n\r\nSELECT employee_id,salary,department_name,location_id\r\nFROM employees e RIGHT JOIN departments d\r\nON e.department_id = d.department_id;\r\n\r\n# 8.\u67e5\u8be2\u6bcf\u4e2a\u5de5\u79cd\u3001\u6bcf\u4e2a\u90e8\u95e8\u7684\u90e8\u95e8\u540d\u3001\u5de5\u79cd\u540d\u548c\u6700\u4f4e\u5de5\u8d44\r\nSELECT d.department_name,e.job_id,MIN(salary)\r\nFROM employees e RIGHT JOIN departments d\r\nON e.department_id = d.department_id\r\nGROUP BY job_id,department_name;\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-1527","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\/1527"}],"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=1527"}],"version-history":[{"count":1,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1527\/revisions"}],"predecessor-version":[{"id":1528,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1527\/revisions\/1528"}],"wp:attachment":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1527"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1527"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1527"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}