select count(*),........ start with .. connect by cityId =parentCityId where
try:select a.cityId,isnull(count(c.*),0) as 笔数 from city_zip a inner join city_zip b on (a.cityId=b.cityId) or (a.parentCityId is null and a.cityId=b.parentCityId)) left join assets_case c where b.cityId=c.belongCityId group by a.cityId
create table assets_case(classfiyId int,approveStatus int,belongCityId int) create table city_zip(cityId int,parentCityId int) insert into city_zip values( 1,null) insert into city_zip values( 2,null) insert into city_zip values(10, 1) insert into city_zip values(11, 1) insert into city_zip values(20, 2)insert into assets_case values(1,1, 1) insert into assets_case values(1,2,11) goselect a.cityId,isnull(count(c.classfiyId),0) as 笔数 from city_zip a inner join city_zip b on ((a.cityId=b.cityId) or (a.parentCityId is null and a.cityId=b.parentCityId)) left join assets_case c on b.cityId=c.belongCityId group by a.cityId go /* cityId 笔数 ----------- ----------- 1 2 2 0 10 0 11 1 20 0 */drop table assets_case,city_zip go
之前写的SQL有些Bug,楼主用楼上这个。
libin_ftsafe,我的belongCityId 是从外面传进来的
create table assets_case(classfiyId int,approveStatus int,belongCityId int) create table city_zip(cityId int,parentCityId int) insert into city_zip values( 1,null) insert into city_zip values( 2,null) insert into city_zip values(10, 1) insert into city_zip values(11, 1) insert into city_zip values(20, 2)insert into assets_case values(1,1, 1) insert into assets_case values(1,2,11) go declare @belongCityId int set @belongCityId=1select a.cityId,isnull(count(c.classfiyId),0) as 笔数 from city_zip a inner join city_zip b on ((a.cityId=b.cityId) or (a.parentCityId is null and a.cityId=b.parentCityId)) left join assets_case c on b.cityId=c.belongCityId where @belongCityId in(a.cityId,isnull(a.parentCityId,a.cityId)) group by a.cityId,isnull(a.parentCityId,a.cityId) go /* cityId 笔数 ----------- ----------- 1 2 2 0 10 0 11 1 20 0 */drop table assets_case,city_zip go
create table assets_case(classfiyId int,approveStatus int,belongCityId int) create table city_zip(cityId int,parentCityId int) insert into city_zip values( 1,null) insert into city_zip values( 2,null) insert into city_zip values(10, 1) insert into city_zip values(11, 1) insert into city_zip values(20, 2)insert into assets_case values(1,1, 1) insert into assets_case values(1,2,11) go declare @belongCityId int set @belongCityId=1select a.cityId,isnull(count(c.classfiyId),0) as 笔数 from city_zip a inner join city_zip b on ((a.cityId=b.cityId) or (a.parentCityId is null and a.cityId=b.parentCityId)) left join assets_case c on b.cityId=c.belongCityId where @belongCityId in(a.cityId,isnull(a.parentCityId,a.cityId)) group by a.cityId,isnull(a.parentCityId,a.cityId) go /* cityId 笔数 ----------- ----------- 1 2 10 0 11 1 */drop table assets_case,city_zip go
start with ..
connect by cityId =parentCityId
where
a.cityId,isnull(count(c.*),0) as 笔数
from
city_zip a
inner join
city_zip b
on
(a.cityId=b.cityId) or (a.parentCityId is null and a.cityId=b.parentCityId))
left join
assets_case c
where
b.cityId=c.belongCityId
group by
a.cityId
create table city_zip(cityId int,parentCityId int)
insert into city_zip values( 1,null)
insert into city_zip values( 2,null)
insert into city_zip values(10, 1)
insert into city_zip values(11, 1)
insert into city_zip values(20, 2)insert into assets_case values(1,1, 1)
insert into assets_case values(1,2,11)
goselect
a.cityId,isnull(count(c.classfiyId),0) as 笔数
from
city_zip a
inner join
city_zip b
on
((a.cityId=b.cityId) or (a.parentCityId is null and a.cityId=b.parentCityId))
left join
assets_case c
on
b.cityId=c.belongCityId
group by
a.cityId
go
/*
cityId 笔数
----------- -----------
1 2
2 0
10 0
11 1
20 0
*/drop table assets_case,city_zip
go
create table city_zip(cityId int,parentCityId int)
insert into city_zip values( 1,null)
insert into city_zip values( 2,null)
insert into city_zip values(10, 1)
insert into city_zip values(11, 1)
insert into city_zip values(20, 2)insert into assets_case values(1,1, 1)
insert into assets_case values(1,2,11)
go
declare @belongCityId int
set @belongCityId=1select
a.cityId,isnull(count(c.classfiyId),0) as 笔数
from
city_zip a
inner join
city_zip b
on
((a.cityId=b.cityId) or (a.parentCityId is null and a.cityId=b.parentCityId))
left join
assets_case c
on
b.cityId=c.belongCityId
where
@belongCityId in(a.cityId,isnull(a.parentCityId,a.cityId))
group by
a.cityId,isnull(a.parentCityId,a.cityId)
go
/*
cityId 笔数
----------- -----------
1 2
2 0
10 0
11 1
20 0
*/drop table assets_case,city_zip
go
create table city_zip(cityId int,parentCityId int)
insert into city_zip values( 1,null)
insert into city_zip values( 2,null)
insert into city_zip values(10, 1)
insert into city_zip values(11, 1)
insert into city_zip values(20, 2)insert into assets_case values(1,1, 1)
insert into assets_case values(1,2,11)
go
declare @belongCityId int
set @belongCityId=1select
a.cityId,isnull(count(c.classfiyId),0) as 笔数
from
city_zip a
inner join
city_zip b
on
((a.cityId=b.cityId) or (a.parentCityId is null and a.cityId=b.parentCityId))
left join
assets_case c
on
b.cityId=c.belongCityId
where
@belongCityId in(a.cityId,isnull(a.parentCityId,a.cityId))
group by
a.cityId,isnull(a.parentCityId,a.cityId)
go
/*
cityId 笔数
----------- -----------
1 2
10 0
11 1
*/drop table assets_case,city_zip
go
不行哦,真头疼
select city_code.cityName,city_code.cityId,
isnull(level_data.allCount,0) allCont,
isnull(level_data.ct,0) ct,
isnull(level_data.sm,0) sm,
isnull(level_data.zd,0) zd,
isnull(level_data.cg,0) cg,
isnull(level_data.gs,0) gs,
isnull(level_data.ys,0) ys,
isnull(level_data.cz,0) cz,
isnull(level_data.sy,0) sy,
isnull(level_data.biy,0) biy
from(select cityId,cityName from city_zip where cityId =26 ) city_code left join
( select
main.belongCityId,
count (1) allCount,
count (case when main.assetsClassifyId = 1.1 then 1 else null end) ct,
count (case when main.assetsClassifyId = 1.2 then 1 else null end) sm,count (case when main.assetsClassifyId = 1.3 then 1 else null end) zd,
count(case when main.assetsClassifyId = 2.1 then 1 else null end) cg,count (case when main.assetsClassifyId = 3.1 then 1 else null end) gs,
count (case when main.assetsClassifyId = 4.1 then 1 else null end) ys,count (case when main.assetsClassifyId = 5.1 then 1 else null end) cz,
count (case when main.assetsClassifyId = 6.1 then 1 else null end) sy,count (case when main.assetsClassifyId = 7.1 then 1 else null end) biy
from
assets_case main,city_zip zip
where
main.approveStatus in(5,6) and zip.cityId= 26 and main.belongCityId=zip.cityId
group by main.belongCityId) level_data on city_code.cityId=level_data.belongCityId
union all
select '小計' cityName,9999 belongCityId,isnull(level_data.allCount,0) allCount,isnull(level_data.ct,0) ct,isnull(level_data.sm,0) sm,
isnull(level_data.zd,0) zd,isnull(level_data.cg,0) cg,isnull(level_data.gs,0) gs,isnull(level_data.ys,0) ys,
isnull(level_data.cz,0) cz,isnull(level_data.sy,0) sy,isnull(level_data.biy,0) biy
from(select count (1) allCount,
count (case when main.assetsClassifyId = 1.1 then 1 else null end) ct,
count (case when main.assetsClassifyId = 1.2 then 1 else null end) sm,count (case when main.assetsClassifyId = 1.3 then 1 else null end) zd,
count(case when main.assetsClassifyId = 2.1 then 1 else null end) cg,count (case when main.assetsClassifyId = 3.1 then 1 else null end) gs,
count (case when main.assetsClassifyId = 4.1 then 1 else null end) ys,count (case when main.assetsClassifyId = 5.1 then 1 else null end) cz,
count (case when main.assetsClassifyId = 6.1 then 1 else null end) sy,count (case when main.assetsClassifyId = 7.1 then 1 else null end) biy
from
assets_case main,city_zip zip
where
main.approveStatus in(5,6) and zip.cityId= 26 and main.belongCityId=zip.cityId
group by main.belongCityId) level_data