本文介绍如何在 python 中基于数据库中实际存在的年份值，动态构建 sql pivot 查询语句，自动生成按姓名分组、按年份列展开的宽表结构，并支持新增年份自动适配，避免硬编码维护。
本文介绍如何在 python 中基于数据库中实际存在的年份值，动态构建 sql pivot 查询语句，自动生成按姓名分组、按年份列展开的宽表结构，并支持新增年份自动适配，避免硬编码维护。
在处理时间序列类业务数据（如年度成绩、销售统计）时，常需将“长格式”数据（name, year, score）转换为“宽格式”报表（每行为一人，每年为一列），并导出至 Excel。若年份范围随业务增长而变化（例如从 2016 持续新增至 2024+），手动编写 CASE WHEN 语句不仅繁琐，还极易出错且不可维护。
核心思路是：先查出所有有效年份 → 动态拼接 SQL 的 SELECT 子句 → 执行并构造 DataFrame。
以下为完整可运行的 Python 示例（以 SQLite/MySQL/PostgreSQL 兼容语法为基础，使用 sqlite3 + pandas）：
import sqlite3
import pandas as pd
# 假设已建立数据库连接
conn = sqlite3.connect("scores.db")
cursor = conn.cursor()
# 步骤 1：动态获取全部唯一年份（升序排列，确保列顺序稳定）
cursor.execute("SELECT DISTINCT year FROM scores ORDER BY year")
years = [row[0] for row in cursor.fetchall()] # 如 [2020, 2021, 2022, 2023, 2024]
# 步骤 2：构建动态 SELECT 字段（含各年份列 + TOTAL 列）
year_columns = [
f"MAX(CASE WHEN year = {y} THEN score END) AS '{y}'"
for y in years
]
total_expr = " + ".join([
f"COALESCE(MAX(CASE WHEN year = {y} THEN score END), 0)"
for y in years
])
# 步骤 3：组合最终 SQL
sql = f"""
SELECT
name,
{', '.join(year_columns)},
{total_expr} AS total
FROM scores
GROUP BY name
ORDER BY name;
"""
# 步骤 4：执行查询并转为 DataFrame
cursor.execute(sql)
rows = cursor.fetchall()
columns = ['name'] + [str(y) for y in years] + ['total']
df = pd.DataFrame(rows, columns=columns)
# 步骤 5：导出 Excel（需安装 openpyxl）
df.to_excel("annual_scores_pivot.xlsx", index=False)
print("✅ 动态透视表已生成，支持未来年份自动扩展！")✅ 关键优势说明：
- 零人工维护：只要新数据插入
scores表（如("Andi", 2025, 62)），下次运行脚本即自动包含2025列；- NULL 安全：使用
COALESCE(..., 0)确保缺失年份计为 0，避免NULL参与SUM导致整行total为NULL；- 兼容性强：
CASE WHEN ... THEN ... END是标准 SQL，适用于 MySQL、PostgreSQL、SQL Server、SQLite 等主流数据库；- 性能可控：
DISTINCT year查询极快（年份维度基数小），整体仍为单次聚合查询，无循环多次 IO。
⚠️ 注意事项：
- 若年份跨度极大（如 1990–2050），需评估
CASE表达式长度是否超数据库限制（通常不构成问题）； - 生产环境建议对
year字段添加索引（CREATE INDEX idx_scores_year ON scores(year);）以加速DISTINCT和GROUP BY； - 如需支持多指标（如
score,count,rate），可为每个指标重复生成CASE块，或改用pandas.pivot_table()在内存中完成（适合中等数据量）。
该方案兼顾灵活性与工程健壮性，是数据库驱动型报表自动化的核心实践之一。
