本文介绍如何在 python 中动态构建 sql 查询语句，将数据库中按年份存储的分数数据自动转为列（如 2020、2021…），并计算每人年度总分，避免硬编码年份，适配未来新增年份。
本文介绍如何在 python 中动态构建 sql 查询语句，将数据库中按年份存储的分数数据自动转为列（如 2020、2021…），并计算每人年度总分，避免硬编码年份，适配未来新增年份。
在处理时间序列类业务数据（如用户年度得分）时，常需将“长格式”（name, year, score）转换为“宽格式”（name, 2020, 2021, ..., TOTAL），尤其当 Excel 报表需按年份横向展示时。若年份范围随时间增长（如从 2016 持续新增至 2025+），手动维护 CASE WHEN 语句不仅繁琐易错，也违背自动化原则。
核心思路是：先查询数据库中所有实际存在的年份 → 动态拼接 SQL 的 SELECT 子句 → 执行查询 → 构建结构化 DataFrame。该方案完全解耦年份逻辑与代码，无需每年修改 SQL。
以下为完整可执行流程（基于 SQLite/MySQL/PostgreSQL 等主流数据库 + sqlite3/pymysql + pandas）：
import pandas as pd
# 假设已建立数据库连接 conn 和游标 cursor
cursor = conn.cursor()
# 步骤 1：获取全部唯一年份（升序排列，确保列顺序稳定）
cursor.execute("SELECT DISTINCT year FROM scores ORDER BY year")
years = cursor.fetchall() # 返回 [(2020,), (2021,), ...]
if not years:
raise ValueError("No data found in 'scores' table.")
# 步骤 2：动态构建 pivot 字段列表（含年份列和 TOTAL 列）
year_columns = []
coalesce_terms = []
for year_row in years:
year_val = year_row[0]
year_str = str(year_val)
year_columns.append(f"MAX(CASE WHEN year = {year_val} THEN score END) AS `{year_str}`")
coalesce_terms.append(f"COALESCE(MAX(CASE WHEN year = {year_val} THEN score END), 0)")
columns_expr = ", ".join(year_columns)
total_expr = " + ".join(coalesce_terms)
# 步骤 3：组装最终 SQL（注意：使用反引号包裹列名，兼容含数字/特殊字符的别名）
sql = f"""
SELECT
name,
{columns_expr},
{total_expr} AS total
FROM scores
GROUP BY name
ORDER BY name;
"""
# 步骤 4：执行并加载为 DataFrame
cursor.execute(sql)
result_rows = cursor.fetchall()
# 构建列名列表：['name', '2020', '2021', ..., 'total']
column_names = ['name'] + [str(y[0]) for y in years] + ['total']
df = pd.DataFrame(result_rows, columns=column_names)
# 可选：导出为 Excel（需安装 openpyxl）
df.to_excel("annual_scores_pivot.xlsx", index=False)
print(df)
✅ 关键优势说明：
-
全自动适配新增年份：只要新数据插入
scores表（如("Andi", 2024, 49)），下次运行即自动包含2024列； -
安全防错：使用
COALESCE(..., 0)将缺失年份值转为0，避免NULL导致total计算失败； -
列名健壮性：用反引号（
`2020`）替代单引号，防止某些数据库对纯数字别名报错； -
性能友好：仅一次
DISTINCT查询 + 一次主聚合查询，无循环多次访问 DB。
⚠️ 注意事项：
- 若年份数量极大（如 >100 年），需评估数据库对
CASE WHEN数量的限制（多数支持数百级，可忽略）； - 生产环境建议对
year字段添加索引（CREATE INDEX idx_scores_year ON scores(year);）提升DISTINCT和GROUP BY效率； - 如需支持空值显示为
'-'而非0，可将COALESCE(..., 0)替换为COALESCE(CAST(... AS TEXT), '-')（注意类型一致性）； - 使用参数化查询无法解决列名动态化问题（SQL 标准不支持参数化列名），因此字符串拼接在此场景下是合理且安全的——因
year来自受信数据库字段，非用户输入。
通过该方法，你获得的不再是一次性脚本，而是一个可持续演进的数据透视管道，真正实现“写一次，用多年”。
