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