27,579
社区成员
发帖
与我相关
我的任务
分享
if object_id('[customer]') is not null drop table [customer]
go
create table customer(cst_hometown varchar(50),number int)
insert into customer values('广东省深圳市罗湖区',10000)
insert into customer values('广东省深圳市福田区',20000)
insert into customer values('广东省深圳市宝安区',30000)
insert into customer values('广东省广州市天河区',20000)
insert into customer values('湖南省长沙市河西区',3000)
insert into customer values('湖南省株洲市河A区',7000)
insert into customer values('湖南省常德市河B区',3000)
insert into customer values('湖南省衡阳市河珠晖区',21000)
select SUBSTRING(cst_hometown,0,CHARINDEX('省',cst_hometown)+1) as province ,sum(number)from customer group by
SUBSTRING(cst_hometown,0,CHARINDEX('省',cst_hometown)+1)
province
-------------------------------------------------- -----------
广东省 80000
湖南省 34000
(2 行受影响)
create t2(city nvarchar(20))
insert into t2 select '北京市'
insert into t2 select '河北省'
....
select a.city,sum(b.number)
from t2 a inner join customer b on charindex(a.city,b.cst_hometown)>0
group by a.city
--按省市统计,未测试
SELECT LEFT(cst_hometown,CHARINDEX('省',cst_hometown)) AS PROVINCE
,LEFT(STUFF(cst_hometown,1,CHARINDEX('省',cst_hometown),''),CHARINDEX('市',STUFF(cst_hometown,1,CHARINDEX('省',cst_hometown),''))) AS CITY
,COUNT(1) AS NUMBER
FROM CUSTOMER
WHERE cst_hometown LIKE '%省%市%'
GROUP BY LEFT(cst_hometown,CHARINDEX('省',cst_hometown)),LEFT(STUFF(cst_hometown,1,CHARINDEX('省',cst_hometown),''),CHARINDEX('市',STUFF(cst_hometown,1,CHARINDEX('省',cst_hometown),'')))
create t2(city nvarchar(20))
insert into t2 select '北京市'
insert into t2 select '河北省'
....
select a.city,count(*)
from t2 a inner join customer b on charindex(a.city,b.cst_hometown)>0
group by a.city
--按省统计
SELECT LEFT(cst_hometown,CHARINDEX('省',cst_hometown)) AS PROVINCE,COUNT(1) AS NUMBER
FROM CUSTOMER
WHERE cst_hometown LIKE '%省%'
GROUP BY LEFT(cst_hometown,CHARINDEX('省',cst_hometown))
select
substring(cst_hometown,0,charindex('省',cst_hometown)+1) as province from customer),
sum(number) as number
from
tb
group by
substring(cst_hometown,0,charindex('省',cst_hometown)+1) as province from customer)