有两个表,想根据用户的手机号码统计所在地区的用户数,由于数据量较大,还未找到解决办法,请各位给点意见,谢谢了!tb_area表(120193条记录)id(主键) , num(手机号前七位) ,area(地区)
1 1300000 北京
2 1300012 ,天津
.
.
.
120193 1595879 ,浙江tb_user表(几百万级的记录)
id(主键), mobile(手机号)
1 13800138000
.
.
.
1 1300000 北京
2 1300012 ,天津
.
.
.
120193 1595879 ,浙江tb_user表(几百万级的记录)
id(主键), mobile(手机号)
1 13800138000
.
.
.
b.area as [地区],
count(1) as [用户数]
from tb_user a
left jion tb_area on left(a.mobile,7)=b.num
group by b.area
select a.anum,b.area,a.cnt
from (
select left(a.mobile,7) as anum,count(*) as cnt
from tb_user
group by left(a.mobile,7)
) a inner join tb_area on a.anum=b.num
在此字段上建立索引,再与工作表连接
select
b.area as [地区],
count(1) as [用户数]
from tb_area a
left join on tb_user on b.临时字段=a.num
group by a.area也可以将LEFT JOIN->INNER JOIN
调大一点试试.