SQL TREE

[暗尘掩月]

CREATE TABLE [dbo].[tblAdminNavigation](
	[NavID] [int] IDENTITY(1,1) NOT NULL,
	[NavName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[ParentID] [int] NULL,
	[IsNav] [bit] NULL,
	[NavUrl] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[Sort] [int] NULL,
	[Remark] [nvarchar](200) COLLATE Chinese_PRC_CI_AS NULL,
	[PermissionCode] [int] NULL,
	[PermissionFile] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_tblAdminNavigation] PRIMARY KEY CLUSTERED 
(
	[NavID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'导航' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblAdminNavigation', @level2type=N'COLUMN',@level2name=N'NavID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'导航名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblAdminNavigation', @level2type=N'COLUMN',@level2name=N'NavName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'父节点ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblAdminNavigation', @level2type=N'COLUMN',@level2name=N'ParentID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否为导航' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblAdminNavigation', @level2type=N'COLUMN',@level2name=N'IsNav'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'导航链接' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblAdminNavigation', @level2type=N'COLUMN',@level2name=N'NavUrl'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'导航排序' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblAdminNavigation', @level2type=N'COLUMN',@level2name=N'Sort'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblAdminNavigation', @level2type=N'COLUMN',@level2name=N'Remark'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'权限代码（取枚举类型）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblAdminNavigation', @level2type=N'COLUMN',@level2name=N'PermissionCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'权限对应的文件路径' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblAdminNavigation', @level2type=N'COLUMN',@level2name=N'PermissionFile'

测试数据如下

SET IDENTITY_INSERT [dbo].[tblAdminNavigation] ON
INSERT [dbo].[tblAdminNavigation] ([NavID], [NavName], [ParentID], [IsNav], [NavUrl], [Sort], [Remark], [PermissionCode], [PermissionFile]) VALUES (1, N'学员管理', 0, 1, N'', 1, N'', 0, N'')
INSERT [dbo].[tblAdminNavigation] ([NavID], [NavName], [ParentID], [IsNav], [NavUrl], [Sort], [Remark], [PermissionCode], [PermissionFile]) VALUES (2, N'学员列表', 1, 1, N'../Student/List', 12, N'', 0, N'')
INSERT [dbo].[tblAdminNavigation] ([NavID], [NavName], [ParentID], [IsNav], [NavUrl], [Sort], [Remark], [PermissionCode], [PermissionFile]) VALUES (3, N'教务管理', 0, 1, N'', 2, N'', 0, N'')
INSERT [dbo].[tblAdminNavigation] ([NavID], [NavName], [ParentID], [IsNav], [NavUrl], [Sort], [Remark], [PermissionCode], [PermissionFile]) VALUES (4, N'班级列表', 3, 1, N'../Student/List', 2, N'', 0, N'')
INSERT [dbo].[tblAdminNavigation] ([NavID], [NavName], [ParentID], [IsNav], [NavUrl], [Sort], [Remark], [PermissionCode], [PermissionFile]) VALUES (5, N'新增学员', 1, 1, N'../ServiceLog/List', 13, N'', 0, N'')
INSERT [dbo].[tblAdminNavigation] ([NavID], [NavName], [ParentID], [IsNav], [NavUrl], [Sort], [Remark], [PermissionCode], [PermissionFile]) VALUES (6, N'新增班级', 3, 1, N'../Student/List', 1, N'', 0, N'')
SET IDENTITY_INSERT [dbo].[tblAdminNavigation] OFF

求sql语句
输出结果如下 树形结构暂定只有2层
学员管理
学员列表
新增学员
教务管理
新增班级
班级列表

[快溜]

好像是递归查询。

[H_Gragon]

我有Treeview绑定数据的方法，跟你的要求差不多，楼主可以参考一下：地址

[暗尘掩月]

我不是要绑定treeview 我是要直接返回个DataTable
查询结果为
父节点名称1
子节点名称1-1
子节点名称1-2
子节点名称1-3
父节点名称2
子节点名称2-1
子节点名称2-2
父节点名称3
子节点名称3-2
子节点名称2-3
父节点名称4
子节点名称4-1
子节点名称4-3

[暗尘掩月]

非存储过程

[暗尘掩月]

来个人撒 难道我只有去用存储过程解决了

[一天到晚游泳的鱼2016]

你获取的这些是父类，还是子类

你的表设计成递归了，不管几层，循环调用就可以了

[jyh070207]

可以做成函数,输入菜单id,返回所有子项

[暗尘掩月]

IF OBJECT_ID('[Navigation]') IS NOT NULL DROP TABLE [Navigation]
GO

CREATE TABLE Navigation
(
    NavID INT PRIMARY KEY NOT NULL,
    NavName VARCHAR(50),
    ParentID int,  --为0表示顶级节点
	Sort int--ParentID相同情况按Sort排序
)

INSERT Navigation
SELECT 1,'节点1',0,1 UNION ALL
SELECT 2,'节点2',0,2 UNION ALL
SELECT 3,'节点3',0,4 UNION ALL
SELECT 4,'节点1-2',1,2 UNION ALL
SELECT 5,'节点4',0,3 UNION ALL
SELECT 6,'节点3-2',3,2 UNION ALL
SELECT 7,'节点3-1',3,1 UNION ALL
SELECT 8,'节点4-1',5,1 UNION ALL
SELECT 9,'节点1-1',1,1 UNION ALL
SELECT 10,'节点2-1',2,1
go

SELECT * FROM Navigation
/*
如何得到如下结果(最好别用存储过程)
NavID     NavName   ParentID   Sort
1         节点1      0         1
4         节点1-1    1         1
9         节点1-2    1         2
2         节点2      0         2
10        节点2-1    2         1
5         节点4      0         3
6         节点4-1    5         1
3         节点3      0         4     
5         节点3-1    3         1
8         节点3-2    3         2  
*/

[暗尘掩月]

结果写错了

/*
如何得到如下结果(最好别用存储过程)
NavID     NavName   ParentID   Sort
1           节点1        0           1
9           节点1-1      1           1
4           节点1-2      1           2
2           节点2        0           2
10          节点2-1     2            1
5           节点4        0           3
8           节点4-1      5           1
3           节点3        0           4     
7           节点3-1      3           1
6           节点3-2      3           2  
*/

[天下如山]

为毛不用存储过程?
你这个 竟然用到递归查询 一层一层下去的话(你说了是2层 但不能确保以后也是吧)

[暗尘掩月]

自己写了个 但是在ParentID相同的情况下不能按Sort排序

select NavID,NavName ,ParentID,Sort  from (select NavID,NavName ,ParentID, (case when ParentID = 0 then NavID else ParentID end) as pid,Sort  from Navigation) a order by pid,navid

来个大牛帮忙改改

[暗尘掩月]

只要2层就行了 多了用不到 因为是导航菜单是accordion 不是treeview

[lizhengdao1314]

公用表表达式一句解决：（sql2005版本及以上）

with temp as (select * from Navigation where ParentID=0 union all select a.* from Navigation a,temp b where a.ParentID=b.NavID) select * from temp

[天下如山]

select NavID,NavName,ParentID,Sort,SortTree = case when ParentID=0 then NavID else ParentID end ,SortChild=ParentID+Sort  from  Navigation order by SortTree,SortChild,ParentID
//试试

[暗尘掩月]

[Quote=引用 14 楼 的回复:]

公用表表达式一句解决：（sql2005版本及以上）
SQL code

with temp as (select * from Navigation where ParentID=0 union all select a.* from Navigation a,temp b where a.ParentID=b.NavID) select * from temp
[/Quote]

结果不对啊

[暗尘掩月]

[Quote=引用 15 楼 的回复:]

SQL code

select NavID,NavName,ParentID,Sort,SortTree = case when ParentID=0 then NavID else ParentID end ,SortChild=ParentID+Sort from Navigation order by SortTree,SortChild,ParentID
//试试
[/Quote]

OK了 谢谢

[天下如山]

SQL方面的问题去SQL版块问会快点 那边高手多。

[暗尘掩月]

[Quote=引用 15 楼 的回复:]
SQL code

select NavID,NavName,ParentID,Sort,SortTree = case when ParentID=0 then NavID else ParentID end ,SortChild=ParentID+Sort from Navigation order by SortTree,SortChild,ParentID
//试试
[/Quote]

这个Sort字段 不能为 null吧

[天下如山]

Sort字段处理下
isnull()