select SUBSTRING_INDEX(SUBSTRING_INDEX(字段名,'.',2) ,'.',-1),count(SUBSTRING_INDEX(SUBSTRING_INDEX(字段名,'.',2) ,'.',-1)) from 表名 group by SUBSTRING_INDEX(SUBSTRING_INDEX(字段名,'.',2) ,'.',-1) order by 2 desc limit 6;
mysql> select * from url; +-------------------------------+ | url | +-------------------------------+ | http://www.baidu.com/ | | http://www.baidu.com/a/b/c/d/ | | http://www.sina.com | | www.google.cn | | www.sina.com/comm/ | +-------------------------------+ 5 rows in set (0.00 sec)mysql> select count(substring(url,instr(url,'.')+1, -> instr(substring(url,instr(url,'.')+1),'.')-1)) as nCount, -> substring(url,instr(url,'.')+1, -> instr(substring(url,instr(url,'.')+1),'.')-1) as address -> from url -> group by -> (substring(url,instr(url,'.')+1, -> instr(substring(url,instr(url,'.')+1),'.')-1) -> ) -> order by count(*) desc; +--------+---------+ | nCount | address | +--------+---------+ | 2 | baidu | | 2 | sina | | 1 | google | +--------+---------+ 3 rows in set (0.00 sec)
mysql> set @url="http://www.baidu.com/"; Query OK, 0 rows affected (0.00 sec)mysql> select @url; +-----------------------+ | @url | +-----------------------+ | http://www.baidu.com/ | +-----------------------+ 1 row in set (0.00 sec)mysql> set @url1=substring(@url,instr(@url,'.')+1,length(@url)-instr(@url,'.'));Query OK, 0 rows affected (0.00 sec)mysql> select @url1; +------------+ | @url1 | +------------+ | baidu.com/ | +------------+ 1 row in set (0.00 sec) mysql> select substring(@url1,1,instr(@url1,'.')-1); +---------------------------------------+ | substring(@url1,1,instr(@url1,'.')-1) | +---------------------------------------+ | baidu | +---------------------------------------+ 1 row in set (0.00 sec)
报错了 ERROR 1305 (42000): FUNCTION url.SUBSTRING does not exist
from 表名
group by SUBSTRING_INDEX(SUBSTRING_INDEX(字段名,'.',2) ,'.',-1)
order by 2 desc
limit 6;
mysql> select * from url;
+-------------------------------+
| url |
+-------------------------------+
| http://www.baidu.com/ |
| http://www.baidu.com/a/b/c/d/ |
| http://www.sina.com |
| www.google.cn |
| www.sina.com/comm/ |
+-------------------------------+
5 rows in set (0.00 sec)mysql> select count(substring(url,instr(url,'.')+1,
-> instr(substring(url,instr(url,'.')+1),'.')-1)) as nCount,
-> substring(url,instr(url,'.')+1,
-> instr(substring(url,instr(url,'.')+1),'.')-1) as address
-> from url
-> group by
-> (substring(url,instr(url,'.')+1,
-> instr(substring(url,instr(url,'.')+1),'.')-1)
-> )
-> order by count(*) desc;
+--------+---------+
| nCount | address |
+--------+---------+
| 2 | baidu |
| 2 | sina |
| 1 | google |
+--------+---------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)mysql> select @url;
+-----------------------+
| @url |
+-----------------------+
| http://www.baidu.com/ |
+-----------------------+
1 row in set (0.00 sec)mysql> set @url1=substring(@url,instr(@url,'.')+1,length(@url)-instr(@url,'.'));Query OK, 0 rows affected (0.00 sec)mysql> select @url1;
+------------+
| @url1 |
+------------+
| baidu.com/ |
+------------+
1 row in set (0.00 sec)
mysql> select substring(@url1,1,instr(@url1,'.')-1);
+---------------------------------------+
| substring(@url1,1,instr(@url1,'.')-1) |
+---------------------------------------+
| baidu |
+---------------------------------------+
1 row in set (0.00 sec)
报错了
ERROR 1305 (42000): FUNCTION url.SUBSTRING does not exist