如何使用SQLite统计To、CC、BCC字段中的有效唯一邮箱数量？

我正在使用以下查询语句，该语句每行仅返回一条结果，但我知道字段中存储了多个以分号分隔的邮箱地址：

SELECT UID, EmailToField,
EmailToField REGEXP '[a-zA-Z0-9+._-]+@[a-zA-Z0-9._-]+\.[a-zA-Z0-9_-]+' AS valid_emailTo
FROM table

数据库中的示例数据：

期望得到的结果：

请问是否存在SQLite查询语句，可统计To、CC、BCC这三个独立邮箱字段中的有效唯一邮箱地址数量？

解决方案 #

SQLite没有内置的字符串拆分函数，但可以通过递归CTE拆分三个字段里的分号分隔邮箱，结合正则验证、去重后统计总数。完整查询如下：

WITH split_emails AS (
    -- 拆分EmailTo字段
    SELECT UID, TRIM(REPLACE(SUBSTR(EmailTo, 1, INSTR(EmailTo || ';', ';') - 1), ' ', '')) AS email
    FROM your_table
    WHERE EmailTo IS NOT NULL AND EmailTo != ''
    UNION ALL
    SELECT UID, TRIM(REPLACE(SUBSTR(EmailTo, INSTR(EmailTo || ';', ';') + 1), ' ', '')) AS email
    FROM your_table
    JOIN split_emails USING (UID)
    WHERE INSTR(EmailTo || ';', ';') > 0
      AND SUBSTR(EmailTo, INSTR(EmailTo || ';', ';') + 1) != ''
    UNION ALL
    -- 拆分EmailCC字段
    SELECT UID, TRIM(REPLACE(SUBSTR(EmailCC, 1, INSTR(EmailCC || ';', ';') - 1), ' ', '')) AS email
    FROM your_table
    WHERE EmailCC IS NOT NULL AND EmailCC != ''
    UNION ALL
    SELECT UID, TRIM(REPLACE(SUBSTR(EmailCC, INSTR(EmailCC || ';', ';') + 1), ' ', '')) AS email
    FROM your_table
    JOIN split_emails USING (UID)
    WHERE INSTR(EmailCC || ';', ';') > 0
      AND SUBSTR(EmailCC, INSTR(EmailCC || ';', ';') + 1) != ''
    UNION ALL
    -- 拆分EmailBCC字段
    SELECT UID, TRIM(REPLACE(SUBSTR(EmailBCC, 1, INSTR(EmailBCC || ';', ';') - 1), ' ', '')) AS email
    FROM your_table
    WHERE EmailBCC IS NOT NULL AND EmailBCC != ''
    UNION ALL
    SELECT UID, TRIM(REPLACE(SUBSTR(EmailBCC, INSTR(EmailBCC || ';', ';') + 1), ' ', '')) AS email
    FROM your_table
    JOIN split_emails USING (UID)
    WHERE INSTR(EmailBCC || ';', ';') > 0
      AND SUBSTR(EmailBCC, INSTR(EmailBCC || ';', ';') + 1) != ''
),
valid_unique_emails AS (
    SELECT DISTINCT UID, email
    FROM split_emails
    WHERE email REGEXP '[a-zA-Z0-9+._-]+@[a-zA-Z0-9._-]+\.[a-zA-Z0-9_-]+'
)
SELECT UID, COUNT(email) AS validEmailToCcBcc_count
FROM valid_unique_emails
GROUP BY UID;

关键步骤说明： #

• 递归拆分字段：通过CTE split_emails 分别处理三个邮箱字段，拆分分号分隔的条目，同时清理邮箱前后的空格。
• 过滤有效且唯一的邮箱：在valid_unique_emails中用正则验证邮箱格式，并用DISTINCT去除重复的邮箱地址。
• 分组统计：最终按UID分组，统计每个UID对应的有效唯一邮箱总数。

注意事项： #

1. 将查询中的your_table替换为你的实际表名。
2. 若字段可能为空，查询已通过IS NOT NULL AND != ''过滤空值，避免无效拆分。
3. SQLite默认未开启REGEXP功能，若无法使用，可改用简单的字符串匹配逻辑替代：

   WHERE email LIKE '%@%.%' 
     AND email NOT LIKE '%@%@%'
     AND email NOT LIKE '%..%'
   

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