-- ============================================================
|
-- 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;
|
