在Snowflake SQL中为DUPLICATES表添加唯一行ID遇报错求助

你遇到的问题是无法在通过CREATE TABLE AS SELECT (CTAS)创建的表上直接添加IDENTITY列——这在很多数据库（比如Snowflake，从你的语法和报错来看大概率是它）里是有明确限制的，因为CTAS创建的表在结构定义逻辑上和常规CREATE TABLE创建的表有差异，不支持直接追加IDENTITY属性。

下面给你两种可行的解决方案，根据你的实际情况选择：

方案1：重新创建表时直接生成唯一ID #

最省心的方式是在初始创建duplicates表的时候，就用窗口函数生成唯一的行ID，这样不需要后续修改表结构：

CREATE TABLE duplicates AS
SELECT 
    ROW_NUMBER() OVER (ORDER BY _count DESC, "a", "b") AS id, -- 按计数降序+ab列排序生成连续唯一ID
    "a", 
    "b", 
    COUNT(*) AS _count 
FROM "table" 
GROUP BY "a", "b" 
HAVING _count > 1 
ORDER BY _count DESC;

这里用ROW_NUMBER()生成连续的整数ID，排序规则可以根据你的需求调整——如果不需要特定顺序，也可以用OVER ()生成无排序的ID，但建议指定排序规则，保证每次执行的ID结果一致。

方案2：给已存在的duplicates表添加唯一ID列 #

如果你不想重建表，可以通过创建序列+添加带默认值的列的方式实现自增唯一ID：

步骤1：创建一个自增序列 #

CREATE SEQUENCE IF NOT EXISTS duplicates_id_seq START = 1 INCREMENT = 1;

步骤2：添加ID列并自动填充已有行 #

ALTER TABLE duplicates ADD COLUMN id INT DEFAULT duplicates_id_seq.NEXTVAL;

执行这条语句后，数据库会自动用序列的下一个值填充已有行的id列，后续插入新行时也会自动生成唯一ID。

可选：确保后续插入自动生成ID #

如果之后还要往duplicates表插入数据，想让id列自动递增，可执行这条语句确认默认值绑定：

ALTER TABLE duplicates ALTER COLUMN id SET DEFAULT duplicates_id_seq.NEXTVAL;

针对其他数据库的补充（比如SQL Server） #

如果你的数据库是SQL Server，由于它的CTAS语法不支持直接生成IDENTITY列，你可以改用SELECT INTO语句实现：

SELECT 
    IDENTITY(INT, 1, 1) AS id,
    "a", 
    "b", 
    COUNT(*) AS _count 
INTO duplicates
FROM "table" 
GROUP BY "a", "b" 
HAVING COUNT(*) > 1 
ORDER BY COUNT(*) DESC;

SELECT INTO会直接创建新表并同时生成带IDENTITY属性的id列，完美适配你的需求。

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