这个查询怎么统计请高手亮剑

[cwdhubin]

要做一个查询病人地域分布的一个统计，根据要求我写了下面的语句，
select home_district,name,count(*) from(select a.home_district,c.name from a_patient_mi a,
ba_first_page3 b,zd_district_code c
where a.patient_id=b.patient_id
and a.home_district=c.code) a
group by home_district,name
得到的结果如下表，
home_district name count
110000 北京市 10
110001 北京市朝阳区 20
110002 北京市海啶区
120000 天津市 8
120001 天津市**区 6
120031 天津市**区 10
...........................
420000 湖北省 3000
420100 湖北省武汉市 2000
420200 湖北省武汉市江岸区 300
420300 湖北省武汉市武昌区 200
420400 湖北省武汉市青山区 80
..................................


现在要做一个区域的统计，如统计北京市的，把北京市辖区的所有都合计到北京市里面来，如北京市朝阳区，海啶区等，但是里面有一个例外，湖北省辖区里面的不做合计，请问各位高手有什么好的办法没有，在线等，先谢谢了。

[cwdhubin]

谢谢两位的回答，问题可以解决了。

[新鲜鱼排]

分开考虑
-------------------------
查询没有湖北省的数据的统计
union all
只有湖北的数据

[cwdhubin]

能否写下来看下，谢谢

[cwdhubin]

关键在这边，合计的时候要去code的前两位相同合计到如同110000里面去，如同树的父结点和字结点一样，把子结点的人数合计到父结点里面来。大家看有没有什么好的办法。

[lsqkeke]

合计的时候要去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)

union

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)

[lsqkeke]

根据要求改一下

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)

union

select home_district,
name,
count
from tb
where left(home_district,2)='42'

[msjqd]

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

[msjqd]

修改一下
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

[lsqkeke]

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)

union

select 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