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