-- ============================================================
|
-- 2025 / 2026 年 1-8 月累计 货运量、货物周转量 汇总（同名企业口径）
|
-- 生成日期：2026-09-19 数据库：traffic_audit（MySQL 8.0.29）
|
-- ------------------------------------------------------------
|
-- 数据来源表：h2032_enterprise_monthly
|
-- 《道路货物运输月度生产情况表》= 交企统H203-2表，企业级月报（规上企业）
|
-- 字段：freight_total 货运量_总计（吨）
|
-- turnover_total 货物周转量_总计（吨公里）
|
-- report_period 报表期，格式 'YYYY-MM'
|
-- enterprise_name 企业名称
|
-- 单位换算：表内存的是「吨 / 吨公里」，报表口径是「万吨 / 万吨公里」，
|
-- 所以下面统一 ÷10000。
|
-- 期间取法：1-8 月 = report_period BETWEEN '2025-01' AND '2025-08'
|
-- （report_period 是字符串 'YYYY-MM'，直接比大小即可）
|
-- 为什么要「同名企业口径」：
|
-- 2025 年 1-8 月有 664 家企业、2026 年 1-8 月只有 636 家，
|
-- 直接拿两年总数相减，差额里混着「企业数量变了」的影响，不是真实增减。
|
-- 把两年都报送过的企业名称取交集（实测 545 家），两年的企业范围才一致。
|
-- 实测结果（同名企业口径 545 家）：
|
-- 2025 年 1-8 月：货运量 30153.14 万吨，周转量 4635340.46 万吨公里
|
-- 2026 年 1-8 月：货运量 27752.68 万吨，周转量 4389882.05 万吨公里
|
-- 同比：货运量 -7.96%，周转量 -5.30%
|
-- ============================================================
|
|
|
-- =====================================================================
|
-- 查询 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, 2) AS 货运量2025_万吨,
|
ROUND(SUM(CASE WHEN e.report_period >= '2026-01'
|
THEN e.freight_total ELSE 0 END) / 10000, 2) 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, 2) AS 周转量2025_万吨公里,
|
ROUND(SUM(CASE WHEN e.report_period >= '2026-01'
|
THEN e.turnover_total ELSE 0 END) / 10000, 2) 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 地区代码,
|
ROUND(SUM(CASE WHEN e.report_period <= '2025-08' THEN e.freight_total ELSE 0 END) / 10000, 2) AS 货运量2025_万吨,
|
ROUND(SUM(CASE WHEN e.report_period >= '2026-01' THEN e.freight_total ELSE 0 END) / 10000, 2) AS 货运量2026_万吨,
|
ROUND((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, 2) AS 货运量增减_万吨,
|
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, 2) AS 周转量2025_万吨公里,
|
ROUND(SUM(CASE WHEN e.report_period >= '2026-01' THEN e.turnover_total ELSE 0 END) / 10000, 2) AS 周转量2026_万吨公里,
|
ROUND((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, 2) AS 周转量增减_万吨公里,
|
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 (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, 2) AS 货运量_万吨,
|
ROUND(SUM(t.turnover_total) / 10000, 2) 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, 2) AS 货运量2025_万吨,
|
ROUND(SUM(d.freight_2026) / 10000, 2) AS 货运量2026_万吨,
|
ROUND(SUM(d.turnover_2025) / 10000, 2) AS 周转量2025_万吨公里,
|
ROUND(SUM(d.turnover_2026) / 10000, 2) 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 年货运量降序。
|
-- 提示：明细行是「先四舍五入再求和」，与查询 8 的合计有 0.01~0.02 的尾差，属正常。
|
-- =====================================================================
|
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 地区代码,
|
ROUND(d.freight_2025 / 10000, 2) AS 货运量2025_万吨,
|
ROUND(d.freight_2026 / 10000, 2) AS 货运量2026_万吨,
|
ROUND((d.freight_2026 - d.freight_2025) / 10000, 2) AS 货运量增减_万吨,
|
ROUND((d.freight_2026 / NULLIF(d.freight_2025, 0) - 1) * 100, 2) AS 货运量同比_百分比,
|
ROUND(d.turnover_2025 / 10000, 2) AS 周转量2025_万吨公里,
|
ROUND(d.turnover_2026 / 10000, 2) AS 周转量2026_万吨公里,
|
ROUND((d.turnover_2026 - d.turnover_2025) / 10000, 2) AS 周转量增减_万吨公里,
|
ROUND((d.turnover_2026 / NULLIF(d.turnover_2025, 0) - 1) * 100, 2) 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.99 万吨、周转量 4637038.58 万吨公里
|
-- 2026 年 1-8 月：货运量 27764.88 万吨、周转量 4392984.81 万吨公里
|
-- =====================================================================
|
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, 2) AS 货运量2025_万吨,
|
ROUND(SUM(CASE WHEN e.report_period >= '2026-01' THEN e.freight_total ELSE 0 END) / 10000, 2) AS 货运量2026_万吨,
|
ROUND(SUM(CASE WHEN e.report_period <= '2025-08' THEN e.turnover_total ELSE 0 END) / 10000, 2) AS 周转量2025_万吨公里,
|
ROUND(SUM(CASE WHEN e.report_period >= '2026-01' THEN e.turnover_total ELSE 0 END) / 10000, 2) 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，Excel 双击即可分列，最省事）
|
-- 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 是「UTF-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：SHOW VARIABLES LIKE 'secure_file_priv';
|
