时间戳

首页 > 解决方案 > 计算列中的相似条目

sqlite - 计算列中的相似条目

问题描述

假设我正在计算人口普查中的姓氏，如下所示：

select surname, count(*) from census where (surname like "Mc%" or surname like "Mac%" or surname like "Mag%") group by surname order by count(*) desc;

并获得以下信息：

McGee 1000
McDonald 990
Mc Gee 221
Magee 210
McGinn 200
Macdonald 100    
Maginn 30

sqlite 有没有办法获得以下结果，或者我应该坚持对这个结果进行编程：

MACGEE 1221
MACDONALD 1090     
MAGEE 210   
MACGINN 200
MAGINN 30

所以我将 McX、Mc X 和 MacX 归为一个姓氏。

标签： sqlite

解决方案

使用 CTE 来返回结果中所需的所有前缀及其替换（如果有）并将其连接到表中。
进行所有必要的替换，然后按生成的姓氏分组：

WITH cte(prefix, replacement) AS (VALUES ('MC', 'MAC'), ('MAC', 'MAC'), ('MAG', 'MAG'))
SELECT UPPER(c.replacement || SUBSTR(REPLACE(t.surname, ' ', ''), LENGTH(c.prefix) + 1)) name,
       COUNT(*) total 
FROM tablename t INNER JOIN cte c
ON t.surname LIKE c.prefix || '%'
GROUP BY name

请参阅演示。

推荐阅读