sql树形算法

 

SQL 2000和2005 树形递归法小汇总

--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'中国'
UNION ALL SELECT '002','001','陕西省'
UNION ALL SELECT '003','002','西安市'
UNION ALL SELECT '004','003','雁塔区'
UNION ALL SELECT '005','001','广东省'
UNION ALL SELECT '006','005','深圳市'
UNION ALL SELECT '007','006','宝安区'
UNION ALL SELECT '008',NULL ,'日本'
UNION ALL SELECT '009','008','东京市'
UNION ALL SELECT '010','008','长岛市'
GO

 

 

ALTER FUNCTION [dbo].[f_Cid](@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
	DECLARE @Level int
	SET @Level=1
	INSERT @t_Level SELECT @ID,@Level
	WHILE @@ROWCOUNT>0
	BEGIN
		SET @Level=@Level+1
		INSERT @t_Level SELECT a.ID,@Level
		FROM tb a,@t_Level b
		WHERE a.PID=b.ID AND b.Level=@Level-1;
	END
	RETURN
END

ALTER FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3))
AS
BEGIN
	INSERT @t_Level SELECT @ID;
	SELECT @ID=PID FROM tb WHERE ID=@ID AND PID IS NOT NULL;	
	WHILE @@ROWCOUNT>0
	BEGIN
		INSERT @t_Level SELECT @ID;
		SELECT @ID=PID FROM tb WHERE ID=@ID AND PID IS NOT NULL;			
	END
	RETURN
END


DECLARE @n varchar(10)
set @n='001';
with 
CTE as
(
  select * from tb where ID=@n
  union all
  select t.* from CTE j join tb t on j.ID=t.PID
)
select * from CTE;
go
   	
DECLARE @n varchar(10)
set @n='004';
with
CTE2 as
(
 select * from tb where ID=@n and PID is not null
 union all
 select a.* from tb a join CTE2 f on a.ID=f.PID 
)
select * from CTE2 order by ID

5、oracle树型查询5、oracle树型查询5、oracle树型查询5、oracle树型查询oracle树型查询

以下转：

 
--建表
Create table income_sheet (ID INTEGER ,PID INTEGER ,NAME VARCHAR2(100) ,IC_VALUE NUMBER(10) );

--插数据
insert into income_sheet values(1,0,'中国',14256.45);

insert into income_sheet values(2,1,'陕西省',6300.00);
insert into income_sheet values(3,1,'广东省',7330.00);
insert into income_sheet values(4,1,'江苏省',4350.00);

insert into income_sheet values(5,2,'西安市',1600.77);
insert into income_sheet values(6,2,'汉中市',1200.77);
insert into income_sheet values(7,2,'安康市',1900.77);
insert into income_sheet values(8,3,'广州市',1200.77);
insert into income_sheet values(9,3,'深圳市',1100.77);
insert into income_sheet values(10,3,'东莞市',1500.77);
insert into income_sheet values(11,4,'南京市',1900.77);
insert into income_sheet values(12,4,'苏州市',1300.77);
insert into income_sheet values(13,4,'徐州市',1600.77);

insert into income_sheet values(14,5,'西安县',680.34);
insert into income_sheet values(15,8,'广州县',680.34);
insert into income_sheet values(16,11,'南京县',680.34);

insert into income_sheet values(17,14,'西安乡', 103.41);
insert into income_sheet values(18,15,'广州乡', 103.41);
insert into income_sheet values(19,16,'南京乡', 103.41);

insert into income_sheet values(20,17,'西安村',71.93);
insert into income_sheet values(21,18,'广州村',71.93);
insert into income_sheet values(22,19,'南京村',71.93);


commit;



--查询

select level,name,ic_value from income_sheet 
connect by prior id = pid start with pid = 0  order by level