mysql - 如何查询所有具有共享模式的记录

Question

我有一列包含网址。一些(但不是全部)网址共享相同的域。例如:aaa.yahoo.com、bbb.yahoo.com、ccc.yahoo.com。这些 url 存储在一个唯一的字段中。此外，同一张表还有一个自增主键。

我需要做并需要帮助的是:查询共享相同域名的所有网址。请注意，我没有可以在查询中输入确切的域名，数据库中有很多可用的共享域名，我想将它们全部查询。

编辑:这是我尝试过的命令，但完全不确定，因为我在 SQL 方面没有太多经验:

SELECT domainname FROM DB.TABLE
WHERE SUBSTRING(domainname, LOCATE('.', domainname))
IN(
    SELECT   domainname
    FROM     DB.TABLE
    GROUP BY SUBSTRING(domainname, LOCATE('.', doaminname)) 
);

Answer 1

如果我没理解错的话...

SELECT
    COUNT(`domainname`) AS 'occ'
    ,SUBSTRING(`domainname`,LOCATE('.',`domainname`)+1) AS 'domain'
FROM `the_table`
GROUP BY SUBSTRING(`domainname`,LOCATE('.',`domainname`)+1)
HAVING `occ`>1
ORDER BY `occ` DESC,`domain` ASC

HAVING... 行将过滤掉表中仅出现 1 次的域。

---

编辑:

代表您的评论...因为您无法知道以下示例中的哪个是域(除非您针对大量国家/地区代码、域扩展名等测试这些值)

• 域名.co.uk
• 子域名.domain.co

您无法以编程方式检测这些。如果您打算依赖分机或国家分机，那也行不通。您可以做的是进行智能排序并通过您的眼睛挑选那些，除非您采用上述解决方案。

例如，

SELECT
    `id`
    ,`d` AS 'domain'
    ,IF(CHAR_LENGTH(`d`)-CHAR_LENGTH(REPLACE(`d`,'.',''))=2,
        SUBSTRING_INDEX(`d`,'.',1),'') AS 'f1'
    ,SUBSTRING_INDEX(SUBSTRING_INDEX(`d`,'.',-2),'.',1) AS 'f2'
    ,SUBSTRING_INDEX(`d`,'.',-1) AS 'f3'
FROM (
    SELECT
        `id`
        ,IF(CHAR_LENGTH(`domainname`)-CHAR_LENGTH(REPLACE(`domainname`,'.',''))>2,
            SUBSTRING_INDEX(`domainname`,'.',-3),`domainname`) AS 'd'
    FROM `the_table`
    GROUP BY `d`
) t
ORDER BY `f3`,`f2`,`f1`

可能会帮助你看到它。请注意，您无法按 f2 和 f3 对结果进行分组，因为您可能有

• example1.yahoo.com
• example2.yahoo.com

但你可能也有

• yahoo.com.br
• google.com.br