select
b.area,
count(1) as [用户数]
from tb_user a
left jion tb_area on left(a.mobile,7)=b.num
group by b.area
这样?
b.area,
count(1) as [用户数]
from tb_user a
left jion tb_area on left(a.mobile,7)=b.num
group by b.area
这样?
解决方案 »
- 如何创建索引
- 做套打时想实现可以预览但有些内容无需打印如何实现 fastreport或reportmachine
- 备份SQL数据库后找不到备份文件了
- 在存储过程中执行一段代码出现的怪现象///好烦呀请高手解决!
- SQL char与text比较取值问题
- 进者有分,SQL问题简单问题。
- 希望能有一个好的SQL语句
- 如何和远程的Microsoft sql server连接,分配给我用户名和密码已经知道了。但我是一头雾水啊
- 求一sql语句,时间段内的统计!!!!!!!!!!!!!!!!!!!!!!!!!
- 求一条SQL语句
- 如何让sql2005 ID自动增加且可以重复,例:1,1,2,2,3,3..........
- [求助]根据长度更新xuhao
select left(a.mobile,7) 号码段 ,b.area 地区,count(a.id) 用户数
from tb_user a,tb_area b
where left(a.mobile,7)=b.num
group by left(a.mobile,7),b.area
from tb_user a,tb_area b
where left(a.mobile,7)=b.num
group by b.area
insert @a select '1300000','北京'
union all select '1300012','天津'declare @b table(id int identity,mobile varchar(20))
insert @b select '1300000qqee'
union all select '1300000aadd'
union all select '1300000aaad'
union all select '1300000aaqd'
union all select '1300012aa3d'
union all select '1300012afdd'select *,用户数=(select count(*) from @b where left(mobile,7)=num)
from @aid num area 用户数
----------- -------------------- -------------------------------------------------- -----------
1 1300000 北京 4
2 1300012 天津 2(2 行受影响)
不好意思,刚才有点笔误---测试数据---
if object_id('[tb_area]') is not null drop table [tb_area]
go
create table [tb_area]([id] int,[num] int,[area] varchar(4))
insert [tb_area]
select 1,1300000,'北京' union all
select 2,1300012,'天津' union all
select 120193,1595879,'浙江'
if object_id('[tb_user]') is not null drop table [tb_user]
go
create table [tb_user]([id] int,[mobile] bigint)
insert [tb_user]
select 1,13800138000 union all
select 2,13000009123 union all
select 3,13000009124 union all
select 4,13000009125 union all
select 5,13000009126 union all
select 6,13000009127 union all
select 7,13000129120 union all
select 8,13000129121 union all
select 9,13000129122
---查询---
select
b.area,
count(1) as [用户数]
from tb_user a
left join tb_area b on left(a.mobile,7)=b.num
group by b.area---结果---
area 用户数
---- -----------
NULL 1
北京 5
天津 3(所影响的行数为 3 行)
select a.* ,用户数=(select count(2) from tb_user where left(mobile,7)=num group by area,left(mobile,7))
from tb_area a
num area
1302325 上海 1301284 上海 1301283 上海 1301282 上海 1301281 上海 1301280 上海
select b.area 地区,count(a.id) 用户数
from tb_user a,tb_area b
where left(a.mobile,7)=b.num
group by b.area这个可以执行,不过很慢
如果是这种情况的话,把表稍微该写,例如上海地区从那个段到哪个段,,然后
select *--可以进行统计之类的
from a,b
where left(b.phone,7) between 起始段 and 终止段另外添加索引试试
if object_id('[tb_area]') is not null drop table [tb_area]
go
create table [tb_area]([id] int,[num] int,[area] varchar(4))
insert [tb_area]
select 1,1300000,'北京' union all
select 2,1300012,'天津' union all
select 120193,1595879,'浙江'
if object_id('[tb_user]') is not null drop table [tb_user]
go
create table [tb_user]([id] int,[mobile] bigint)
insert [tb_user]
select 1,13800138000 union all
select 2,13000009123 union all
select 3,13000009124 union all
select 4,13000009125 union all
select 5,13000009126 union all
select 6,13000009127 union all
select 7,13000129120 union all
select 8,13000129121 union all
select 9,13000129122
---插入临时表---
select
left(mobile,7) as num,
count(1) as [用户数]
into #
from tb_user
group by left(mobile,7)---查询---
select
b.area,
sum(a.[用户数]) as [用户数]
from # a
left join tb_area b on a.num=b.num
group by b.areadrop table #---结果---
area 用户数
---- -----------
NULL 1
北京 5
天津 3(所影响的行数为 3 行)