{"id":2868,"date":"2023-07-13T15:08:23","date_gmt":"2023-07-13T07:08:23","guid":{"rendered":"https:\/\/xinchewhd.com.cn\/?p=2868"},"modified":"2023-07-13T15:08:24","modified_gmt":"2023-07-13T07:08:24","slug":"mysql%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b","status":"publish","type":"post","link":"https:\/\/xinchewhd.com.cn\/index.php\/mysql\/mysql%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b\/","title":{"rendered":"MySQL\u5b58\u50a8\u8fc7\u7a0b"},"content":{"rendered":"\n<p>\u5e26\u4e34\u65f6\u8868\u5b58\u50a8\u8fc7\u7a0b\uff0c\u5e76\u5c06\u6570\u636e\u5b58\u50a8\u5230\u73b0\u5b9e\u8868\u4e2d<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\u4e00\u3001\u4e0d\u5e26\u4f20\u53c2\u7684\u5b58\u50a8\u8fc7\u7a0b\n1\u3001\u5efa\u8868sql\uff1a\nCREATE TABLE your_target_table (\n        id INT AUTO_INCREMENT PRIMARY KEY,\n        branch_no VARCHAR(50),\n        shop_name VARCHAR(100),\n        foodHundred varchar(50),\n        hotPotConvenienceDish varchar(50),\n        fruits varchar(50),\n        meat varchar(50),\n        vegetable varchar(50),\n        call_time DATETIME\n    );<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>2\u3001\u5b58\u50a8\u8fc7\u7a0b\uff1a\n\u521b\u5efa\u4e34\u65f6\u8868\uff08temp_data_table\uff09\uff0c\u5c06\u903b\u8f91\u67e5\u8be2\u5230\u7684sql\u5b58\u50a8\u5230\u4e34\u65f6\u8868\uff08temp_data_table\uff09\u4e2d\uff0c\u5c06\u4e34\u65f6\u8868\uff08temp_data_table\uff09\u4e2d\u7684\u6570\u636e\u5b58\u50a8\u5230\u73b0\u5b9e\u8868\uff08your_target_table\uff09\u4e2d\uff0c\u5e76\u5c06\u4e34\u65f6\u8868\uff08temp_data_table\uff09\u5220\u9664\nDELIMITER \/\/\n\nCREATE PROCEDURE save_data_to_diffable()\nBEGIN\n    -- \u521b\u5efa\u4e00\u4e2a\u4e34\u65f6\u8868\u7528\u4e8e\u5b58\u50a8\u5f53\u524d\u6570\u636e\n    CREATE TEMPORARY TABLE temp_data_table (\n        id INT AUTO_INCREMENT PRIMARY KEY,\n        branch_no VARCHAR(50),\n        shop_name VARCHAR(100),\n        foodHundred VARCHAR(50),\n        hotPotConvenienceDish VARCHAR(50),\n        fruits VARCHAR(50),\n        meat VARCHAR(50),\n        vegetable VARCHAR(50),\n        call_time DATETIME\n    );\n\n    -- \u63d2\u5165\u5f53\u524d\u6570\u636e\u5230\u4e34\u65f6\u8868\u4e2d\n    INSERT INTO temp_data_table (branch_no, shop_name, foodHundred, hotPotConvenienceDish, fruits, meat, vegetable, call_time)\n    SELECT\n        b.branch_no,\n        b.shop_name,\n        max(case b.div_name when '\u98df\u767e' then b.num else 0 end) as 'foodHundred',\n        max(case b.div_name when '\u706b\u9505\u65b9\u4fbf\u83dc' then b.num else 0 end) as 'hotPotConvenienceDish',\n        max(case b.div_name when '\u6c34\u679c' then b.num else 0 end) as 'fruits',\n        max(case b.div_name when '\u8089\u7c7b' then b.num else 0 end) as 'meat',\n        max(case b.div_name when '\u852c\u83dc' then b.num else 0 end) as 'vegetable',\n        NOW() as 'call_time'\n    FROM (\n        SELECT\n            `a`.`branch_no` AS `branch_no`,\n            `a`.`shop_name` AS `shop_name`,\n            `a`.`div_name` AS `div_name`,\n            SUM(`a`.`countNum`) AS `num`\n        FROM\n            (\n            SELECT\n                `rhd`.`branch_no` AS `branch_no`,\n                `hs`.`shop_name` AS `shop_name`,\n                `rhi`.`div_name` AS `div_name`,\n                ((`rhd`.`pos_qty` + `rhd`.`so_qty`) - `rhd`.`pos_ret_qty`) AS `countNum`\n            FROM\n                `huixiaoxian_sixun`.`real_time_hxx_rpt_fresh_bhd_item_info_di` `rhi`\n                JOIN `huixiaoxian_sixun`.`real_time_hxx_sync_t_da_jxc_day_sum` `rhd` ON (`rhi`.`item_no` = `rhd`.`item_no`)\n                JOIN `huixiaoxian_sixun`.`hxx_bhd_dim_fresh_bhd_area_shop_di` `hs` ON (`hs`.`shop_id` = `rhd`.`branch_no`)\n            WHERE\n                rhd.__create_time >= DATE_FORMAT(CURDATE(), '%Y-%m-01')\n                AND rhd.__create_time &lt; DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)\n                AND rhi.update_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')\n                AND rhi.update_date &lt; DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)\n            ) `a`\n        GROUP BY\n            `a`.`shop_name`,\n            `a`.`div_name`\n    ) b\n    GROUP BY b.branch_no;\n\n    -- \u63d2\u5165\u4e34\u65f6\u8868\u4e2d\u7684\u6570\u636e\u5230\u76ee\u6807\u8868\u4e2d\n    INSERT INTO your_target_table (branch_no, shop_name, foodHundred, hotPotConvenienceDish, fruits, meat, vegetable, call_time)\n    SELECT branch_no, shop_name, foodHundred, hotPotConvenienceDish, fruits, meat, vegetable, call_time\n    FROM temp_data_table;\n\n    -- \u5220\u9664\u4e34\u65f6\u8868\n    DROP TABLE temp_data_table;\nEND \/\/\n\nDELIMITER ;\n\n\n3\u3001\u6267\u884c\u5b58\u50a8\u8fc7\u7a0b\ncall save_data_to_diffable();\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>\u4e8c\u3001\u5e26\u6761\u4ef6\u7684\u5b58\u50a8\u8fc7\u7a0b\n\nDELIMITER \/\/\r\nCREATE PROCEDURE task_pm (IN businessType VARCHAR(50), IN servType VARCHAR(50))\r\nBEGIN\r\nINSERT INTO n_pm_project (\r\n    `F_Id`, `F_ProjectNO`, `F_CustomNO`, `F_ShopId`, `F_ShortName`, `F_Title`, `F_Type`,\r\n    `F_Area`, `F_BusinessCompany`, `F_BusinessType`, `F_Description`, `F_CreatorUserId`,\r\n    `F_CreatorTime`, `F_Place`, `F_Region`, `F_Address`, `F_StartTime`, `F_EndTime`,\r\n    `F_Status`, `F_business_type`, `F_IsAction`, `F_Org_Id`, `F_Project_Relation`,\r\n    `F_AttendanceMachineId`, `F_Shop`, `F_Shop_Name`, `F_Shop_Address`, `F_Org`,\r\n    `F_Org_Name`\r\n)\r\nSELECT\r\n    a.`F_Id`, a.`F_ProjectNO`, a.`F_CustomNO`, a.`F_ShopId`, a.`F_ShortName`, a.`F_Title`,\r\n    a.`F_Type`, a.`F_Area`, a.`F_BusinessCompany`, a.`F_BusinessType`, a.`F_Description`,\r\n    a.`F_CreatorUserId`, a.`F_CreatorTime`, a.`F_Place`, a.`F_Region`, a.`F_Address`,\r\n    a.`F_StartTime`, a.`F_EndTime`, a.`F_Status`, a.`F_business_type`, a.`F_IsAction`,\r\n    a.`F_Org_Id`, a.`F_Project_Relation`, a.`F_AttendanceMachineId`, a.`F_Shop`,\r\n    a.`F_Shop_Name`, a.`F_Shop_Address`, a.`F_Org`, a.`F_Org_Name`\r\nFROM\r\n    szxh_business.pm_project a\r\nWHERE\r\n    a.F_BusinessType = businessType AND a.F_Type IN (\r\n        SELECT\r\n            c.F_EnCode\r\n        FROM\r\n            szxh_java_boot.base_dictionarytype b\r\n            INNER JOIN szxh_java_boot.base_dictionarydata c\r\n                ON c.F_DictionaryTypeId = b.F_Id\r\n        WHERE\r\n            b.F_FullName LIKE CONCAT('%', '\u4e3b\u6570\u636e\u4e1a\u52a1\u7ebf', '%')\r\n            AND c.F_FullName LIKE CONCAT('%', servType, '%')\r\n    )\r\nON DUPLICATE KEY UPDATE\r\n    F_ProjectNO = VALUES(F_ProjectNO),\r\n    F_CustomNO = VALUES(F_CustomNO),\r\n    F_ShopId = VALUES(F_ShopId),\r\n    F_ShortName = VALUES(F_ShortName),\r\n    F_Title = VALUES(F_Title),\r\n    F_Type = VALUES(F_Type),\r\n    F_Area = VALUES(F_Area),\r\n    F_BusinessCompany = VALUES(F_BusinessCompany),\r\n    F_Description = VALUES(F_Description),\r\n    F_CreatorUserId = VALUES(F_CreatorUserId),\r\n    F_CreatorTime = VALUES(F_CreatorTime),\r\n    F_Place = VALUES(F_Place),\r\n    F_Region = VALUES(F_Region),\r\n    F_Address = VALUES(F_Address),\r\n    F_StartTime = VALUES(F_StartTime),\r\n    F_EndTime = VALUES(F_EndTime),\r\n    F_Status = VALUES(F_Status),\r\n    F_business_type = VALUES(F_business_type),\r\n    F_IsAction = VALUES(F_IsAction),\r\n    F_Org_Id = VALUES(F_Org_Id),\r\n    F_Project_Relation = VALUES(F_Project_Relation),\r\n    F_AttendanceMachineId = VALUES(F_AttendanceMachineId),\r\n    F_Shop = VALUES(F_Shop),\r\n    F_Shop_Name = VALUES(F_Shop_Name),\r\n    F_Shop_Address = VALUES(F_Shop_Address),\r\n    F_Org = VALUES(F_Org),\r\n    F_Org_Name = VALUES(F_Org_Name);\r\nEND \/\/ \r\n\r\nDELIMITER ;\n\n\n\n\u5e26\u53c2\u6570\u6267\u884c\u5b58\u50a8\u8fc7\u7a0b\n\/\/ businessType: \u4e1a\u52a1\u7c7b\u578b\n\/\/ servType: \u4e1a\u52a1\u7ebf\ncall task_pm ('\u81ea\u8425','\u4fdd\u6d01')\r\n<\/code><\/pre>\n","raw":"","protected":false},"excerpt":{"rendered":"<p>\u5e26\u4e34\u65f6\u8868\u5b58\u50a8\u8fc7\u7a0b\uff0c\u5e76\u5c06\u6570\u636e\u5b58\u50a8\u5230\u73b0\u5b9e\u8868\u4e2d<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"emotion":"","emotion_color":"","title_style":"","license":"","footnotes":""},"categories":[3],"tags":[],"class_list":["post-2868","post","type-post","status-publish","format-standard","hentry","category-mysql"],"post_thumbnail_image":"","content_first_image":null,"post_medium_image_300":"","post_thumbnail_image_624":"","post_frist_image":null,"post_medium_image":"","post_large_image":"","post_full_image":"","post_all_images":[],"videoAdId":"","listAd":"0","listAdId":"","listAdEvery":6,"total_comments":0,"category_name":"MySQL","post_date":"2023-07-13","like_count":"0","praiseWord":"\u9f13\u52b1","copyright_state":"","excitationAd":"0","rewardedVideoAdId":"","detailAdId":"","detailAd":"0","enterpriseMinapp":"0","audios":[],"postImageUrl":"https:\/\/wp-moto-1258805347.cos.ap-shanghai.myqcloud.com\/2023\/05\/20230519082947553.jpg","avatarurls":[],"related_posts":null,"pageviews":290,"next_post_id":2935,"next_post_title":"8\u79cd\u7ecf\u5e38\u88ab\u5ffd\u89c6\u7684SQL\u9519\u8bef\u7528\u6cd5\uff0c\u4f60\u4e2d\u62db\u4e86\u5417\uff1f","previous_post_id":2865,"previous_post_title":"MySQL\u7684on duplicate key update \u4f7f\u7528\u8bf4\u660e\u4e0e\u603b\u7ed3","_links":{"self":[{"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/posts\/2868","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/comments?post=2868"}],"version-history":[{"count":0,"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/posts\/2868\/revisions"}],"wp:attachment":[{"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/media?parent=2868"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/categories?post=2868"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/tags?post=2868"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}