From ade4387f72c876f779b0956295922003464434f0 Mon Sep 17 00:00:00 2001 From: zhizhijie <zhizhijie@users.noreply.gitee.com> Date: 星期六, 19 九月 2026 23:26:59 +0800 Subject: [PATCH] fix(汇总表): 中口径排名周转量口径统一为「总量-城市内」差额法 --- docs/生成汇总大表/同名企业_1-8月累计对比_2026-09-19.sql | 319 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 319 insertions(+), 0 deletions(-) diff --git "a/docs/\347\224\237\346\210\220\346\261\207\346\200\273\345\244\247\350\241\250/\345\220\214\345\220\215\344\274\201\344\270\232_1-8\346\234\210\347\264\257\350\256\241\345\257\271\346\257\224_2026-09-19.sql" "b/docs/\347\224\237\346\210\220\346\261\207\346\200\273\345\244\247\350\241\250/\345\220\214\345\220\215\344\274\201\344\270\232_1-8\346\234\210\347\264\257\350\256\241\345\257\271\346\257\224_2026-09-19.sql" new file mode 100644 index 0000000..be8d051 --- /dev/null +++ "b/docs/\347\224\237\346\210\220\346\261\207\346\200\273\345\244\247\350\241\250/\345\220\214\345\220\215\344\274\201\344\270\232_1-8\346\234\210\347\264\257\350\256\241\345\257\271\346\257\224_2026-09-19.sql" @@ -0,0 +1,319 @@ +-- ============================================================ +-- 2025 / 2026 骞� 1-8 鏈堢疮璁� 璐ц繍閲忋�佽揣鐗╁懆杞噺 姹囨�伙紙鍚屽悕浼佷笟鍙ｅ緞锛� +-- 鐢熸垚鏃ユ湡锛�2026-09-19 鏁版嵁搴擄細traffic_audit锛圡ySQL 8.0.29锛� +-- ------------------------------------------------------------ +-- 鏁版嵁鏉ユ簮琛細h2032_enterprise_monthly +-- 銆婇亾璺揣鐗╄繍杈撴湀搴︾敓浜ф儏鍐佃〃銆�= 浜や紒缁烪203-2琛紝浼佷笟绾ф湀鎶ワ紙瑙勪笂浼佷笟锛� +-- 瀛楁锛歠reight_total 璐ц繍閲廮鎬昏锛堝惃锛� +-- turnover_total 璐х墿鍛ㄨ浆閲廮鎬昏锛堝惃鍏噷锛� +-- report_period 鎶ヨ〃鏈燂紝鏍煎紡 'YYYY-MM' +-- enterprise_name 浼佷笟鍚嶇О +-- 鍗曚綅鎹㈢畻锛氳〃鍐呭瓨鐨勬槸銆屽惃 / 鍚ㄥ叕閲屻�嶏紝鎶ヨ〃鍙ｅ緞鏄�屼竾鍚� / 涓囧惃鍏噷銆嶏紝 +-- 鎵�浠ヤ笅闈㈢粺涓� 梅10000銆� +-- 鏈熼棿鍙栨硶锛�1-8 鏈� = report_period BETWEEN '2025-01' AND '2025-08' +-- 锛坮eport_period 鏄瓧绗︿覆 'YYYY-MM'锛岀洿鎺ユ瘮澶у皬鍗冲彲锛� +-- 涓轰粈涔堣銆屽悓鍚嶄紒涓氬彛寰勩�嶏細 +-- 2025 骞� 1-8 鏈堟湁 664 瀹朵紒涓氥��2026 骞� 1-8 鏈堝彧鏈� 636 瀹讹紝 +-- 鐩存帴鎷夸袱骞存�绘暟鐩稿噺锛屽樊棰濋噷娣风潃銆屼紒涓氭暟閲忓彉浜嗐�嶇殑褰卞搷锛屼笉鏄湡瀹炲鍑忋�� +-- 鎶婁袱骞撮兘鎶ラ�佽繃鐨勪紒涓氬悕绉板彇浜ら泦锛堝疄娴� 545 瀹讹級锛屼袱骞寸殑浼佷笟鑼冨洿鎵嶄竴鑷淬�� +-- 瀹炴祴缁撴灉锛堝悓鍚嶄紒涓氬彛寰� 545 瀹讹紝4 浣嶅皬鏁帮級锛� +-- 2025 骞� 1-8 鏈堬細璐ц繍閲� 30153.1392 涓囧惃锛屽懆杞噺 4635340.4627 涓囧惃鍏噷 +-- 2026 骞� 1-8 鏈堬細璐ц繍閲� 27752.6820 涓囧惃锛屽懆杞噺 4389882.0468 涓囧惃鍏噷 +-- 鍚屾瘮锛氳揣杩愰噺 -7.9609%锛屽懆杞噺 -5.2954% +-- 锛�2026 骞磋繖涓や釜鏁颁笌銆婃寔缁湪瑙勪紒涓�545瀹禵鍒嗗競宸�1-8鏈堢疮璁¤繍璺濆強鍚屾瘮銆嬩氦浠樹欢鐨勫叏鐪佽瀹屽叏涓�鑷达級 +-- ============================================================ + + +-- ============================================================ +-- 鈽� 灏忔暟浣嶇害瀹氾紙2026-09-19 琛ュ厖锛屽緢閲嶈锛夆槄 +-- ------------------------------------------------------------ +-- 1) 琛ㄩ噷瀛樼殑鏄�屽惃 / 鍚ㄥ叕閲屻�嶇殑鏁存暟锛屽鍑烘椂 梅10000 鍙樻垚銆屼竾鍚� / 涓囧惃鍏噷銆嶃�� +-- 2) 鍑℃槸銆愯鎷垮幓浜屾璁＄畻銆戠殑鍒楋紙灏ゅ叾鏄�屽懆杞噺 梅 璐ц繍閲� = 骞冲潎杩愯窛銆嶏級锛� +-- 鍗冧竾涓嶈兘鍦� SQL 閲屽彧淇濈暀 2 浣嶅皬鏁帮紒 +-- 鍘熷洜锛氫繚鐣� 2 浣� = 绮惧害鍒� 100 鍚ㄣ�傚璐ц繍閲忓彧鏈夊嚑鍗冨惃鐨勫皬浼佷笟锛� +-- 鍒嗘瘝浼氳鐮嶆帀鏈�澶� 1%锛岀畻鍑烘潵鐨勮繍璺濊兘宸嚭濂藉嚑鍏噷銆� +-- 瀹炰緥锛氭箹鍖楀紭闆风墿娴佹湁闄愬叕鍙� 2026 骞� 1-8 鏈堣揣杩愰噺 4631 鍚� +-- 鈫� 涓嶅彇鏁� 0.4631 涓囧惃锛堣繍璺� 1267.05 鍏噷锛屾纭級 +-- 鈫� 鍙� 2 浣� 0.46 涓囧惃锛堣繍璺� 1275.59 鍏噷锛岃櫄楂� 8.53 鍏噷锛� +-- 3) 鎵�浠ユ湰鑴氭湰绾﹀畾锛� +-- 路 鏄庣粏绫绘煡璇紙鏌ヨ 2銆佹煡璇� 7锛夆啋 瀹屽叏涓嶅 ROUND锛屽鍑哄嵆鍘熷鍊� +-- 锛堣揣杩愰噺/鍛ㄨ浆閲忕殑鍘熷鍊兼渶澶� 4 浣嶅皬鏁帮紝灏辨槸鍏ㄧ簿搴︼紱鍚屾瘮鍒楀皬鏁颁綅杈冮暱锛� +-- 瀚岄暱鍙嚜琛屽湪澶栭潰濂� ROUND(...,2)锛夛紱 +-- 路 姹囨�荤被锛堟煡璇� 1銆�3銆�5銆�8锛夆啋 鍚屾牱淇濈暀 4 浣嶅皬鏁帮紙瀵规湰琛ㄦ暟鎹潵璇� 4 浣嶅氨鏄叏绮惧害锛� +-- 淇濈暀 2 浣嶆病鏈変换浣曞ソ澶勶紝鍙嶈�屼細璁╀汉璇互涓烘暟鎹氨鍙湁 2 浣嶇簿搴︼級锛� +-- 路 濡傛灉浣犺鑷繁绠楄繍璺�/鍚屾瘮锛屽缓璁洿鎺ョ敤鏌ヨ 7锛屾垨鎶� /10000 閭ｄ竴灞傚幓鎺夛紝 +-- 鍏堝湪 Excel 閲屾寜銆屽惃銆嶇畻瀹屽啀闄ゃ�� +-- ============================================================ + +-- ===================================================================== +-- 鏌ヨ 1锛氭眹鎬讳竴琛� 鈥斺�� 鍚屽悕浼佷笟鍙ｅ緞鐨勪袱骞寸疮璁℃暟 + 鍚屾瘮澧為�� +-- ===================================================================== +SELECT COUNT(DISTINCT e.enterprise_name) AS 浼佷笟鏁�, + ROUND(SUM(CASE WHEN e.report_period <= '2025-08' + THEN e.freight_total ELSE 0 END) / 10000, 4) AS 璐ц繍閲�2025_涓囧惃, + ROUND(SUM(CASE WHEN e.report_period >= '2026-01' + THEN e.freight_total ELSE 0 END) / 10000, 4) AS 璐ц繍閲�2026_涓囧惃, + ROUND((SUM(CASE WHEN e.report_period >= '2026-01' THEN e.freight_total ELSE 0 END) + / NULLIF(SUM(CASE WHEN e.report_period <= '2025-08' THEN e.freight_total ELSE 0 END), 0) - 1) * 100, 2) + AS 璐ц繍閲忓悓姣擾鐧惧垎姣�, + ROUND(SUM(CASE WHEN e.report_period <= '2025-08' + THEN e.turnover_total ELSE 0 END) / 10000, 4) AS 鍛ㄨ浆閲�2025_涓囧惃鍏噷, + ROUND(SUM(CASE WHEN e.report_period >= '2026-01' + THEN e.turnover_total ELSE 0 END) / 10000, 4) AS 鍛ㄨ浆閲�2026_涓囧惃鍏噷, + ROUND((SUM(CASE WHEN e.report_period >= '2026-01' THEN e.turnover_total ELSE 0 END) + / NULLIF(SUM(CASE WHEN e.report_period <= '2025-08' THEN e.turnover_total ELSE 0 END), 0) - 1) * 100, 2) + AS 鍛ㄨ浆閲忓悓姣擾鐧惧垎姣� +FROM h2032_enterprise_monthly e +-- 涓嬮潰涓や釜 JOIN锛氬彧瑕佷袱骞� 1-8 鏈堥兘鎶ラ�佽繃鐨勪紒涓氾紙鍚屽悕浼佷笟鍙ｅ緞鐨勬牳蹇冿級 +JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2025-01' AND '2025-08') a + ON a.enterprise_name = e.enterprise_name +JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2026-01' AND '2026-08') b + ON b.enterprise_name = e.enterprise_name +WHERE e.report_period BETWEEN '2025-01' AND '2026-08'; + + +-- ===================================================================== +-- 鏌ヨ 2锛氶�愪紒涓氬鐓ф槑缁嗭紙鍚屽悕浼佷笟锛屼袱骞村苟鎺掞級锛屽彲鐩存帴瀵煎嚭 Excel +-- 鎸� 2026 骞磋揣杩愰噺浠庡ぇ鍒板皬鎺掞紱鍘绘帀 ORDER BY 灏辨槸瀹屾暣 545 琛屻�� +-- ===================================================================== +SELECT e.enterprise_name AS 浼佷笟鍚嶇О, + MAX(CASE WHEN e.report_period >= '2026-01' THEN e.region_code END) AS 鍦板尯浠ｇ爜, + SUM(CASE WHEN e.report_period <= '2025-08' THEN e.freight_total ELSE 0 END) / 10000 AS 璐ц繍閲�2025_涓囧惃, + SUM(CASE WHEN e.report_period >= '2026-01' THEN e.freight_total ELSE 0 END) / 10000 AS 璐ц繍閲�2026_涓囧惃, + (SUM(CASE WHEN e.report_period >= '2026-01' THEN e.freight_total ELSE 0 END) + - SUM(CASE WHEN e.report_period <= '2025-08' THEN e.freight_total ELSE 0 END)) / 10000 AS 璐ц繍閲忓鍑廮涓囧惃, + (SUM(CASE WHEN e.report_period >= '2026-01' THEN e.freight_total ELSE 0 END) + / NULLIF(SUM(CASE WHEN e.report_period <= '2025-08' THEN e.freight_total ELSE 0 END), 0) - 1) * 100 AS 璐ц繍閲忓悓姣擾鐧惧垎姣�, + SUM(CASE WHEN e.report_period <= '2025-08' THEN e.turnover_total ELSE 0 END) / 10000 AS 鍛ㄨ浆閲�2025_涓囧惃鍏噷, + SUM(CASE WHEN e.report_period >= '2026-01' THEN e.turnover_total ELSE 0 END) / 10000 AS 鍛ㄨ浆閲�2026_涓囧惃鍏噷, + (SUM(CASE WHEN e.report_period >= '2026-01' THEN e.turnover_total ELSE 0 END) + - SUM(CASE WHEN e.report_period <= '2025-08' THEN e.turnover_total ELSE 0 END)) / 10000 AS 鍛ㄨ浆閲忓鍑廮涓囧惃鍏噷, + (SUM(CASE WHEN e.report_period >= '2026-01' THEN e.turnover_total ELSE 0 END) + / NULLIF(SUM(CASE WHEN e.report_period <= '2025-08' THEN e.turnover_total ELSE 0 END), 0) - 1) * 100 AS 鍛ㄨ浆閲忓悓姣擾鐧惧垎姣� +FROM h2032_enterprise_monthly e +JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2025-01' AND '2025-08') a + ON a.enterprise_name = e.enterprise_name +JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2026-01' AND '2026-08') b + ON b.enterprise_name = e.enterprise_name +WHERE e.report_period BETWEEN '2025-01' AND '2026-08' +GROUP BY e.enterprise_name +ORDER BY 璐ц繍閲�2026_涓囧惃 DESC; + + +-- ===================================================================== +-- 鏌ヨ 3锛氫笁绉嶅彛寰勫鐓� 鈥斺�� 鐪嬫竻妤氥�屽叏閲忋�嶅拰銆屽悓鍚嶃�嶅樊鍦ㄥ摢 +-- ALL_2025 / ALL_2026 : 鍚勮嚜骞翠唤鐨勫叏閮ㄤ紒涓� +-- SAME_2025 / SAME_2026 : 鍚屽悕浼佷笟鍙ｅ緞锛�545 瀹讹級 +-- ===================================================================== +SELECT t.scope_type AS 鍙ｅ緞, + COUNT(DISTINCT t.enterprise_name) AS 浼佷笟鏁�, + ROUND(SUM(t.freight_total) / 10000, 4) AS 璐ц繍閲廮涓囧惃, + ROUND(SUM(t.turnover_total) / 10000, 4) AS 鍛ㄨ浆閲廮涓囧惃鍏噷 +FROM ( + SELECT 'ALL_2025' AS scope_type, enterprise_name, freight_total, turnover_total + FROM h2032_enterprise_monthly WHERE report_period BETWEEN '2025-01' AND '2025-08' + UNION ALL + SELECT 'ALL_2026', enterprise_name, freight_total, turnover_total + FROM h2032_enterprise_monthly WHERE report_period BETWEEN '2026-01' AND '2026-08' + UNION ALL + SELECT 'SAME_2025', e.enterprise_name, e.freight_total, e.turnover_total + FROM h2032_enterprise_monthly e + JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2025-01' AND '2025-08') a ON a.enterprise_name = e.enterprise_name + JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2026-01' AND '2026-08') b ON b.enterprise_name = e.enterprise_name + WHERE e.report_period BETWEEN '2025-01' AND '2025-08' + UNION ALL + SELECT 'SAME_2026', e.enterprise_name, e.freight_total, e.turnover_total + FROM h2032_enterprise_monthly e + JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2025-01' AND '2025-08') a ON a.enterprise_name = e.enterprise_name + JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2026-01' AND '2026-08') b ON b.enterprise_name = e.enterprise_name + WHERE e.report_period BETWEEN '2026-01' AND '2026-08' +) t +GROUP BY t.scope_type +ORDER BY t.scope_type; + + +-- ===================================================================== +-- 鏌ヨ 4锛氬樊寮備紒涓氬悕鍗曪紙鏍稿鐢級鈥斺�� 鍙湪涓�骞存姤閫佺殑浼佷笟鍙婂叾绱閲� +-- only_in = 2025 / 2026 +-- 瀹炴祴锛�2025 骞寸嫭鏈� 119 瀹躲��2026 骞寸嫭鏈� 91 瀹讹紙1-8 鏈堝彛寰勶級 +-- ===================================================================== +SELECT '2025' AS only_in, e.enterprise_name AS 浼佷笟鍚嶇О, + ROUND(SUM(e.freight_total) / 10000, 2) AS 璐ц繍閲廮涓囧惃, + ROUND(SUM(e.turnover_total) / 10000, 2) AS 鍛ㄨ浆閲廮涓囧惃鍏噷 +FROM h2032_enterprise_monthly e +LEFT JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2026-01' AND '2026-08') b + ON b.enterprise_name = e.enterprise_name +WHERE e.report_period BETWEEN '2025-01' AND '2025-08' AND b.enterprise_name IS NULL +GROUP BY e.enterprise_name +UNION ALL +SELECT '2026', e.enterprise_name, + ROUND(SUM(e.freight_total) / 10000, 2), + ROUND(SUM(e.turnover_total) / 10000, 2) +FROM h2032_enterprise_monthly e +LEFT JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2025-01' AND '2025-08') a + ON a.enterprise_name = e.enterprise_name +WHERE e.report_period BETWEEN '2026-01' AND '2026-08' AND a.enterprise_name IS NULL +GROUP BY e.enterprise_name +ORDER BY only_in, 璐ц繍閲廮涓囧惃 DESC; + + +-- ===================================================================== +-- 鏌ヨ 5锛氬垎甯傚窞銆屽悓鍚嶄紒涓氬彛寰勩�嶄袱骞寸疮璁″鐓� +-- 鍦板尯浠ｇ爜鍙� 2026 骞撮偅鏉¤褰曠殑 region_code +-- ===================================================================== +SELECT CASE + WHEN region_code LIKE '4201%' THEN '姝︽眽' WHEN region_code LIKE '4202%' THEN '榛勭煶' + WHEN region_code LIKE '4203%' THEN '鍗佸牥' WHEN region_code LIKE '4205%' THEN '瀹滄槍' + WHEN region_code LIKE '4206%' THEN '瑗勯槼' WHEN region_code LIKE '4207%' THEN '閯傚窞' + WHEN region_code LIKE '4208%' THEN '鑽嗛棬' WHEN region_code LIKE '4209%' THEN '瀛濇劅' + WHEN region_code LIKE '4210%' THEN '鑽嗗窞' WHEN region_code LIKE '4211%' THEN '榛勫唸' + WHEN region_code LIKE '4212%' THEN '鍜稿畞' WHEN region_code LIKE '4213%' THEN '闅忓窞' + WHEN region_code LIKE '4228%' THEN '鎭╂柦' WHEN region_code LIKE '429004%' THEN '浠欐' + WHEN region_code LIKE '429005%' THEN '娼滄睙' WHEN region_code LIKE '429006%' THEN '澶╅棬' + WHEN region_code LIKE '429021%' THEN '鏋楀尯' ELSE '鍏朵粬' END AS 甯傚窞, + COUNT(DISTINCT d.enterprise_name) AS 鍚屽悕浼佷笟鏁�, + ROUND(SUM(d.freight_2025) / 10000, 4) AS 璐ц繍閲�2025_涓囧惃, + ROUND(SUM(d.freight_2026) / 10000, 4) AS 璐ц繍閲�2026_涓囧惃, + ROUND(SUM(d.turnover_2025) / 10000, 4) AS 鍛ㄨ浆閲�2025_涓囧惃鍏噷, + ROUND(SUM(d.turnover_2026) / 10000, 4) AS 鍛ㄨ浆閲�2026_涓囧惃鍏噷 +FROM ( + SELECT e.enterprise_name, + MAX(CASE WHEN e.report_period >= '2026-01' THEN e.region_code END) AS region_code, + SUM(CASE WHEN e.report_period <= '2025-08' THEN e.freight_total ELSE 0 END) AS freight_2025, + SUM(CASE WHEN e.report_period >= '2026-01' THEN e.freight_total ELSE 0 END) AS freight_2026, + SUM(CASE WHEN e.report_period <= '2025-08' THEN e.turnover_total ELSE 0 END) AS turnover_2025, + SUM(CASE WHEN e.report_period >= '2026-01' THEN e.turnover_total ELSE 0 END) AS turnover_2026 + FROM h2032_enterprise_monthly e + JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2025-01' AND '2025-08') a ON a.enterprise_name = e.enterprise_name + JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2026-01' AND '2026-08') b ON b.enterprise_name = e.enterprise_name + WHERE e.report_period BETWEEN '2025-01' AND '2026-08' + GROUP BY e.enterprise_name +) d +GROUP BY 甯傚窞 +ORDER BY 璐ц繍閲�2026_涓囧惃 DESC; + + +-- ===================================================================== +-- 鏌ヨ 6锛氭暟鎹綋妫� 鈥斺�� 鍚屽悕浼佷笟閲屾湀浠戒笉瓒� 8 涓湀鐨勶紙绱鏁颁細鍋忓皬锛屾瘮瀵规椂娉ㄦ剰锛� +-- 姝ｅ父锛�2025 骞� 1-8 鏈� 8 鏉� + 2026 骞� 1-8 鏈� 8 鏉� +-- 瀹炴祴锛氭棤寮傚父浼佷笟锛屽叏閮� 545 瀹朵袱骞村悇 8 涓湀榻愬叏 +-- ===================================================================== +SELECT e.enterprise_name AS 浼佷笟鍚嶇О, + SUM(CASE WHEN e.report_period BETWEEN '2025-01' AND '2025-08' THEN 1 ELSE 0 END) AS 璁板綍鏁�2025, + SUM(CASE WHEN e.report_period BETWEEN '2026-01' AND '2026-08' THEN 1 ELSE 0 END) AS 璁板綍鏁�2026 +FROM h2032_enterprise_monthly e +JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2025-01' AND '2025-08') a ON a.enterprise_name = e.enterprise_name +JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2026-01' AND '2026-08') b ON b.enterprise_name = e.enterprise_name +WHERE e.report_period BETWEEN '2025-01' AND '2026-08' +GROUP BY e.enterprise_name +HAVING 璁板綍鏁�2025 <> 8 OR 璁板綍鏁�2026 <> 8; +-- ===================================================================== +-- 鏌ヨ 7锛氫紒涓氭槑缁嗗鍑猴紙鎺ㄨ崘鐢ㄨ繖鐗堬紝546 瀹讹級 +-- 涓庢煡璇� 2 鐨勫尯鍒細鍖归厤閿敤銆岀粺涓�绀句細淇＄敤浠ｇ爜銆嶈�屼笉鏄紒涓氬悕绉帮紝 +-- 澶氳鐩� 1 瀹舵敼鍚嶄紒涓氾紙91420112558443820B锛� +-- 2025-01~2025-09 鍙�屾箹鍖椾笁宄″瘜鐑ㄧ豢鐢典緵搴旈摼鏈夐檺鍏徃銆嶏紝 +-- 2025-10 璧峰彨銆屾箹鍖楅暱宄″瘜鐑ㄧ豢鐢典緵搴旈摼鏈夐檺鍏徃銆嶏級锛� +-- 鏌ヨ 2 鎸夊悕绉板尮閰嶄細鎶婅繖瀹朵袱骞撮兘婕忔帀锛屼笖鍚屾椂璁″叆涓ゅ勾銆岀嫭鏈変紒涓氥�嶅悕鍗曘�� +-- 杈撳嚭鍒楀凡鎸夊鍑轰範鎯帓濂斤紝鍙洿鎺ョ矘杩� Excel锛涙寜 2026 骞磋揣杩愰噺闄嶅簭銆� +-- 鎻愮ず锛氭槑缁嗗垪涓嶅 ROUND锛堝叏绮惧害锛岃揣杩愰噺/鍛ㄨ浆閲忔渶澶� 4 浣嶅皬鏁帮級锛� +-- 涓庢煡璇� 8 鐨� 2 浣嶅悎璁′細鏈� 0.01 绾у熬宸紝灞炴甯搞�� +-- ===================================================================== +SELECT d.credit_code AS 缁熶竴绀句細淇＄敤浠ｇ爜, + d.name_2026 AS 浼佷笟鍚嶇О, + d.name_2025 AS 鍚嶇О2025, + IF(d.name_2025 <> d.name_2026, '鏄�', '') AS 鏄惁鏀瑰悕, + CASE + WHEN d.region_code LIKE '4201%' THEN '姝︽眽' WHEN d.region_code LIKE '4202%' THEN '榛勭煶' + WHEN d.region_code LIKE '4203%' THEN '鍗佸牥' WHEN d.region_code LIKE '4205%' THEN '瀹滄槍' + WHEN d.region_code LIKE '4206%' THEN '瑗勯槼' WHEN d.region_code LIKE '4207%' THEN '閯傚窞' + WHEN d.region_code LIKE '4208%' THEN '鑽嗛棬' WHEN d.region_code LIKE '4209%' THEN '瀛濇劅' + WHEN d.region_code LIKE '4210%' THEN '鑽嗗窞' WHEN d.region_code LIKE '4211%' THEN '榛勫唸' + WHEN d.region_code LIKE '4212%' THEN '鍜稿畞' WHEN d.region_code LIKE '4213%' THEN '闅忓窞' + WHEN d.region_code LIKE '4228%' THEN '鎭╂柦' WHEN d.region_code LIKE '429004%' THEN '浠欐' + WHEN d.region_code LIKE '429005%' THEN '娼滄睙' WHEN d.region_code LIKE '429006%' THEN '澶╅棬' + WHEN d.region_code LIKE '429021%' THEN '鏋楀尯' ELSE '鍏朵粬' END AS 甯傚窞, + d.region_code AS 鍦板尯浠ｇ爜, + d.freight_2025 / 10000 AS 璐ц繍閲�2025_涓囧惃, + d.freight_2026 / 10000 AS 璐ц繍閲�2026_涓囧惃, + (d.freight_2026 - d.freight_2025) / 10000 AS 璐ц繍閲忓鍑廮涓囧惃, + (d.freight_2026 / NULLIF(d.freight_2025, 0) - 1) * 100 AS 璐ц繍閲忓悓姣擾鐧惧垎姣�, + d.turnover_2025 / 10000 AS 鍛ㄨ浆閲�2025_涓囧惃鍏噷, + d.turnover_2026 / 10000 AS 鍛ㄨ浆閲�2026_涓囧惃鍏噷, + (d.turnover_2026 - d.turnover_2025) / 10000 AS 鍛ㄨ浆閲忓鍑廮涓囧惃鍏噷, + (d.turnover_2026 / NULLIF(d.turnover_2025, 0) - 1) * 100 AS 鍛ㄨ浆閲忓悓姣擾鐧惧垎姣� +FROM ( + SELECT e.unified_credit_code AS credit_code, + -- 2026 骞寸敤鐨勫悕瀛楋紙鏀瑰悕鍚庡彇鏂板悕锛夈��2025 骞寸敤鐨勫悕瀛楋紝鐢ㄤ簬鏍囪鏀瑰悕 + MAX(CASE WHEN e.report_period >= '2026-01' THEN e.enterprise_name END) AS name_2026, + MAX(CASE WHEN e.report_period <= '2025-08' THEN e.enterprise_name END) AS name_2025, + MAX(CASE WHEN e.report_period >= '2026-01' THEN e.region_code END) AS region_code, + SUM(CASE WHEN e.report_period <= '2025-08' THEN e.freight_total ELSE 0 END) AS freight_2025, + SUM(CASE WHEN e.report_period >= '2026-01' THEN e.freight_total ELSE 0 END) AS freight_2026, + SUM(CASE WHEN e.report_period <= '2025-08' THEN e.turnover_total ELSE 0 END) AS turnover_2025, + SUM(CASE WHEN e.report_period >= '2026-01' THEN e.turnover_total ELSE 0 END) AS turnover_2026 + FROM h2032_enterprise_monthly e + JOIN (SELECT DISTINCT unified_credit_code c FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2025-01' AND '2025-08') a ON a.c = e.unified_credit_code + JOIN (SELECT DISTINCT unified_credit_code c FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2026-01' AND '2026-08') b ON b.c = e.unified_credit_code + WHERE e.report_period BETWEEN '2025-01' AND '2026-08' + GROUP BY e.unified_credit_code +) d +ORDER BY 璐ц繍閲�2026_涓囧惃 DESC; + + +-- ===================================================================== +-- 鏌ヨ 8锛氭煡璇� 7 鐨勫悎璁¤锛堝鍑烘槑缁嗗悗鐢ㄦ潵瀵规�绘暟锛�546 瀹跺彛寰勶級 +-- 2025 骞� 1-8 鏈堬細璐ц繍閲� 30162.9872 涓囧惃銆佸懆杞噺 4637038.5803 涓囧惃鍏噷 +-- 2026 骞� 1-8 鏈堬細璐ц繍閲� 27764.8812 涓囧惃銆佸懆杞噺 4392984.8116 涓囧惃鍏噷 +-- ===================================================================== +SELECT COUNT(DISTINCT e.unified_credit_code) AS 浼佷笟鏁�, + ROUND(SUM(CASE WHEN e.report_period <= '2025-08' THEN e.freight_total ELSE 0 END) / 10000, 4) AS 璐ц繍閲�2025_涓囧惃, + ROUND(SUM(CASE WHEN e.report_period >= '2026-01' THEN e.freight_total ELSE 0 END) / 10000, 4) AS 璐ц繍閲�2026_涓囧惃, + ROUND(SUM(CASE WHEN e.report_period <= '2025-08' THEN e.turnover_total ELSE 0 END) / 10000, 4) AS 鍛ㄨ浆閲�2025_涓囧惃鍏噷, + ROUND(SUM(CASE WHEN e.report_period >= '2026-01' THEN e.turnover_total ELSE 0 END) / 10000, 4) AS 鍛ㄨ浆閲�2026_涓囧惃鍏噷 +FROM h2032_enterprise_monthly e +JOIN (SELECT DISTINCT unified_credit_code c FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2025-01' AND '2025-08') a ON a.c = e.unified_credit_code +JOIN (SELECT DISTINCT unified_credit_code c FROM h2032_enterprise_monthly + WHERE report_period BETWEEN '2026-01' AND '2026-08') b ON b.c = e.unified_credit_code +WHERE e.report_period BETWEEN '2025-01' AND '2026-08'; + + +-- ===================================================================== +-- 涓夈�佹�庝箞鎶婃煡璇� 7 鐨勭粨鏋滃鍑烘潵 +-- --------------------------------------------------------------------- +-- 鏂瑰紡涓�锛氬懡浠よ瀵煎嚭锛堣緭鍑哄埗琛ㄧ鍒嗛殧鐨� .tsv锛孍xcel 鍙屽嚮鍗冲彲鍒嗗垪锛屾渶鐪佷簨锛� +-- mysql --host=127.0.0.1 --port=3308 --user=root --password=123456 ^ +-- --database=traffic_audit --default-character-set=utf8mb4 --batch --raw ^ +-- --execute="杩欓噷鏁存绮樿创鏌ヨ 7 鐨� SQL锛堝惈鏈�澶栧眰鍒嗗彿涔嬪墠鐨勫叏閮ㄥ唴瀹癸級" ^ +-- > D:\涓存椂\浼佷笟鏄庣粏_546瀹禵2025-2026骞�1-8鏈�.tsv +-- 娉ㄦ剰 1锛氫竴瀹氳甯� --default-character-set=utf8mb4锛屽惁鍒欎腑鏂囧垪鍚�/浼佷笟鍚嶄細鍙樹贡鐮併�� +-- 娉ㄦ剰 2锛氬鍑虹殑 .tsv 鏄�孶TF-8 鏃� BOM銆嶏紝閮ㄥ垎浜虹殑 Excel 鐩存帴鍙屽嚮浼氭樉绀轰贡鐮侊紝 +-- 璇锋敼鐢� Excel 鐨勩�屾暟鎹� 鈫� 浠庢枃鏈�/CSV銆嶅鍏ュ苟閫� UTF-8 缂栫爜锛� +-- 鎴栫洿鎺ョ敤涓嬮潰鐨勬柟寮忎簩瀵煎嚭鎴� xlsx锛堟渶涓嶅鏄撳嚭闂锛夈�� + +-- +-- 鏂瑰紡浜岋細鏌ヨ宸ュ叿瀵煎嚭锛堟帹鑽愮粰涓嶇啛鍛戒护琛岀殑鍦烘櫙锛� +-- 鍦� Navicat / MySQL Workbench / DBeaver 閲岀洿鎺ユ墽琛屾煡璇� 7锛� +-- 鍙抽敭缁撴灉缃戞牸 鈫掋�屽鍑虹粨鏋溿�嶁啋 閫� xlsx 鎴� csv 鍗冲彲銆� +-- +-- 鏂瑰紡涓夛細MySQL 鏈嶅姟绔洿鎺ュ啓鏂囦欢锛堜竴鑸笉鐢級 +-- 闇�瑕佹湇鍔＄ secure_file_priv 鍏佽鐩爣鐩綍锛屼笖璺緞鏄�屾湇鍔″櫒涓娿�嶇殑璺緞锛� +-- SELECT ... INTO OUTFILE 'D:/tmp/freight_detail.csv' +-- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'; +-- 鍏堟煡 secure_file_priv锛歋HOW VARIABLES LIKE 'secure_file_priv'; \ No newline at end of file -- Gitblit v1.9.1