Sql Server按树形结构排序查询表记录
http://blog.csdn.net/dxnn520/article/details/8089149
-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-04-23 02:37:28 -- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) -- Subject: BOM按节点排序应用实例 -------------------------------------------------------------------------- --实例1: --> 生成测试数据表:tb IF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10)) INSERT [tb] SELECT 1,'01',0,N'服装' UNION ALL SELECT 2,'01',1,N'男装' UNION ALL SELECT 3,'01',2,N'西装' UNION ALL SELECT 4,'01',3,N'全毛' UNION ALL SELECT 5,'02',3,N'化纤' UNION ALL SELECT 6,'02',2,N'休闲装' UNION ALL SELECT 7,'02',1,N'女装' UNION ALL SELECT 8,'01',7,N'套装' UNION ALL SELECT 9,'02',7,N'职业装' UNION ALL SELECT 10,'03',7,N'休闲装' UNION ALL SELECT 11,'04',7,N'西装' UNION ALL SELECT 12,'01',11,N'全毛' UNION ALL SELECT 13,'02',11,N'化纤' UNION ALL SELECT 14,'05',7,N'休闲装' GO --SELECT * FROM [tb] -->SQL查询如下: ;WITH T AS ( SELECT CAST(CODE AS VARCHAR(20)) AS CODE,*, CAST(ID AS VARBINARY(MAX)) AS px FROM tb AS A WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.pid) UNION ALL SELECT CAST(B.CODE+A.CODE AS VARCHAR(20)),A.*, CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX)) FROM tb AS A JOIN T AS B ON A.pid=B.id ) SELECT Code,Name FROM T ORDER BY px /* Code Name -------------------- ---------- 01 服装 0101 男装 010101 西装 01010101 全毛 01010102 化纤 010102 休闲装 0102 女装 010201 套装 010202 职业装 010203 休闲装 010204 西装 01020401 全毛 01020402 化纤 010205 休闲装 (14 行受影响) */ --实例2: --> 生成测试数据表:tb IF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([id] INT,[parentid] INT,[categoryname] NVARCHAR(10)) INSERT [tb] SELECT 1,0,'test1' UNION ALL SELECT 2,0,'test2' UNION ALL SELECT 3,1,'test1.1' UNION ALL SELECT 4,2,'test2.1' UNION ALL SELECT 5,3,'test1.1.1' UNION ALL SELECT 6,1,'test1.2' GO --SELECT * FROM [tb] -->SQL查询如下: ;WITH T AS ( SELECT *,CAST(ID AS VARBINARY(MAX)) AS px FROM tb AS A WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.[parentid]) UNION ALL SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX)) FROM tb AS A JOIN T AS B ON A.[parentid]=B.id ) SELECT [id],[parentid],[categoryname] FROM T ORDER BY px /* id parentid categoryname ----------- ----------- ------------ 1 0 test1 3 1 test1.1 5 3 test1.1.1 6 1 test1.2 2 0 test2 4 2 test2.1 (6 行受影响) */ 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2010/04/23/5518166.aspx
复杂例：
USE [YZL] GO /****** Object: Table [dbo].[P_DictionaryType] Script Date: 10/13/2015 14:59:57 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[P_DictionaryType]( [DT_ObjectID] [uniqueidentifier] NOT NULL, [DT_Name] [nvarchar](100) NULL, [DT_ShortName] [nvarchar](100) NULL, [DT_Number] [nvarchar](100) NULL, [DT_Introduction] [text] NULL, [DT_IsDelete] [bit] NULL, CONSTRAINT [PK_P_DICTIONARYTYPE] PRIMARY KEY CLUSTERED ( [DT_ObjectID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'6937f4c4-0656-4ce7-8049-078b7199c26d', N'行政职务', N'XZZW', N'DT_XZZW', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'dce2ea08-1bd8-4b36-a39c-0e0ee1c253f4', N'劳动合同', N'DT_LDHT', N'DT_LDHT', NULL, 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'd5a92adc-886f-420b-ae78-0ef161e4aaf6', N'国籍', N'DT_GJ', N'DT_GJ', NULL, 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'227f7bd8-bf36-48ce-9221-1155b218da6d', N'计算机等级', N'DT_JSJDJ', N'DT_JSJDJ', NULL, 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'b5ff1e1c-2802-49ad-a9f0-1331ec704207', N'岗位类型', N'GWLX', N'DT_GWLB', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'bf309285-b83e-404a-8baa-167b88028db5', N'专业技术职务', N'ZYJSZW', N'DT_ZYJSZW', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'5c58325f-a70b-4ab1-a0f0-1eb07aa1c14f', N'课程类型', N'课程', N'DT_KCLX', N'课程', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'a5622506-2d39-4c31-99d3-216e4c19fc77', N'健康状况', N'', N'DT_JKZK', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'029c58d8-9ab3-4ef9-807c-275c3fa6dba8', N'身份证件类型', N'ZJLX', N'DT_SFZJLX', N'身份证件类型', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'0f3b1b82-daae-4e5a-a0db-2b565f34138a', N'教师职称', N'DT_JSZC', N'DT_JSZC', NULL, 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'f5c29858-238c-41fe-9bc4-2ca0acec613d', N'民族', N'民族', N'DT_MZ', N'民族', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'2a800f43-13bb-4795-ab1b-5633f4eac80d', N'学生类型', N'', N'DT_XSLX', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'e770aff5-beb5-4c41-8935-5bf1177ba405', N'专任教师接受培训情况', N'DT_ZRJSJSPXQK', N'DT_ZRJSJSPXQK', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'f16ea79f-8210-4992-b5ae-60761959e9d3', N'签订合同情况', N'DT_QDHTQK', N'DT_QDHTQK', NULL, 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'105343d1-3997-4f1b-ae32-66f713417707', N'费用类型', N'DT_FYLX', N'DT_FYLX', NULL, 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'76c6e55e-331c-4e9a-af91-776801e1f0a6', N'户口性质', N'HKXZ', N'DT_HKXZ', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'f674b9ac-0bfd-4fa9-b88d-816364616714', N'荣誉类型', N'RYLX', N'DT_RYLX', NULL, 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'cac52b7e-1f4f-4d09-9903-8360a7750c65', N'企业工作（实践）时长', N'QYGZ_SJ_SC', N'DT_QYGZ_SJ_SC', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'27927422-1e91-4ff2-aef0-8942fd30aef8', N'普通话等级', N'PTHDJ', N'DT_PTHDJ', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'12965e30-9b7a-4c38-af54-8c4b7303f1e6', N'奖学金', N'', N'DT_JXJ', N'为资助世界各国学生、学者到中国高等学校进行学习和研究，增进中国人民与世界各国人民的相互理解和友谊，发展中国与世界各国在教育、科技、文化、经贸等领域的交流与合作，中国政府设立了中国政府奖学金。教育部负责根据中国政府与外国政府或国际组织达成的协议或计划对外提供中国政府奖学金', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'22965e30-9b7a-4c38-af54-8c4b7303f1e6', N'五险一金', N'金', N'DT_WXYJ', N'你知道什么是五险一金吗?', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'9c21ebf6-5a99-4e45-9b0d-8ce4f23de110', N'助学金', N'DT_ZXJ', N'DT_ZXJ', NULL, 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'6d660ae9-2c48-4ad2-abee-8e481199b04b', N'取得其他职业资格证书', N'QDQTZYZGZS', N'DT_QDQTZYZGZS', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'0f65f497-f371-48e3-b0b1-9226487a1c35', N'任教学科类别', N'RJXKLB', N'DT_RJXKLB', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'dd3afa0d-5c46-4820-a226-95ab8fa82134', N'生源类型', N'', N'DT_SYLB', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'114c7ea1-aef4-4aba-bc3e-9c8a737b2682', N'请假类型', N'DT_QJLX', N'DT_QJLX', NULL, 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'ef31b612-f457-41a1-a4bc-9d416ec2a757', N'任课状况', N'RKZK', N'DT_RKZK', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'5e9d84b6-2cf2-4590-910e-9db30ee8126c', N'政治面貌', N'政治面貌', N'DT_ZZMM', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'b9c3ec2d-6708-4020-b93f-9f664575f7eb', N'获奖等级', N'HJDJ', N'DT_HJDJ', NULL, 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'bcd1fbb8-9209-44d7-af77-9f6ecec246c0', N'学历', N'XL', N'DT_XL', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'b39e45dd-7ac2-4ab2-8241-a3fdebd9f4eb', N'教师资格证', N'JSZGZ', N'DT_JSZGZ', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'4d802690-51ed-45ee-90a0-adee3e7e4bde', N'婚姻状况', N'DT_HYZK', N'DT_HYZK', NULL, 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'ad4fcf0a-a68c-4059-a1fe-adef2a379fe9', N'职业资格证书等级', N'DT_ZYZGZSDJ', N'DT_ZYZGZSDJ', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'559f7c59-e710-40cd-be9e-b1582ffb059f', N'入学资格', N'DT_RXZG', N'DT_RXZG', NULL, 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'dfc8072d-a10d-480d-9462-bc20971bb177', N'职称类别', N'DT_ZCLB', N'DT_ZCLB', NULL, 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'fee41d93-4677-417f-ae20-bf989a579c7b', N'学位', N'XW', N'DT_XW', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'993d74dc-21a2-4517-b317-d9013203b6db', N'论文级别', N'DT_LWJB', N'DT_LWJB', NULL, 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'503fe824-b190-431e-9a2d-dce0fde3f9cb', N'岗位等级', N'GWDJ', N'DT_GWDJ', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'6211fb24-7db1-4d97-9c08-ee728d2dc1f2', N'团员所在领域', N'DT_TYSZLY', N'DT_TYSZLY', NULL, 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'657f6b25-8c32-40d2-8df9-f08df43e97bc', N'毕业证书', N'DT_BYZS', N'DT_BYZS', N'', 0) INSERT [dbo].[P_DictionaryType] ([DT_ObjectID], [DT_Name], [DT_ShortName], [DT_Number], [DT_Introduction], [DT_IsDelete]) VALUES (N'a60d6b5f-14b7-4ccf-bed6-f12663f21e4a', N'处分级别', N'DT_QFJB', N'DT_QFJB', NULL, 0)
USE [YZL] GO /****** Object: Table [dbo].[P_Dictionary] Script Date: 10/13/2015 14:55:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[P_Dictionary]( [D_ObjectID] [uniqueidentifier] NOT NULL, [D_ParentID] [uniqueidentifier] NULL, [D_Name] [nvarchar](100) NULL, [D_TypeID] [uniqueidentifier] NULL, [D_Sort] [int] NULL, [D_IsDelete] [bit] NULL, CONSTRAINT [PK_P_DICTIONARY] PRIMARY KEY CLUSTERED ( [D_ObjectID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0：已删除，1：未删除' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'P_Dictionary', @level2type=N'COLUMN',@level2name=N'D_IsDelete' GO INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'0e771708-91ab-4f39-a5fe-00cc3755cfab', N'00000000-0000-0000-0000-000000000000', N'1年', N'cac52b7e-1f4f-4d09-9903-8360a7750c65', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'a25a3510-f521-48b2-9b36-037f1fac965f', N'00000000-0000-0000-0000-000000000000', N'文学类', N'5c58325f-a70b-4ab1-a0f0-1eb07aa1c14f', 3, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'0b00d8b1-15df-4eee-86b0-07a3192b053c', N'00000000-0000-0000-0000-000000000000', N'统招生', N'dd3afa0d-5c46-4820-a226-95ab8fa82134', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'29f9db85-c3ef-487a-8ac7-095ede6b20db', N'00000000-0000-0000-0000-000000000000', N'身份证', N'029c58d8-9ab3-4ef9-807c-275c3fa6dba8', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'ac909f1e-b032-4122-834f-0a7b1131e55e', N'00000000-0000-0000-0000-000000000000', N'高级教师资格证', N'b39e45dd-7ac2-4ab2-8241-a3fdebd9f4eb', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'1654e498-51ce-47b4-888e-16afcc6a3800', N'00000000-0000-0000-0000-000000000000', N'中级', N'503fe824-b190-431e-9a2d-dce0fde3f9cb', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'12a12def-3276-46b1-9870-17a63f3e7c89', N'00000000-0000-0000-0000-000000000000', N'学生类型2', N'2a800f43-13bb-4795-ab1b-5633f4eac80d', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'd184a2a5-de48-4ed5-8705-1bde57fad5cc', N'00000000-0000-0000-0000-000000000000', N'教学', N'b5ff1e1c-2802-49ad-a9f0-1331ec704207', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'52153566-b386-40d2-bd82-1f3ec26a89f6', N'4044496b-b928-459b-999c-9821877054c4', N'车祸意外保险', N'22965e30-9b7a-4c38-af54-8c4b7303f1e6', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'8a6911b6-c895-401a-9397-211864ea0aa3', N'00000000-0000-0000-0000-000000000000', N'健康', N'a5622506-2d39-4c31-99d3-216e4c19fc77', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'4d9b53e2-46c9-40f7-a11c-24297c6d3053', N'00000000-0000-0000-0000-000000000000', N'厅级', N'6937f4c4-0656-4ce7-8049-078b7199c26d', 3, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'd6fbf19d-7c27-4ef8-835f-2730d452ff8d', N'00000000-0000-0000-0000-000000000000', N'党员', N'5e9d84b6-2cf2-4590-910e-9db30ee8126c', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'0c5aba63-3d55-4e24-8452-298fb2f316ae', N'52153566-b386-40d2-bd82-1f3ec26a89f6', N'大型车祸意外保险', N'22965e30-9b7a-4c38-af54-8c4b7303f1e6', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'f251f31f-e3fa-4dc0-8401-2b8c26f816bc', N'00000000-0000-0000-0000-000000000000', N'初级', N'503fe824-b190-431e-9a2d-dce0fde3f9cb', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'2a0349be-4bce-4406-abd3-2e34b709a38d', N'00000000-0000-0000-0000-000000000000', N'博士', N'fee41d93-4677-417f-ae20-bf989a579c7b', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'ad5d1f6a-bd38-4a06-92ed-393e4af078a9', N'00000000-0000-0000-0000-000000000000', N'奖学金', N'12965e30-9b7a-4c38-af54-8c4b7303f1e6', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'bd5d1f6a-bd38-4a06-92ed-393e4af078a9', N'ad5d1f6a-bd38-4a06-92ed-393e4af078a9', N'国家奖学金', N'12965e30-9b7a-4c38-af54-8c4b7303f1e6', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'02fd11ed-de48-42fa-a94e-3ebc42b8954c', N'00000000-0000-0000-0000-000000000000', N'艺术生', N'dd3afa0d-5c46-4820-a226-95ab8fa82134', 3, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'ac8160c9-694f-411a-8396-417af30e3b8c', N'00000000-0000-0000-0000-000000000000', N'高级证书', N'ad4fcf0a-a68c-4059-a1fe-adef2a379fe9', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'9943ced6-cd9e-4f0a-a924-4767b6e58c29', N'00000000-0000-0000-0000-000000000000', N'科学类', N'5c58325f-a70b-4ab1-a0f0-1eb07aa1c14f', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'3447506c-4afd-4582-a6d3-4aaedc075453', N'00000000-0000-0000-0000-000000000000', N'正国级', N'6937f4c4-0656-4ce7-8049-078b7199c26d', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'e61168a1-2ab4-4c12-af33-5689446a353f', N'00000000-0000-0000-0000-000000000000', N'良好', N'e770aff5-beb5-4c41-8935-5bf1177ba405', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'075df81d-e74e-4289-b9b8-5e5e388ae2d1', N'00000000-0000-0000-0000-000000000000', N'高级', N'503fe824-b190-431e-9a2d-dce0fde3f9cb', 3, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'3a4d0847-77d1-4ca4-92ac-653a421d299f', N'00000000-0000-0000-0000-000000000000', N'学士', N'fee41d93-4677-417f-ae20-bf989a579c7b', 3, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'901ec89e-0b57-4a08-93b1-6d1728573eb3', N'00000000-0000-0000-0000-000000000000', N'城市', N'76c6e55e-331c-4e9a-af91-776801e1f0a6', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'53bd5b02-f2cd-4848-b1cb-73059e308eb1', N'00000000-0000-0000-0000-000000000000', N'部级', N'6937f4c4-0656-4ce7-8049-078b7199c26d', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'd903c5dd-01cb-46ed-aca7-753930c200dd', N'00000000-0000-0000-0000-000000000000', N'国家级', N'6d660ae9-2c48-4ad2-abee-8e481199b04b', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'6b45fa17-a9d1-48d1-a96f-7c42e66f6f6b', N'00000000-0000-0000-0000-000000000000', N'学生类型1', N'2a800f43-13bb-4795-ab1b-5633f4eac80d', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'044025c4-47b3-448a-8ab2-7d53996c93fd', N'00000000-0000-0000-0000-000000000000', N'中级', N'bf309285-b83e-404a-8baa-167b88028db5', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'762673aa-cbbb-48b0-824b-82c742c4c677', N'00000000-0000-0000-0000-000000000000', N'省级', N'6d660ae9-2c48-4ad2-abee-8e481199b04b', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'adef9849-9184-495a-b746-8394957a3bee', N'00000000-0000-0000-0000-000000000000', N'保险金', N'22965e30-9b7a-4c38-af54-8c4b7303f1e6', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'ee3d6152-7857-472b-9104-843d0844f6cb', N'00000000-0000-0000-0000-000000000000', N'优秀', N'e770aff5-beb5-4c41-8935-5bf1177ba405', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'89150190-59f5-481b-90e5-864a3c173a01', N'00000000-0000-0000-0000-000000000000', N'保险金2', N'22965e30-9b7a-4c38-af54-8c4b7303f1e6', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'755ef1f6-90cb-47e4-848b-899e16afd9e3', N'00000000-0000-0000-0000-000000000000', N'甲', N'27927422-1e91-4ff2-aef0-8942fd30aef8', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'e022a968-209b-4653-b65a-8bbd0a6421c6', N'00000000-0000-0000-0000-000000000000', N'硕士', N'fee41d93-4677-417f-ae20-bf989a579c7b', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'e66644c4-955b-46df-b2f9-98008b3978a0', N'00000000-0000-0000-0000-000000000000', N'农村', N'76c6e55e-331c-4e9a-af91-776801e1f0a6', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'4044496b-b928-459b-999c-9821877054c4', N'89150190-59f5-481b-90e5-864a3c173a01', N'意外保险', N'22965e30-9b7a-4c38-af54-8c4b7303f1e6', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'85d3675a-f348-455e-b09b-abc43c7c229f', N'c5c721ec-ff78-455d-957e-bfd4bac0b256', N'123', N'5c58325f-a70b-4ab1-a0f0-1eb07aa1c14f', 1, 1) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'618ed255-58a7-4f84-91e3-add92f01ae93', N'89150190-59f5-481b-90e5-864a3c173a01', N'社保', N'22965e30-9b7a-4c38-af54-8c4b7303f1e6', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'f37d9a13-bf8e-4518-9fb9-ae139fd68f09', N'00000000-0000-0000-0000-000000000000', N'患病', N'a5622506-2d39-4c31-99d3-216e4c19fc77', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'4d44d992-a5a3-4e04-b85f-b10576d13262', N'00000000-0000-0000-0000-000000000000', N'少数民族', N'f5c29858-238c-41fe-9bc4-2ca0acec613d', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'd7380ab8-5a7f-46ea-b206-b3e3ae1407fc', N'00000000-0000-0000-0000-000000000000', N'2年', N'cac52b7e-1f4f-4d09-9903-8360a7750c65', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'34b4cd54-2b58-41c3-9a49-b410932796d5', N'00000000-0000-0000-0000-000000000000', N'高级', N'bf309285-b83e-404a-8baa-167b88028db5', 3, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'aabe767d-9bb0-482f-b29b-b43cb0d9aceb', N'00000000-0000-0000-0000-000000000000', N'团员', N'5e9d84b6-2cf2-4590-910e-9db30ee8126c', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'1ad263db-8dfc-492c-a967-bd68c573c51a', N'00000000-0000-0000-0000-000000000000', N'本科', N'bcd1fbb8-9209-44d7-af77-9f6ecec246c0', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'c3b34299-d1c6-4f4a-b938-bec5b76877e2', N'00000000-0000-0000-0000-000000000000', N'人文', N'0f65f497-f371-48e3-b0b1-9226487a1c35', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'c5c721ec-ff78-455d-957e-bfd4bac0b256', N'00000000-0000-0000-0000-000000000000', N'数学', N'5c58325f-a70b-4ab1-a0f0-1eb07aa1c14f', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'e25c6d63-d189-42ce-aabc-bfd9df782f7f', N'00000000-0000-0000-0000-000000000000', N'汉族', N'f5c29858-238c-41fe-9bc4-2ca0acec613d', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'688dffb3-ee14-45e6-9049-c0adcc64d51f', N'00000000-0000-0000-0000-000000000000', N'体育生', N'dd3afa0d-5c46-4820-a226-95ab8fa82134', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'00c77275-6012-4e4d-a3bd-cf648fe39353', N'adef9849-9184-495a-b746-8394957a3bee', N'养老保险', N'22965e30-9b7a-4c38-af54-8c4b7303f1e6', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'7ca72548-6c6b-48cb-a578-d1bdbfaeb447', N'52153566-b386-40d2-bd82-1f3ec26a89f6', N'小型车祸意外保险', N'22965e30-9b7a-4c38-af54-8c4b7303f1e6', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'ab32b169-9793-42a9-b519-d52eb870e65a', N'00000000-0000-0000-0000-000000000000', N'专科', N'bcd1fbb8-9209-44d7-af77-9f6ecec246c0', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'8f2cdfda-6d1d-4abc-a169-dfddde5d2c1f', N'00000000-0000-0000-0000-000000000000', N'科学', N'0f65f497-f371-48e3-b0b1-9226487a1c35', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'595cbdfb-75a9-4dff-bcab-ea71ee8513c3', N'00000000-0000-0000-0000-000000000000', N'学生证', N'029c58d8-9ab3-4ef9-807c-275c3fa6dba8', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'f7466922-882a-4290-8627-eb2956e09d71', N'00000000-0000-0000-0000-000000000000', N'优', N'ef31b612-f457-41a1-a4bc-9d416ec2a757', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'3c76a079-20c6-4c04-98e8-f518595d4ad5', N'00000000-0000-0000-0000-000000000000', N'后勤', N'b5ff1e1c-2802-49ad-a9f0-1331ec704207', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'cabf7495-a5d8-4eb1-8f8e-fbc0bfea0bb2', N'00000000-0000-0000-0000-000000000000', N'初级', N'bf309285-b83e-404a-8baa-167b88028db5', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'7b82ea31-b1be-4d58-a13e-fcd63fe88376', N'00000000-0000-0000-0000-000000000000', N'中级教师资格证', N'b39e45dd-7ac2-4ab2-8241-a3fdebd9f4eb', 1, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'3291641a-5f89-468e-99e3-fd9f8000ce35', N'00000000-0000-0000-0000-000000000000', N'乙', N'27927422-1e91-4ff2-aef0-8942fd30aef8', 2, 0) INSERT [dbo].[P_Dictionary] ([D_ObjectID], [D_ParentID], [D_Name], [D_TypeID], [D_Sort], [D_IsDelete]) VALUES (N'98231747-24fa-4d40-9d55-ff8485b78ae3', N'00000000-0000-0000-0000-000000000000', N'良', N'ef31b612-f457-41a1-a4bc-9d416ec2a757', 2, 0)
实现sql:
;WITH T AS ( SELECT *,CAST(CAST(D_Sort AS nvarchar)+CAST(D_ObjectID AS nvarchar(100)) as nvarchar(100)) AS px FROM dbo.P_Dictionary AS A WHERE NOT EXISTS(SELECT * FROM dbo.P_Dictionary WHERE D_ObjectID=A.D_ParentID) UNION ALL SELECT A.*,CAST(B.px+CAST(A.D_Sort AS nvarchar)+CAST(A.D_ObjectID AS nvarchar(100)) AS nvarchar(100)) FROM dbo.P_Dictionary AS A JOIN T AS B ON A.D_ParentID=B.D_ObjectID ) SELECT * FROM T ORDER BY D_TypeID, px
