树形排序

最新推荐文章于 2024-03-20 16:19:33 发布

吉普赛的歌

最新推荐文章于 2024-03-20 16:19:33 发布

阅读量785

点赞数

CC 4.0 BY-SA版权

分类专栏： # 练习

本文链接：https://blog.csdn.net/yenange/article/details/84260937

练习专栏收录该内容

49 篇文章

订阅专栏

USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL 
	DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[id] NVARCHAR(MAX)
,[areaname] NVARCHAR(MAX)
,[postcode] NVARCHAR(MAX)
,[iParent] NVARCHAR(MAX)	
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'1',N'浙江',NULL,N'0')
INSERT INTO dbo.[t] VALUES(N'2',N'杭州',N'315300',N'1')
INSERT INTO dbo.[t] VALUES(N'3',N'宁波',N'315400',N'1')
INSERT INTO dbo.[t] VALUES(N'4',N'温州',N'315500',N'1')
INSERT INTO dbo.[t] VALUES(N'5',N'上海',NULL,N'0')
INSERT INTO dbo.[t] VALUES(N'6',N'浦东',N'210013',N'5')
INSERT INTO dbo.[t] VALUES(N'7',N'张江',N'210023',N'5')
INSERT INTO dbo.[t] VALUES(N'8',N'北京',NULL,N'0')
INSERT INTO dbo.[t] VALUES(N'9',N'海淀',N'100014',N'8')
INSERT INTO dbo.[t] VALUES(N'10',N'朝阳',N'100026',N'8')
INSERT INTO dbo.[t] VALUES(N'11',N'西湖',N'315300',N'2')
GO

--增加一个排序函数
IF OBJECT_ID('[Fun_GetOrderBy]') IS NOT NULL DROP FUNCTION [Fun_GetOrderBy]
GO
CREATE FUNCTION [dbo].[Fun_GetOrderBy]
(
	@id NVARCHAR(MAX)
)
RETURNS VARCHAR(500)
AS
BEGIN
	DECLARE @r VARCHAR(500)
	;with cte as
	(
	select id,iParent,id as orderBy from t where id=@id
	union all
	select a.id,a.iParent,a.id as orderBy from t A,
			cte B where A.id=B.iParent
	)
	select @r=STUFF((SELECT '_'+RIGHT('0000'+ltrim(orderBy),4) from cte order BY RIGHT('0000'+ltrim(orderBy),4) ASC FOR XML PATH('')),1,1,'')
	RETURN @r;
END
GO

--查询
SELECT
	REPLICATE(' ',LEN(tt.orderBy)-4-charindex('-',reverse(tt.orderBy))) 
		+tt.areaname AS areaName
	,tt.orderBy
FROM (
	SELECT 
	t.areaname
	,dbo.[Fun_GetOrderBy](id) AS orderBy 
	FROM t
) AS tt
ORDER BY tt.orderBy