有这样一组记录
http://my.donews.com/yeer/2006/10/06/download-video-from-youtube
http://www.pcpie.com/search/我的功夫女友
http://www.tvmao.com/program/CCTV-CCTV5-w7.html
http://www.newhua.com/soft/59655.htm
http://www.baidu.com/soft/38031.html
http://www.pcpie.com/search/恐龙战队
http://cache.baidu.com/s?wd=疾风请问怎么样用sql语句才能达到以下效果(使相同的域名自动归为一类):
输出
url total
donews.com 1
pcpie.com 2
tvmao.com 1
baidu.com 2
......
http://my.donews.com/yeer/2006/10/06/download-video-from-youtube
http://www.pcpie.com/search/我的功夫女友
http://www.tvmao.com/program/CCTV-CCTV5-w7.html
http://www.newhua.com/soft/59655.htm
http://www.baidu.com/soft/38031.html
http://www.pcpie.com/search/恐龙战队
http://cache.baidu.com/s?wd=疾风请问怎么样用sql语句才能达到以下效果(使相同的域名自动归为一类):
输出
url total
donews.com 1
pcpie.com 2
tvmao.com 1
baidu.com 2
......
CREATE TABLE `lk4` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`url` varchar(1000) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
insert into lk4(url) values
('http://my.donews.com/yeer/2006/10/06/download-video-from-youtube'),
('http://www.pcpie.com/search/我的功夫女友'),
('http://www.tvmao.com/program/CCTV-CCTV5-w7.html'),
('http://www.newhua.com/soft/59655.htm'),
('http://www.baidu.com/soft/38031.html'),
('http://www.pcpie.com/search/恐龙战队'),
('http://cache.baidu.com/s?wd=疾风');
select substring(url,instr(url,'.')+1,locate('/',url,8)-instr(url,'.')-1) url,count(1) total from lk4
group by substring(url,instr(url,'.')+1,locate('/',url,8)-instr(url,'.')-1)
order by id;
query result(5 records)
url total
donews.com 1
pcpie.com 2
tvmao.com 1
newhua.com 1
baidu.com 2