34,590
社区成员
发帖
与我相关
我的任务
分享
select
[address]=left([address],charindex('省',[address])),记录=count(1)
from
customer
where
convert(varchar(7),CreateDate,120)='2007-10'--10月,createdate为录入时间
group by left([address],charindex('省',[address]))
/*
如果有这样的数据(没有“省”字):江西南昌……
建表:
*/
create table State (State varchar(20))
insert into State select '江西'
insert into State select '浙江'
....
select a.State,count(*) from State a join 客户资料表 on a.State=left(b.客户地址,len(a.State)) group by a.State
select
[显示信息]=[address]+'有'+rtrim(记录)+'家客户 '
from
(select
[address]=left([address],charindex('省',[address])),记录=count(1)
from
customer
group by left([address],charindex('省',[address]))
)Tmp
--如果都是XX省的数据数据:
select left(客户地址,charindex('省',客户地址)),count(*) from 客户资料表 group by left(客户地址,charindex('省',客户地址))
select
[address]=left([address],charindex('省',[address])),记录=count(1)
from
customer
group by left([address],charindex('省',[address]))