合计的时候要去code的前两位相同合计到如同110000里面去 --------------------------- 有这个编码规则就好处理了 在你列出的数据的基础上处理:select home_district=min(home_district), name=min(name), count=sum(count) from tb where left(home_district,2)<>'42' group by left(home_district,2)unionselect home_district=min(home_district), name=min(name), count=sum(count) from tb where left(home_district,2)='42' group by left(home_district,2)
根据要求改一下select home_district=min(home_district), name=min(name), count=sum(count) from tb where left(home_district,2)<>'42' group by left(home_district,2)unionselect home_district, name, count from tb where left(home_district,2)='42'
create table test ( home_district varchar(20), name varchar(200), count int )insert into test select '110000', '北京市', 10 insert into test select '110001', '北京市朝阳区', 20 insert into test select '110002', '北京市海啶区',20 insert into test select '120000', '天津市' ,8 insert into test select '120001', '天津市**区' ,6 insert into test select '120031', '天津市**区' , 10 insert into test select '420000', '湖北省' ,3000 insert into test select '420100', '湖北省武汉市', 2000 insert into test select '420200', '湖北省武汉市江岸区', 300 insert into test select '420300', '湖北省武汉市武昌区', 200 select substring(a.home_district,1,2) + '0000' as dis,sum(count) from test a where substring(home_district,1,2) = substring(home_district,1,2) group by substring(a.home_district,1,2) + '0000'drop table test 110000 50 120000 24 420000 5500
修改一下 create table test ( home_district varchar(20), name varchar(200), count int )insert into test select '110000', '北京市', 10 insert into test select '110001', '北京市朝阳区', 20 insert into test select '110002', '北京市海啶区',20 insert into test select '120000', '天津市' ,8 insert into test select '120001', '天津市**区' ,6 insert into test select '120031', '天津市**区' , 10 insert into test select '420000', '湖北省' ,3000 insert into test select '420100', '湖北省武汉市', 2000 insert into test select '420200', '湖北省武汉市江岸区', 300 insert into test select '420300', '湖北省武汉市武昌区', 200 select substring(home_district,1,2) + '0000' as dis,(select name from test where home_district = substring(a.home_district,1,2) +'0000') as cc,sum(count) from test a where substring(home_district,1,2) = substring(home_district,1,2) group by substring(home_district,1,2) + '0000' select name from test where home_district = '110000' --select * from test drop table test 110000 北京市 50 120000 天津市 24 420000 湖北省 5500
declare @t table(home_district varchar(20),[name] varchar(200),[count] int)insert into @t select '110000', '北京市', 10 union all select '110001', '北京市朝阳区', 20 union all select '110002', '北京市海啶区',20 union all select '120000', '天津市' ,8 union all select '120001', '天津市**区' ,6 union all select '120031', '天津市**区' , 10 union all select '420000', '湖北省' ,3000 union all select '420100', '湖北省武汉市', 2000 union all select '420200', '湖北省武汉市江岸区', 300 union all select '420300', '湖北省武汉市武昌区',200 union all select '420400', '湖北省武汉市青山区', 80 select home_district=min(home_district), [name]=min([name]), [count]=sum([count]) from @t where left(home_district,2)<>'42' group by left(home_district,2)unionselect home_district, [name], [count] from @t where left(home_district,2)='42'结果:home_district name count 110000 北京市 50 120000 天津市 24 420000 湖北省 3000 420100 湖北省武汉市 2000 420200 湖北省武汉市江岸区 300 420300 湖北省武汉市武昌区 200 420400 湖北省武汉市青山区 80
---------------------------
有这个编码规则就好处理了
在你列出的数据的基础上处理:select home_district=min(home_district),
name=min(name),
count=sum(count)
from tb
where left(home_district,2)<>'42'
group by left(home_district,2)unionselect home_district=min(home_district),
name=min(name),
count=sum(count)
from tb
where left(home_district,2)='42'
group by left(home_district,2)
name=min(name),
count=sum(count)
from tb
where left(home_district,2)<>'42'
group by left(home_district,2)unionselect home_district,
name,
count
from tb
where left(home_district,2)='42'
(
home_district varchar(20),
name varchar(200),
count int
)insert into test select '110000', '北京市', 10
insert into test select '110001', '北京市朝阳区', 20
insert into test select '110002', '北京市海啶区',20
insert into test select '120000', '天津市' ,8
insert into test select '120001', '天津市**区' ,6
insert into test select '120031', '天津市**区' , 10
insert into test select '420000', '湖北省' ,3000
insert into test select '420100', '湖北省武汉市', 2000
insert into test select '420200', '湖北省武汉市江岸区', 300
insert into test select '420300', '湖北省武汉市武昌区', 200
select substring(a.home_district,1,2) + '0000' as dis,sum(count)
from test a
where substring(home_district,1,2) = substring(home_district,1,2)
group by substring(a.home_district,1,2) + '0000'drop table test
110000 50
120000 24
420000 5500
create table test
(
home_district varchar(20),
name varchar(200),
count int
)insert into test select '110000', '北京市', 10
insert into test select '110001', '北京市朝阳区', 20
insert into test select '110002', '北京市海啶区',20
insert into test select '120000', '天津市' ,8
insert into test select '120001', '天津市**区' ,6
insert into test select '120031', '天津市**区' , 10
insert into test select '420000', '湖北省' ,3000
insert into test select '420100', '湖北省武汉市', 2000
insert into test select '420200', '湖北省武汉市江岸区', 300
insert into test select '420300', '湖北省武汉市武昌区', 200
select substring(home_district,1,2) + '0000' as dis,(select name from test where home_district = substring(a.home_district,1,2) +'0000') as cc,sum(count)
from test a
where substring(home_district,1,2) = substring(home_district,1,2)
group by substring(home_district,1,2) + '0000' select name from test where home_district = '110000'
--select * from test
drop table test
110000 北京市 50
120000 天津市 24
420000 湖北省 5500
select '110000', '北京市', 10 union all
select '110001', '北京市朝阳区', 20 union all
select '110002', '北京市海啶区',20 union all
select '120000', '天津市' ,8 union all
select '120001', '天津市**区' ,6 union all
select '120031', '天津市**区' , 10 union all
select '420000', '湖北省' ,3000 union all
select '420100', '湖北省武汉市', 2000 union all
select '420200', '湖北省武汉市江岸区', 300 union all
select '420300', '湖北省武汉市武昌区',200 union all
select '420400', '湖北省武汉市青山区', 80
select home_district=min(home_district),
[name]=min([name]),
[count]=sum([count])
from @t
where left(home_district,2)<>'42'
group by left(home_district,2)unionselect home_district,
[name],
[count]
from @t
where left(home_district,2)='42'结果:home_district name count
110000 北京市 50
120000 天津市 24
420000 湖北省 3000
420100 湖北省武汉市 2000
420200 湖北省武汉市江岸区 300
420300 湖北省武汉市武昌区 200
420400 湖北省武汉市青山区 80