SQLite如何匹配指定列值并将符合条件的全列数据复制到新表

首先明确两个基础前提：

• 假设你的源地址表名为us_addresses，你可以替换为自己实际的源表名
• SQLite 不支持直接按列的位置序号写筛选条件，你需要先拿到源表第2列的实际列名，执行下面的语句就能查到所有列的信息，返回结果中cid为1的行（列序号从0开始计数）对应的name值就是第2列的列名，下文示例中假设该列列名为state_abbr，存储州的两位缩写标识：

PRAGMA table_info(us_addresses);

单州表拆分步骤（以印第安纳州IN为例） #

1. 先创建和源表结构完全一致的目标表，不用手动写42列的定义，SQLite原生支持一键复制表结构：

-- 建表，结构和源表完全一致，不带数据
CREATE TABLE IF NOT EXISTS Indiana LIKE us_addresses;

如果你的SQLite版本较旧不支持LIKE语法，可以用下面的语句实现同样效果：

CREATE TABLE IF NOT EXISTS Indiana AS SELECT * FROM us_addresses WHERE 0;

2. 将符合条件的全列数据插入目标表，因为两张表列结构、顺序完全一致，不需要单独指定列名：

INSERT INTO Indiana
SELECT * FROM us_addresses
WHERE state_abbr = 'IN';

批量拆分所有州的优化方案 #

如果需要一次性拆分全部州的数据，不用逐个手动写SQL，可以先维护一张州缩写和全称的映射表（比如叫state_ref，包含abbr存两位缩写、state_name存州全称两个字段），用SQL直接生成所有需要执行的建表、插入语句：

-- 生成所有州的建表语句，执行查询后把结果复制出来批量运行即可
SELECT 'CREATE TABLE IF NOT EXISTS ' || state_name || ' LIKE us_addresses;'
FROM state_ref;

-- 生成所有州的数据插入语句
SELECT 'INSERT INTO ' || state_name || ' SELECT * FROM us_addresses WHERE state_abbr = ''' || abbr || ''';'
FROM state_ref;

注意事项 #

• 如果源表数据量较大，建议先给州缩写列建索引，能大幅提升筛选插入的速度：

CREATE INDEX IF NOT EXISTS idx_addr_state ON us_addresses(state_abbr);

• 只要目标表是严格通过上述复制结构的方式创建的，列顺序和源表完全一致，用SELECT *插入就不会出现列错位的问题，不要手动修改目标表的列顺序。
• 如果是一次性拆分静态数据，不需要额外加触发器、视图之类的冗余逻辑；如果需要后续源表数据变更时自动同步到对应州表，再按需配置触发器即可。

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