如何在SQL中动态去除域名的顶级域名（TLD）并统计？

以下针对主流数据库（MySQL、SQL Server）给出具体实现，你可以根据自己使用的数据库选择对应方案：

一、MySQL 实现 #

方法1：硬编码常见多段顶级域名（适合固定场景） #

直接通过CASE WHEN优先匹配.co.uk、.com.au这类多段顶级域名，再处理普通单段顶级域名：

SELECT
  CASE
    -- 按需求添加需要匹配的多段顶级域名
    WHEN domain LIKE '%.co.uk' THEN SUBSTRING_INDEX(domain, '.co.uk', 1)
    WHEN domain LIKE '%.com.au' THEN SUBSTRING_INDEX(domain, '.com.au', 1)
    WHEN domain LIKE '%.org.uk' THEN SUBSTRING_INDEX(domain, '.org.uk', 1)
    -- 处理单段顶级域名（如.com/.net），同时兼容无点的纯域名
    ELSE CASE WHEN LOCATE('.', domain) > 0 THEN SUBSTRING_INDEX(domain, '.', 1) ELSE domain END
  END AS main_domain,
  COUNT(*) AS occurrence_count
FROM domains -- 替换为你的表名
GROUP BY main_domain
ORDER BY occurrence_count DESC;

方法2：关联顶级域名表（适合需频繁更新顶级域名的场景） #

先创建一个存储顶级域名的表top_level_domains，字段tdl存储如co.uk、com、org.uk等值，再通过关联查询匹配最长的顶级域名：

SELECT
  SUBSTRING_INDEX(d.domain, CONCAT('.', t.tdl), 1) AS main_domain,
  COUNT(*) AS occurrence_count
FROM domains d
JOIN top_level_domains t ON d.domain LIKE CONCAT('%.', t.tdl)
-- 确保匹配最长的顶级域名，避免短域名先匹配（比如避免.co.uk被当成.uk处理）
WHERE LENGTH(t.tdl) = (
  SELECT MAX(LENGTH(tdl))
  FROM top_level_domains
  WHERE d.domain LIKE CONCAT('%.', tdl)
)
GROUP BY main_domain
ORDER BY occurrence_count DESC;

二、SQL Server 实现 #

基础硬编码方案 #

用LEFT和CHARINDEX替代MySQL的SUBSTRING_INDEX，逻辑和MySQL方法1一致：

WITH domain_cte AS (
  SELECT
    CASE
      WHEN domain LIKE '%.co.uk' THEN LEFT(domain, CHARINDEX('.co.uk', domain) - 1)
      WHEN domain LIKE '%.com.au' THEN LEFT(domain, CHARINDEX('.com.au', domain) - 1)
      WHEN domain LIKE '%.org.uk' THEN LEFT(domain, CHARINDEX('.org.uk', domain) - 1)
      -- 兼容无点的纯域名
      ELSE CASE WHEN CHARINDEX('.', domain) > 0 THEN LEFT(domain, CHARINDEX('.', domain) - 1) ELSE domain END
    END AS main_domain
  FROM domains -- 替换为你的表名
)
SELECT main_domain, COUNT(*) AS occurrence_count
FROM domain_cte
GROUP BY main_domain
ORDER BY occurrence_count DESC;

关键注意事项 #

• 请根据实际业务中的顶级域名类型，补充CASE WHEN中的匹配规则，比如添加.gov.uk、.net.au等。
• 若存在无点的纯域名（如example），上述方案中的兼容逻辑会直接保留原域名，避免报错。

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