CREATE TABLE Cate ( ID INT IDENTITY(1,1) PRIMARY KEY, [C_Name] NVARCHAR(50), [C_Level] [int] NOT NULL, [C_Code] NVARCHAR(255) NULL, [C_Parent] [int] NOT NULL )
GO
INSERT INTO [Cate] VALUES('分类1',1,'0',0 ) INSERT INTO [Cate] VALUES('分类2',1,'0',0 ) INSERT INTO [Cate] VALUES('分类3',1,'0',0 ) INSERT INTO [Cate] VALUES('分类4',1,'0',0 ) INSERT INTO [Cate] VALUES('分类5',2,'1/',1 ) INSERT INTO [Cate] VALUES('分类6',2,'1/',1 ) INSERT INTO [Cate] VALUES('分类7',2,'2/',2 ) INSERT INTO [Cate] VALUES('分类8',2,'2/',2 ) INSERT INTO [Cate] VALUES('分类9',3,'1/5',5 ) INSERT INTO [Cate] VALUES('分类10',3,'1/6',6 ) INSERT INTO [Cate] VALUES('分类11',4,'1/5/9',9 ) INSERT INTO [Cate] VALUES('分类12',4,'1/5/9/11',10 )
GO --WCT第一种方法 ;WITH CTETBL AS ( SELECT ID,[C_Name],C_Parent,[C_Code] FROM [Cate] WHERE ID=1 UNION ALL
SELECT A.ID,A.[C_Name],A.C_Parent,A.[C_Code] FROM CTETBL B INNER JOIN [Cate] A ON B.ID=A.[C_Parent]
) SELECT * FROM CTETBL order by id
SELECT * FROM 表名 WHERE (ID like 'id',主类 like '主类名')
IF OBJECT_ID('Cate') IS NOT NULL
DROP TABLE CATE
GO
CREATE TABLE Cate
(
ID INT IDENTITY(1,1) PRIMARY KEY,
[C_Name] NVARCHAR(50),
[C_Level] [int] NOT NULL,
[C_Code] NVARCHAR(255) NULL,
[C_Parent] [int] NOT NULL
)
GO
INSERT INTO [Cate] VALUES('分类1',1,'0',0 )
INSERT INTO [Cate] VALUES('分类2',1,'0',0 )
INSERT INTO [Cate] VALUES('分类3',1,'0',0 )
INSERT INTO [Cate] VALUES('分类4',1,'0',0 )
INSERT INTO [Cate] VALUES('分类5',2,'1/',1 )
INSERT INTO [Cate] VALUES('分类6',2,'1/',1 )
INSERT INTO [Cate] VALUES('分类7',2,'2/',2 )
INSERT INTO [Cate] VALUES('分类8',2,'2/',2 )
INSERT INTO [Cate] VALUES('分类9',3,'1/5',5 )
INSERT INTO [Cate] VALUES('分类10',3,'1/6',6 )
INSERT INTO [Cate] VALUES('分类11',4,'1/5/9',9 )
INSERT INTO [Cate] VALUES('分类12',4,'1/5/9/11',10 )
GO
--WCT第一种方法
;WITH CTETBL AS
(
SELECT ID,[C_Name],C_Parent,[C_Code] FROM [Cate] WHERE ID=1 UNION ALL
SELECT A.ID,A.[C_Name],A.C_Parent,A.[C_Code] FROM
CTETBL B INNER JOIN
[Cate] A
ON B.ID=A.[C_Parent]
)
SELECT * FROM CTETBL order by id
WHERE (ID like 'id',主类 like '主类名')
select A.ID,A.Name,B.ID,B.Name
from Organiz as A,Organiz as B
where A.ID=B.P_ID
这样等到的是全表中的数据 在where 后加一个条件 A.ID=5 后 只能得到直接子节点,无法得到子节点的子节点...