求一条语句或者存储过程

[spidershark]

表一：zzjg
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zzjg]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[zzjg]
GO

CREATE TABLE [dbo].[zzjg] (
[ZzjgID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentID] [int] NULL ,
[LayID] [int] NULL ,
[orderpx] [int] NULL ,
[Zg_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

表二：Jg_renyuan
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Jg_renyuan]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Jg_renyuan]
GO

CREATE TABLE [dbo].[Jg_renyuan] (
[Ry_id] [int] IDENTITY (1, 1) NOT NULL ,
[ZzjgID] [int] NULL ,
[Ry_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
) ON [PRIMARY]
GO

其中表一与表二通过ZzjgID来关联
表一的结构为树形结构显示，通过ParentIDID来区别父节点和子节点。
要求：1。所有的机构分为三级，LayID等于1时为一级机构，LayID等于2时为二级机构，LayID等于3时为三级机构
统计所有机构中的人数（当为一级机构时其实就是所有的人数，当为二级机构时就是当前二级机构和当前二级机构下所有的人数，当为三级机构时统计的是当前三级机构的人数）
2.所有的机构进行排序显示，具体为：先显示一级机构，再显示二级机构，三级机构显示到其二级机构下面，实际就是树形显示的方式以表的形式能够看到.



表二数据如下：

7 277 王栋
8 277 李丽
9 277 姚小琴
10 305 赵建文
11 296 曹波
12 295 冯学飞
13 296 马小军
14 305 张龙富
15 305 张小苇
16 381 赵峰
17 381 崔国彬
18 381 张旭东
19 381 郭永平

表一数据如下：


277 0 1 0 山西省
292 277 2 2 大同市
293 277 2 6 朔州市
294 277 2 4 长治市
295 277 2 7 忻州市
296 277 2 11 运城市
307 294 3 0 城区
308 303 3 0 太谷县
309 303 3 0 祁县
310 294 3 0 郊区
311 303 3 0 平遥县

[-狙击手-]

好长，晚上看

[soft_wsx]

树型结构是吧！

[-狙击手-]

其中表一与表二通过ZzjgID来关联
表一的结构为树形结构显示，通过ParentIDID来区别父节点和子节点。
要求：1。所有的机构分为三级，LayID等于1时为一级机构，LayID等于2时为二级机构，LayID等于3时为三级机构
统计所有机构中的人数（当为一级机构时其实就是所有的人数，当为二级机构时就是当前二级机构和当前二级机构下所有的人数，当为三级机构时统计的是当前三级机构的人数）
2.所有的机构进行排序显示，具体为：先显示一级机构，再显示二级机构，三级机构显示到其二级机构下面，实际就是树形显示的方式以表的形式能够看到.
－－－

BOM嘓

[dawugui]

/*
标题：查询所有顶级节点及其子节点的例
地址：http://topic.csdn.net/u/20090323/21/63a91f51-c4df-464d-ba18-64343deb4e3a.html
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间：2009-03-23
地点：广东深圳
*/

[code=SQL]create table Area (id int identity,Name varchar(10) ,order_by int ,father_ID int )
insert into area values('广东省',2,0) 
insert into area values('四川省',2,0) 
insert into area values('湖北省',2,0) 
insert into area values('东莞市',1,1) 
insert into area values('广州市',1,1) 
insert into area values('天河区',0,5) 
insert into area values('绵阳市',1,2) 
insert into area values('武汉市',1,3) 
insert into area values('汉口区',0,8) 
insert into area values('随州市',1,3)
go

select * from area

drop table area

/*
id          Name       order_by    father_ID   
----------- ---------- ----------- ----------- 
1           广东省        2           0
2           四川省        2           0
3           湖北省        2           0
4           东莞市        1           1
5           广州市        1           1
6           天河区        0           5
7           绵阳市        1           2
8           武汉市        1           3
9           汉口区        0           8
10          随州市        1           3

（所影响的行数为 10 行）

要求显示为：
name           
-------------- 
广东省
  东莞市
  广州市
    天河区
四川省
  绵阳市
湖北省
  武汉市
    汉口区
  随州市

（所影响的行数为 10 行）
*/

--创建原始表
create table Area (id int identity,Name varchar(10) ,order_by int ,father_ID int )
insert into area values('广东省',2,0) 
insert into area values('四川省',2,0) 
insert into area values('湖北省',2,0) 
insert into area values('东莞市',1,1) 
insert into area values('广州市',1,1) 
insert into area values('天河区',0,5) 
insert into area values('绵阳市',1,2) 
insert into area values('武汉市',1,3) 
insert into area values('汉口区',0,8) 
insert into area values('随州市',1,3)
--创建临时表
create table tmp (id int identity,Name varchar(10) ,order_by int ,father_ID int )
go

--创建查询指定节点及其所有子节点的函数
create function f_cid(@ID int) returns @t_level table(id int , level int)
as
begin
  declare @level int
  set @level = 1
  insert into @t_level select @id , @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.id , @level
    from area a , @t_Level b
    where a.father_ID = b.id and b.level = @level - 1
  end
  return
end
go

--创建存储过程并将数据插入临时表
create proc my_proc 
as
begin
  declare @id as int
  set @id = 0
  while exists(select 1 from area where order_by = 2 and id > @id)
  begin
    set @id = (select min(id) from area where order_by = 2 and id > @id)
    insert into tmp(Name ,order_by ,father_ID) select a.name,a.order_by ,a.father_id from area a , f_cid(@id) b where a.id = b.id order by a.id 
  end
end
go
exec my_proc

--从临时表提取数据并显示
select case when order_by = 2 then name
            when order_by = 1 then '  ' + name
            when order_by = 0 then '    ' + name
       end name
from tmp order by id

drop function f_cid
drop proc my_proc
drop table area , tmp

/*
name           
-------------- 
广东省
  东莞市
  广州市
    天河区
四川省
  绵阳市
湖北省
  武汉市
    汉口区
  随州市

（所影响的行数为 10 行）

*/

[soft_wsx]

等一下，试试看能写出来不！

[soft_wsx]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zzjg]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
drop table [dbo].[zzjg] 
GO 

CREATE TABLE [dbo].[zzjg] ( 
[ZzjgID] [int]  NOT NULL , 
[ParentID] [int] NULL , 
[LayID] [int] NULL , 
[orderpx] [int] NULL , 
[Zg_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] 
GO 

insert into [dbo].[zzjg]
select 
277, 0, 1, 0,   '山西省'  union all select 
292, 277, 2, 2, '大同市' union all select 
293, 277, 2, 6, '朔州市' union all select 
294, 277, 2, 4, '长治市' union all select 
295, 277, 2, 7, '忻州市' union all select   
296, 277, 2, 11, '运城市' union all select 
307, 294, 3, 0, '城区' union all select 
308, 292, 3, 0, '太谷县' union all select   --手工改
309, 292, 3, 0, '祁县' union all select 
310, 294, 3, 0, '郊区' union all select 
311, 303, 3, 0, '平遥' 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Jg_renyuan]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
drop table [dbo].[Jg_renyuan] 
GO 

CREATE TABLE [dbo].[Jg_renyuan] ( 
[Ry_id] [int] NOT NULL , 
[ZzjgID] [int] NULL , 
[Ry_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , 
) ON [PRIMARY] 
GO 
insert [dbo].[Jg_renyuan]    
select

7, 277, '王栋'  union all select 
8, 277, '李丽'  union all select 
9 ,277, '姚小琴'  union all select 
10 ,305, '赵建文'  union all select 
11 ,296, '曹波'  union all select 
12 ,295, '冯学飞'  union all select 
13 ,296, '马小军'  union all select 
14 ,305, '张龙富'  union all select 
15 ,305, '张小苇'  union all select 
16 ,381, '赵峰'  union all select 
17 ,381, '崔国彬'  union all select 
18 ,381, '张旭东'  union all select 
19 ,381, '郭永平'  union all select 
20, 277, '王栋'  union all select 
21, 277, '李丽'  union all select 
22 ,277, '姚小琴'  union all select 
23 ,305, '赵建文'  union all select 
24 ,307, '曹波'  union all select 
25 ,307, '冯学飞'  union all select 
26 ,308, '马小军'  union all select 
27 ,308, '张龙富'  union all select 
28 ,308, '张小苇'  union all select 
29 ,309, '赵峰'  union all select 
30 ,310, '崔国彬'  union all select 
31 ,310, '张旭东'  union all select 
32 ,311, '郭永平' 

 drop table #a

select a.*,b.score  into #a from zzjg a 
  full join  
    (select zzjgid,COUNT(1) as score from Jg_renyuan
      group by zzjgid)b
   on a.ZzjgID=b.zzjgid
  
  declare @level_tt table([ZzjgID] nvarchar(1000),[ParentID] nvarchar(1000),level int)
  declare @level int
  set @level=0
  insert @level_tt([ZzjgID],[ParentID],level)
  select [ZzjgID],[ZzjgID],@level from [dbo].[zzjg] where [ParentID]=0
  while @@ROWCOUNT>0
  begin 
          set @level=@level+1
          insert @level_tt([ZzjgID],[ParentID],level)
          select a.[ZzjgID],cast(b.[ParentID] as nvarchar)+cast(a.[ZzjgID] as nvarchar),@level
            from [dbo].[zzjg] a,@level_tt b
            where a.[ParentID]=b.[ZzjgID] and b.level=@level-1
 end   
select space(b.level*4)+'-->>'+a.[Zg_name] as 层次排序结果,a.zzjgid,a.parentID,SUM(isnull(c.score,0)) as socre
  from #a a,
       @level_tt b,
       #a c,
       @level_tt d
  where a.ZzjgID=b.ZzjgID and c.ZzjgID=d.ZzjgID
        and d.parentID like b.parentID+'%'
  group by a.ZzjgID,a.parentID,b.level,a.zg_name,b.parentID
  order by b.parentID
 

select space(b.level*4)+'-->>'+a.[Zg_name] as 层次排序结果,a.ZzjgID,a.ParentID,a.LayID,b.level
  from [dbo].[zzjg] a,@level_tt b
  where a.[ZzjgID]=b.[ZzjgID]
  order by b.[ParentID]
  
/*
层次排序结果	zzjgid	parentID	socre
-->>山西省	277	0	17
    -->>大同市	292	277	4
        -->>太谷县	308	303	3
        -->>祁县	309	303	1
    -->>朔州市	293	277	0
    -->>长治市	294	277	4
        -->>城区	307	294	2
        -->>郊区	310	294	2
    -->>忻州市	295	277	1
    -->>运城市	296	277	2
 */ 
  

[soft_wsx]

一个小时，累死我了！撤退！

[华夏小卒]

if object_id('[zzjg]') is not null drop table [zzjg]
go 
CREATE TABLE [dbo].[zzjg] ( 
[ZzjgID] [int]  NOT NULL , 
[ParentID] [int] NULL , 
[LayID] [int] NULL , 
[orderpx] [int] NULL , 
[Zg_name] [nvarchar] (50) )  
GO 

insert into [dbo].[zzjg] select 
277, 0, 1, 0,   '山西省'  union all select 
292, 277, 2, 2, '大同市' union all select 
293, 277, 2, 6, '朔州市' union all select 
294, 277, 2, 4, '长治市' union all select 
295, 277, 2, 7, '忻州市' union all select   
296, 277, 2, 11, '运城市' union all select 
307, 294, 3, 0, '城区' union all select 
308, 292, 3, 0, '太谷县' union all select   --手工改
309, 292, 3, 0, '祁县' union all select 
310, 294, 3, 0, '郊区' union all select 
311, 303, 3, 0, '平遥' 

if object_id('[Jg_renyuan]') is not null drop table [Jg_renyuan]
go 

CREATE TABLE [dbo].[Jg_renyuan] ( 
[Ry_id] [int] NOT NULL , 
[ZzjgID] [int] NULL , 
[Ry_name] [varchar] (50)  )  
GO 
insert [dbo].[Jg_renyuan] select
7, 277, '王栋'  union all select 
8, 277, '李丽'  union all select 
9 ,277, '姚小琴'  union all select 
10 ,305, '赵建文'  union all select 
11 ,296, '曹波'  union all select 
12 ,295, '冯学飞'  union all select 
13 ,296, '马小军'  union all select 
14 ,305, '张龙富'  union all select 
15 ,305, '张小苇'  union all select 
16 ,381, '赵峰'  union all select 
17 ,381, '崔国彬'  union all select 
18 ,381, '张旭东'  union all select 
19 ,381, '郭永平'  union all select 
20, 277, '王栋'  union all select 
21, 277, '李丽'  union all select 
22 ,277, '姚小琴'  union all select 
23 ,305, '赵建文'  union all select 
24 ,307, '曹波'  union all select 
25 ,307, '冯学飞'  union all select 
26 ,308, '马小军'  union all select 
27 ,308, '张龙富'  union all select 
28 ,308, '张小苇'  union all select 
29 ,309, '赵峰'  union all select 
30 ,310, '崔国彬'  union all select 
31 ,310, '张旭东'  union all select 
32 ,311, '郭永平' 

if object_id('f_str') is not null drop function f_str
go 
create function f_str(@name varchar(20))
returns int
as
begin
	declare @rs int,@level int 
	set @rs=0
	set @level=(select LayID from [zzjg] where [Zg_name]=@name)
	
	declare @t table(id int,level int)
	insert @t select [ZzjgID],@level from [zzjg] where [Zg_name]=@name
	while @@rowcount>0
	begin
		set @level=@level+1
		insert @t 
		select [ZzjgID],@level from [zzjg] a,@t b
		where a.[ParentID]=b.id and [LayID]=@level 
	end
 
	select @rs=count(*) from [Jg_renyuan] b,@t t where t.id=b.[ZzjgID] 
	return @rs
	
end
go

select Zg_name,人数=dbo.f_str(Zg_name) from [zzjg]

Zg_name                                            人数
-------------------------------------------------- -----------
山西省                                                17
大同市                                                4
朔州市                                                0
长治市                                                4
忻州市                                                1
运城市                                                2
城区                                                 2
太谷县                                                3
祁县                                                 1
郊区                                                 2
平遥                                                 1

(11 行受影响)

[小宏]

留个脚印

[lvveve]

the 4th floor is best

[soft_wsx]

还是结贴，更待何时！

[7761098]

soft_wsx很强，佩服

[nalnait]

[Quote=引用 11 楼 7761098 的回复:]
soft_wsx很强，佩服
[/Quote].

[lovearan2008]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zzjg]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[zzjg]
GO

CREATE TABLE [dbo].[zzjg] (
[ZzjgID] [int] NOT NULL ,
[ParentID] [int] NULL ,
[LayID] [int] NULL ,
[orderpx] [int] NULL ,
[Zg_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

insert into [dbo].[zzjg]
select
277, 0, 1, 0, '山西省' union all select
292, 277, 2, 2, '大同市' union all select
293, 277, 2, 6, '朔州市' union all select
294, 277, 2, 4, '长治市' union all select
295, 277, 2, 7, '忻州市' union all select
296, 277, 2, 11, '运城市' union all select
307, 294, 3, 0, '城区' union all select
308, 292, 3, 0, '太谷县' union all select --手工改
309, 292, 3, 0, '祁县' union all select
310, 294, 3, 0, '郊区' union all select
311, 303, 3, 0, '平遥'

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Jg_renyuan]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Jg_renyuan]
GO

CREATE TABLE [dbo].[Jg_renyuan] (
[Ry_id] [int] NOT NULL ,
[ZzjgID] [int] NULL ,
[Ry_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
) ON [PRIMARY]
GO
insert [dbo].[Jg_renyuan]
select

7, 277, '王栋' union all select
8, 277, '李丽' union all select
9 ,277, '姚小琴' union all select
10 ,305, '赵建文' union all select
11 ,296, '曹波' union all select
12 ,295, '冯学飞' union all select
13 ,296, '马小军' union all select
14 ,305, '张龙富' union all select
15 ,305, '张小苇' union all select
16 ,381, '赵峰' union all select
17 ,381, '崔国彬' union all select
18 ,381, '张旭东' union all select
19 ,381, '郭永平' union all select
20, 277, '王栋' union all select
21, 277, '李丽' union all select
22 ,277, '姚小琴' union all select
23 ,305, '赵建文' union all select
24 ,307, '曹波' union all select
25 ,307, '冯学飞' union all select
26 ,308, '马小军' union all select
27 ,308, '张龙富' union all select
28 ,308, '张小苇' union all select
29 ,309, '赵峰' union all select
30 ,310, '崔国彬' union all select
31 ,310, '张旭东' union all select
32 ,311, '郭永平'

drop table #a

select a.*,b.score into #a from zzjg a
full join
(select zzjgid,COUNT(1) as score from Jg_renyuan
group by zzjgid)b
on a.ZzjgID=b.zzjgid

declare @level_tt table([ZzjgID] nvarchar(1000),[ParentID] nvarchar(1000),level int)
declare @level int
set @level=0
insert @level_tt([ZzjgID],[ParentID],level)
select [ZzjgID],[ZzjgID],@level from [dbo].[zzjg] where [ParentID]=0
while @@ROWCOUNT>0
begin
set @level=@level+1
insert @level_tt([ZzjgID],[ParentID],level)
select a.[ZzjgID],cast(b.[ParentID] as nvarchar)+cast(a.[ZzjgID] as nvarchar),@level
from [dbo].[zzjg] a,@level_tt b
where a.[ParentID]=b.[ZzjgID] and b.level=@level-1
end
select space(b.level*4)+'-->>'+a.[Zg_name] as 层次排序结果,a.zzjgid,a.parentID,SUM(isnull(c.score,0)) as socre
from #a a,
@level_tt b,
#a c,
@level_tt d
where a.ZzjgID=b.ZzjgID and c.ZzjgID=d.ZzjgID
and d.parentID like b.parentID+'%'
group by a.ZzjgID,a.parentID,b.level,a.zg_name,b.parentID
order by b.parentID


select space(b.level*4)+'-->>'+a.[Zg_name] as 层次排序结果,a.ZzjgID,a.ParentID,a.LayID,b.level
from [dbo].[zzjg] a,@level_tt b
where a.[ZzjgID]=b.[ZzjgID]
order by b.[ParentID]

/*
层次排序结果 zzjgid parentID socre
-->>山西省 277 0 17
-->>大同市 292 277 4
-->>太谷县 308 303 3
-->>祁县 309 303 1
-->>朔州市 293 277 0
-->>长治市 294 277 4
-->>城区 307 294 2
-->>郊区 310 294 2
-->>忻州市 295 277 1
-->>运城市 296 277 2
*/

[xxx_007]

佩服