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,
[Re] [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'Re'
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], [Re], [PermissionCode], [PermissionFile]) VALUES (1, N'学员管理', 0, 1, N'', 1, N'', 0, N'')
INSERT [dbo].[tblAdminNavigation] ([NavID], [NavName], [ParentID], [IsNav], [NavUrl], [Sort], [Re], [PermissionCode], [PermissionFile]) VALUES (2, N'学员列表', 1, 1, N'../Student/List', 12, N'', 0, N'')
INSERT [dbo].[tblAdminNavigation] ([NavID], [NavName], [ParentID], [IsNav], [NavUrl], [Sort], [Re], [PermissionCode], [PermissionFile]) VALUES (3, N'教务管理', 0, 1, N'', 2, N'', 0, N'')
INSERT [dbo].[tblAdminNavigation] ([NavID], [NavName], [ParentID], [IsNav], [NavUrl], [Sort], [Re], [PermissionCode], [PermissionFile]) VALUES (4, N'班级列表', 3, 1, N'../Student/List', 2, N'', 0, N'')
INSERT [dbo].[tblAdminNavigation] ([NavID], [NavName], [ParentID], [IsNav], [NavUrl], [Sort], [Re], [PermissionCode], [PermissionFile]) VALUES (5, N'新增学员', 1, 1, N'../ServiceLog/List', 13, N'', 0, N'')
INSERT [dbo].[tblAdminNavigation] ([NavID], [NavName], [ParentID], [IsNav], [NavUrl], [Sort], [Re], [PermissionCode], [PermissionFile]) VALUES (6, N'新增班级', 3, 1, N'../Student/List', 1, N'', 0, N'')
SET IDENTITY_INSERT [dbo].[tblAdminNavigation] OFF
求sql语句
输出结果如下 树形结构暂定只有2层
学员管理
学员列表
新增学员
教务管理
新增班级
班级列表
解决方案 »
- 急!回复表删除问题,请大哥们指教
- 谁有好看的股票实时行情代码调用模板
- 系统发布问题
- 验证TextBox非空问题:this.TextBox2.Text!=""||this.TextBox2.Text!=String.Empty||this.TextBox2.Text!=null为什么还不能保证验证结果
- javascript 如何判断 字符串是否是时间类型
- 急救!用过PopupCalendar的来帮忙
- 什么叫做多语言网站,把一个网页的内容分别翻译成,英文、日文、法文,让用户来切换???
- 再急问个水晶报表设计的问题啊 在线等啊
- 请问那个网站看球不卡又清楚.
- 用vb写了一个类,不知道如何把它编译成.dll文件
- 服务端的Json处理,是选择ashx,还是webservice呢?哪个效率更高一些
- 网页一直加载
查询结果为
父节点名称1
子节点名称1-1
子节点名称1-2
子节点名称1-3
父节点名称2
子节点名称2-1
子节点名称2-2
父节点名称3
子节点名称3-2
子节点名称2-3
父节点名称4
子节点名称4-1
子节点名称4-3
IF OBJECT_ID('[Navigation]') IS NOT NULL DROP TABLE [Navigation]
GOCREATE 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
goSELECT * 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
*/
来个大牛帮忙改改
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
//试试
这个Sort字段 不能为 null吧