如何在SQL中创建累计实例数列及特定逻辑的主键？

1. 创建跟踪累计实例数量的列 #

通过窗口函数即可实现，根据累计逻辑的不同，有两种常用写法：

• 给每个Month+Name分组内的行分配序号（同一分组下第1个实例为1，第2个为2，以此类推），用ROW_NUMBER()：

SELECT
  Month,
  Name,
  -- 保留原有其他列
  ROW_NUMBER() OVER (PARTITION BY Month, Name ORDER BY Month) AS Instance_Count
FROM your_table;

注：ORDER BY Month可替换为表中唯一排序字段（比如主键ID），确保序号顺序稳定。

• 统计到当前行为止，同一Month+Name分组内的累计总数（结果和序号一致，逻辑为累计计数），用COUNT()窗口函数：

SELECT
  Month,
  Name,
  -- 保留原有其他列
  COUNT(*) OVER (PARTITION BY Month, Name ORDER BY Month ROWS UNBOUNDED PRECEDING) AS Instance_Count
FROM your_table;

2. 生成符合要求的主键 #

基于上面的Instance_Count，用字符串拼接函数（如CONCAT()）将Month、Name和累计次数组合成主键列，再设置主键约束即可。

示例1：查询时临时生成主键 #

SELECT
  Month,
  Name,
  ROW_NUMBER() OVER (PARTITION BY Month, Name ORDER BY Month) AS Instance_Count,
  -- 若Month是日期类型，建议格式化后再拼接，避免格式混乱
  CONCAT(DATE_FORMAT(Month, '%Y-%m'), '_', Name, '_', Instance_Count) AS PK_Column
FROM your_table;

示例2：修改现有表添加物理主键列 #

如果需要将主键作为永久列添加到表中，执行以下步骤：

-- 1. 添加累计实例列
ALTER TABLE your_table ADD COLUMN Instance_Count INT;

-- 2. 更新累计实例列值（假设表有唯一ID列`id`用于排序）
UPDATE your_table t1
JOIN (
  SELECT 
    id,
    ROW_NUMBER() OVER (PARTITION BY Month, Name ORDER BY id) AS rn
  FROM your_table
) t2 ON t1.id = t2.id
SET t1.Instance_Count = t2.rn;

-- 3. 添加主键列
ALTER TABLE your_table ADD COLUMN PK_Column VARCHAR(100);

-- 4. 更新主键列值
UPDATE your_table
SET PK_Column = CONCAT(DATE_FORMAT(Month, '%Y-%m'), '_', Name, '_', Instance_Count);

-- 5. 设置主键约束
ALTER TABLE your_table ADD PRIMARY KEY (PK_Column);

内容的提问来源于stack exchange，提问作者Ethan Altmayer