时间戳

首页 > 解决方案 > 如何根据 SQL 数据库中的其他字段向字段添加数据

sql - 如何根据 SQL 数据库中的其他字段向字段添加数据

问题描述

我有一个名为wcvp从世界维管植物清单下载的 csv 文件构建的 SQLite 表（参见https://wcvp.science.kew.org/和http://sftp.kew.org/pub/data-repositories /WCVP/ )。当我运行此查询时：

sqlite> SELECT kew_id, genus, species, infraspecies 
        FROM wcvp 
        WHERE genus = 'Quercus' 
          AND species = 'robur' 
          AND taxonomic_status = 'Accepted';

我得到这个结果：

kew_id 属 物种 亚种
304293-2 栎属 罗伯
77189540-1 栎属 罗伯 螳螂
77189379-1 栎属 罗伯 布鲁蒂亚
77189383-1 栎属 罗伯 伊梅雷蒂娜
60459295-2 栎属 罗伯 花序梗
77171868-1 栎属 罗伯 罗伯

我想向表中添加一列（其中包含数十万行）number_of_infraspecies，如下所示：

kew_id 属 物种 亚种 number_of_infraspecies
304293-2 栎属 罗伯 5
77189540-1 栎属 罗伯 螳螂 无效的
77189379-1 栎属 罗伯 布鲁蒂亚 无效的
77189383-1 栎属 罗伯 伊梅雷蒂娜 无效的
60459295-2 栎属 罗伯 花序梗 无效的
77171868-1 栎属 罗伯 罗伯 无效的

或者，我可以构建一个包含两列的新表：kew_id作为外键和number_of_infraspecies另一列。

无论我采用哪种方法，我都只能想到一个过程，该过程将导致对wcvp表的每一行或至少在 infraspecies 列中没有值的那些行进行单独查询（AND taxonomic_status = 'Accepted'）。

有没有办法用一个或几个查询来做到这一点？

标签： sqlsqliteviewcountwindow-functions

解决方案

创建一个VIEW返回列的number_of_infraspecies：

CREATE VIEW my_view AS
SELECT kew_id, genus, species, infraspecies,
       CASE 
         WHEN infraspecies IS NULL 
           THEN COUNT(infraspecies) OVER (PARTITION BY genus, species)
       END number_of_infraspecies
FROM wcvp
WHERE taxonomic_status = 'Accepted';

然后从那个VIEW特定的genus和中选择species：

SELECT kew_id, genus, species, infraspecies, number_of_infraspecies
FROM my_view
WHERE genus = 'Quercus' AND species = 'robur';

请参阅演示。

推荐阅读