SQL 仅获取域名

Question

from ===> to

https://www.google.com/asdkasjdhask  ===> google
https://google.com/asdkasjdhask  ===> google
https://google.comasd/asdkasjdhask  ===> google
https://www.google.be/dddasdkasjdhask  ===> google
https://www.google.tr/asdkasjdhask  ===> google
https://www.google.as/asdkasjdhask  ===> google
https://www.google.ur/asdkasjdhask  ===> google
https://www.bing.com/asdkasjdhask  ===> bing
https://bing.com/asdkasjdhask  ===> bing
https://bing.comasd/asdkasjdhask  ===> bing
https://www.bing.be/dddasdkasjdhask  ===> bing
https://www.bing.tr/asdkasjdhask  ===> bing
https://www.bing.as/asdkasjdhask  ===> bing
https://www.bing.ur/asdkasjdhask  ===> bing

和计数 示例 7 来自谷歌，6 来自必应，2 来自 YouTube 等。

我试过SELECT count(*) .......但没有按照我想要的方式工作

Mysql

Answer 1

您可以使用文本函数提取域：

SELECT LEFT(c, INSTR(c, '.')-1) AS site, COUNT(*)
FROM (SELECT REPLACE(REPLACE(url,'https://', ''), 'www.', '') AS c
FROM tab) sub
GROUP BY LEFT(c, INSTR(c, '.')-1);

数据库<>小提琴演示