复杂的SQL问题

Question

create table 单位表(单位编号 varchar(20),单位名称 varchar(40),单位级别 varchar(20),上级单位编号 varchar(20),最上级单位编号 varchar(20))
insert into 单位表 select '101'        ,'公安部'                        ,'1',null        ,null
insert into 单位表 select '1110111'    ,'河北省公安厅'                  ,'2','101'       ,'101'
insert into 单位表 select '111011111'  ,'石家庄公安局'                  ,'3','1110111'   ,'101'
insert into 单位表 select '111011145'  ,'保定公安局'                    ,'3','1110111'   ,'101'
insert into 单位表 select '11101111138','XX县公安局'                    ,'4','111011111' ,'101'
insert into 单位表 select '102'        ,'教育部'                        ,'1',null        ,null
insert into 单位表 select '2210211'    ,'河北省教育厅'                  ,'2','102'       ,'102'
insert into 单位表 select '221021111'  ,'石家庄教育局'                  ,'3','2210211'   ,'102'
insert into 单位表 select '221021145'  ,'保定教育局'                    ,'3','2210211'   ,'102'
insert into 单位表 select '22102111138','XX县教育局'                    ,'4','221021111' ,'102'
insert into 单位表 select '22102111136','XXX县教育局'                   ,'4','221021111' ,'102'
insert into 单位表 select '103'        ,'国家经贸委'                    ,'1',null        ,null
insert into 单位表 select '1110302022' ,'矿产地质调查中心'              ,'2','103'       ,'103'
insert into 单位表 select '1110303148' ,'矿产地质调查中心北京遥感中心'  ,'3','1110302022','103'
insert into 单位表 select '1110303149' ,'矿产地质调查中心北京测绘院'    ,'3','1110302022','103'
insert into 单位表 select '1110303147' ,'矿产地质调查中心北京地质调查所','3','1110302022','103'
create table 帐户表(单位编号 varchar(20),单位名称 varchar(20),单位级别 varchar(20),上级单位编号 varchar(20),最上级单位编号 varchar(20))
insert into 帐户表 select '1110111'     ,'河北省公安厅'    ,'2','101'      ,'101'
insert into 帐户表 select '111011111'   ,'石家庄公安局'    ,'3','1110111'  ,'101'
insert into 帐户表 select '111011145'   ,'保定公安局'      ,'3','1110111'  ,'101'
insert into 帐户表 select '11101111138' ,'XX县公安局'      ,'4','111011111','101'
insert into 帐户表 select '1110302022'  ,'矿产地质调查中心','2','103'      ,'103'
insert into 帐户表 select '1110302022'  ,'矿产地质调查中心','2','103'      ,'103'
go
create function f_ischild(@Pcode varchar(20),@Ccode varchar(20),@type int)
returns int
as
begin
    declare @code varchar(20)
    if(@Pcode=@Ccode)
        return @type
    
    select @code = 上级单位编号 from 单位表 where 单位编号=@Ccode
    while @@rowcount<>0
    begin 
        set @Ccode=@Code
        
        if(@Pcode=@Ccode)
            return 0
            
        select @code = 上级单位编号 from 单位表 where 单位编号=@Ccode
    end
    return -1
end
go
select 
    a.单位编号,
    a.单位名称,
    帐户数量 = sum(case when a.单位级别='1' and dbo.f_ischild(a.单位编号,b.单位编号,-1)=0 then 1 
                        when a.单位级别='2' and dbo.f_ischild(a.单位编号,b.单位编号,0)=0 then 1 
                        when a.单位级别='3' and a.单位编号=b.单位编号 then 1 
                        when a.单位级别='4' and a.单位编号=b.单位编号 then 1
                        else 0 
                   end)
from 
    单位表 a,
    帐户表 b
where
    a.单位编号=b.最上级单位编号
    or
    a.最上级单位编号=b.最上级单位编号
    and
    exists(select 1 from 帐户表 where 单位编号=a.单位编号)
group by
    isnull(b.最上级单位编号,a.单位编号),a.单位编号,a.单位名称
order by
    isnull(b.最上级单位编号,a.单位编号),a.单位编号
go/*
单位编号      单位名称          帐户数量
------------  ----------------  -----------
101           公安部            4
1110111       河北省公安厅      4
111011111     石家庄公安局      1
11101111138   XX县公安局        1
111011145     保定公安局        1
103           国家经贸委        2
1110302022    矿产地质调查中心  2
*/
drop function f_ischild
drop table 单位表,帐户表
go

Answer 1

把查询语句换成这个：
-----------------------------------------------------------------------------------------------------------------------
select 
    a.单位编号,
    a.单位名称,
    帐户数量 = sum(case when a.单位级别='1' and a.单位级别<b.单位级别 then 1 
                        when a.单位级别='2' then 1 
                        when a.单位级别='3' and a.单位编号=b.单位编号 then 1 
                        when a.单位级别='4' and a.单位编号=b.单位编号 then 1
                        else 0 
                   end)
from 
    单位表 a,
    帐户表 b
where
    dbo.f_ischild(a.单位编号,b.单位编号,0)=0
group by
    isnull(b.最上级单位编号,a.单位编号),a.单位编号,a.单位名称
order by
    isnull(b.最上级单位编号,a.单位编号),a.单位编号

Answer 2

create table t_unit (unit_no varchar(20),unit_name varchar(100),unit_level varchar(10),p_unit_no varchar(20),top_unit_no varchar(10))
go
insert into t_unit
select '101','公安部','1',null,null union all
select '1110111','河北省公安厅','2','101','101' union all
select '111011111','石家庄公安局','3','1110111','101' union all
select '111011145','保定公安局','3','1110111','101' union all
select '11101111138','XX县公安局','4','111011111','101' union all
select '102','教育部','1',null,null union all
select '2210211','河北省教育厅','2','102','102' union all
select '221021111','石家庄教育局','3','2210211','102' union all
select '221021145','保定教育局','3','2210211','102' union all
select '22102111138','XX县教育局','4','221021111','102' union all
select '22102111136','XXX县教育局','4','221021111','102' union all
select '103','国家经贸委','1',null,null union all
select '1110302022','矿产地质调查中心','2','103','103' union all
select '1110303148','矿产地质调查中心北京遥感中心','3','1110302022','103' union all
select '1110303149','矿产地质调查中心北京测绘院','3','1110302022','103' union all
select '1110303147','矿产地质调查中心北京地质调查所','3','1110302022','103'
gocreate table t_acc (acc_no varchar(20),acc_name varchar(100),acc_level varchar(10),p_acc_no varchar(20),top_acc_no varchar(10))
go
insert into t_acc 
select '1110111','河北省公安厅','2','101','101' union all
select '111011111','石家庄公安局','3','1110111','101' union all
select '111011145','保定公安局','3','1110111','101' union all
select '11101111138','XX县公安局','4','111011111','101' union all
select '1110302022','矿产地质调查中心','2','103','103' union all
select '1110302022','矿产地质调查中心','2','103','103'
goselect c.acc_no as 单位编号,c.acc_name as 单位名称,count(c.acc_no) as 帐户数量 from(
select a.top_acc_no as acc_no,b.unit_name+'(一级及以下单位总计)' as acc_name,b.unit_level,b.p_unit_no,b.top_unit_no from t_acc a,t_unit b where a.top_acc_no =b.unit_no
union all
select a.p_acc_no as acc_no,(b.unit_name+case b.unit_level when '2' then '(二级及以下单位总计)' when '3' then '(三级单位数量)' else '(四级单位数量)' end) as acc_name, b.unit_level,b.p_unit_no,b.top_unit_no from t_acc a,t_unit b where a.p_acc_no =b.unit_no and b.unit_level>1
union all
select a.acc_no,(a.acc_name+case a.acc_level when '2' then '(二级及以下单位总计)' when '3' then '(三级单位数量)' else '(四级单位数量)' end) as acc_name, b.unit_level,b.p_unit_no,b.top_unit_no from t_acc a,t_unit b where a.acc_no =b.unit_no
) c
group by c.acc_no,c.acc_name
order by case len(c.acc_no) when 3 then c.acc_no else substring(c.acc_no,3,len(c.acc_no)-2)end

Answer 3

就比如101 公安部(一级及以下单位总计)          4
1110111   河北省公安厅(二级及以下单位总计)          3
  二级单位数量 1
111011111               石家庄公安局(三级单位数量) 0
11101111138               XX县公安局(四级单位数量) 1
111011145               保定公安局(三级单位数量) 1河北省公安厅,石家庄公安局,XX县公安局,保定公安局都是公安部的下属单位，所以公安部(一级及以下单位总计)就是四个。河北省公安厅的直接下属单位石家庄公安局和保定公安局，间接下属单位XX县公安局。但是石家庄公安局没有开户信息，所以下会就有两个开户信息，加上自己二级及以下单位总计就是3。一个单位可以有多个开户信息，河北省公安厅有一条开户信息，所以河北省公安厅的二级单位数量就是1.三级和四级只计算自己本级别的开户数量就可以，所以石家庄公安局(三级单位数量)=0，XX县公安局(四级单位数量)=1，保定公安局(三级单位数量)=1。不知道这样写你们能不能看懂，先谢谢你们，我另开帖给分。

Answer 4

回： mislrb(aben) 
111011111 统计出来应该是1，我写错了，不好意思

Answer 5

改下SQL语句b.unit_level>1改为b.unit_level=2 :select c.acc_no as 单位编号,c.acc_name as 单位名称,count(c.acc_no) as 帐户数量 from(
select a.top_acc_no as acc_no,b.unit_name+'(一级及以下单位总计)' as acc_name,b.unit_level,b.p_unit_no,b.top_unit_no from t_acc a,t_unit b where a.top_acc_no =b.unit_no
union all
select a.p_acc_no as acc_no,(b.unit_name+case b.unit_level when '2' then '(二级及以下单位总计)' when '3' then '(三级单位数量)' else '(四级单位数量)' end) as acc_name, b.unit_level,b.p_unit_no,b.top_unit_no from t_acc a,t_unit b where a.p_acc_no =b.unit_no and b.unit_level=2
union all
select a.acc_no,(a.acc_name+case a.acc_level when '2' then '(二级及以下单位总计)' when '3' then '(三级单位数量)' else '(四级单位数量)' end) as acc_name, b.unit_level,b.p_unit_no,b.top_unit_no from t_acc a,t_unit b where a.acc_no =b.unit_no
) c
group by c.acc_no,c.acc_name
order by case len(c.acc_no) when 3 then c.acc_no else substring(c.acc_no,3,len(c.acc_no)-2)end

Answer 6

谢谢mislrb(aben) ：
我要是想加一个二级的本级0数量能实现吗：101 公安部(一级及以下单位总计) 4
1110111 河北省公安厅(二级及以下单位总计) 3
1110111 河北省公安厅(本级数量) 1
111011111 石家庄公安局(三级单位数量) 1
11101111138 XX县公安局(四级单位数量) 1
111011145 保定公安局(三级单位数量) 1
103 国家经贸委(一级及以下单位总计) 2
1110302022 矿产地质调查中心(二级及以下单位总计) 2

Answer 7

回libin_ftsafe(子陌红尘) ：
那个我写错了，应该是石家庄公安局有开户信息，但是如果石家庄公安局没有开户信息，但是他的下级有开户信息，就显示  111011111 石家庄公安局(三级单位数量) 0

Answer 8

谢谢两位，我在
http://community.csdn.net/Expert/topic/4423/4423649.xml?temp=.4587519
开了加分帖子，请去领分。

Answer 9

to楼主,根据你的要求做了点改动并加多了一条SQL解决了可能存在的BUG(如果你的帐号表里只有4级,5级或更高级的记录而没有其对应的2级帐号的话,旧SQL是不能统计出这条记录的2级数量的).select c.acc_no as 单位编号,c.acc_name as 单位名称,count(c.acc_no) as 帐户数量 
from(
 select a.top_acc_no as acc_no,b.unit_name+'(一级及以下单位总计)' as  acc_name,b.unit_level,b.p_unit_no,b.top_unit_no 
 from t_acc a,t_unit b 
 where a.top_acc_no =b.unit_no
 union all
 select a.p_acc_no as acc_no,(b.unit_name+case b.unit_level when '2' then '(二级以下单位总计)'  end) as acc_name, b.unit_level,b.p_unit_no,b.top_unit_no 
 from t_acc a,t_unit b 
 where a.p_acc_no =b.unit_no and b.unit_level='2'
 union all
 select b.unit_no as acc_no,(b.unit_name+case b.unit_level when '2' then '(二级以下单位总计)'  end) as acc_name,b.unit_level,b.p_unit_no,b.top_unit_no 
 from t_acc a,t_unit b 
 where a.acc_level>3 and a.top_acc_no=b.top_unit_no and b.unit_level='2'
 union all
 select a.acc_no,(a.acc_name+case a.acc_level when '2' then '(二级单位数量)' when '3' then '(三级单位数量)' else '(四级单位数量)' end) as acc_name, b.unit_level,b.p_unit_no,b.top_unit_no 
 from t_acc a,t_unit b 
 where a.acc_no =b.unit_no
) c
group by c.acc_no,c.acc_name
order by case len(c.acc_no) when 3 then c.acc_no else substring(c.acc_no,3,len(c.acc_no)-2)end
单位编号                 单位名称                                                                                                                     帐户数量        
-------------------- ------------------------------------------------------------------------------------------------------------------------ ----------- 
101                  公安部(一级及以下单位总计)                                                                                                           4
1110111              河北省公安厅(二级单位)                                                                                                             1
1110111              河北省公安厅(二级以下单位总计)                                                                                                         3
111011111            石家庄公安局(三级单位数量)                                                                                                           1
11101111138          XX县公安局(四级单位数量)                                                                                                           1
111011145            保定公安局(三级单位数量)                                                                                                            1
103                  国家经贸委(一级及以下单位总计)                                                                                                         2
1110302022           矿产地质调查中心(二级单位)                                                                                                           2（所影响的行数为 8 行）

Answer 10

在你的另一个贴子已经给出了
http://community.csdn.net/Expert/topic/4423/4423649.xml?temp=.4587519