如何在SQLite中基于关联字段为指定字段批量填充统计数据

实现方案 #

完全可以用1~2条SQL语句完成需求，无需逐行查询，两种实现方式如下：

方式一：直接修改原表新增字段 #

步骤1：添加新字段

ALTER TABLE wcvp ADD COLUMN number_of_infraspecies INTEGER;

步骤2：批量更新字段值

用关联更新一次性计算所有物种的种下类群数量，仅对物种级记录（infraspecies为空）赋值，种下类群的该字段自动保留NULL：

UPDATE wcvp AS parent
SET number_of_infraspecies = infraspecies_count.cnt
FROM (
    -- 先统计每个属+种组合下的Accepted种下类群总数
    SELECT genus, species, COUNT(*) AS cnt
    FROM wcvp
    WHERE taxonomic_status = 'Accepted'
      AND infraspecies IS NOT NULL 
      AND infraspecies != ''
    GROUP BY genus, species
) AS infraspecies_count
WHERE 
    -- 仅更新Accepted的物种级记录
    parent.taxonomic_status = 'Accepted'
    AND (parent.infraspecies IS NULL OR parent.infraspecies = '')
    AND parent.genus = infraspecies_count.genus
    AND parent.species = infraspecies_count.species;

如果使用的是3.33.0之前不支持UPDATE FROM的旧版本SQLite，可以改用兼容写法：

UPDATE wcvp
SET number_of_infraspecies = (
    SELECT COUNT(*)
    FROM wcvp AS sub
    WHERE sub.genus = wcvp.genus
      AND sub.species = wcvp.species
      AND sub.taxonomic_status = 'Accepted'
      AND sub.infraspecies IS NOT NULL
      AND sub.infraspecies != ''
)
WHERE 
    taxonomic_status = 'Accepted'
    AND (infraspecies IS NULL OR infraspecies = '');

方式二：新建关联表存储统计结果 #

不需要修改原表结构，直接生成一张带外键kew_id的统计结果表：

CREATE TABLE species_infraspecies_count AS
SELECT 
    parent.kew_id,
    infraspecies_count.cnt AS number_of_infraspecies
FROM wcvp AS parent
JOIN (
    SELECT genus, species, COUNT(*) AS cnt
    FROM wcvp
    WHERE taxonomic_status = 'Accepted'
      AND infraspecies IS NOT NULL 
      AND infraspecies != ''
    GROUP BY genus, species
) AS infraspecies_count
ON parent.genus = infraspecies_count.genus
AND parent.species = infraspecies_count.species
WHERE 
    parent.taxonomic_status = 'Accepted'
    AND (parent.infraspecies IS NULL OR parent.infraspecies = '');

性能优化建议 #

如果表数据量较大，可以提前给genus、species、taxonomic_status、infraspecies字段建联合索引，能大幅加快统计和更新速度：

CREATE INDEX idx_wcvp_taxonomy ON wcvp (genus, species, taxonomic_status, infraspecies);

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