22,300
社区成员




表结构如下:
ModID,ModName,URL,ParentID,DisplayOrder
1,china,url,null,null,
2,liaoning,url,1,1
3,jilin,url,1,2
4,shenyang,url,2,1
5,dalian,url,2,2
我想返回类似这样的结果:
level,modid,modname,...
1,china,...
2,liaoning,...
2,jilin,...
3,shenyang,....
4,dalian,....
-----------------------
对于楼主这结果。有点疑问。
为什么2,3的parentid是属于同一个时,他们的level都为2,
而4,5的parentid也属于同一个,而他们的level却不相同呢?按道理这两个也应该属于同一层才对噢。
这个是有其他规律吗?
--测试数据
DECLARE @t TABLE(No varchar(10))
INSERT @t SELECT '1'
UNION ALL SELECT '3'
UNION ALL SELECT '302'
UNION ALL SELECT '305'
UNION ALL SELECT '305001'
UNION ALL SELECT '305005'
UNION ALL SELECT '6'
UNION ALL SELECT '601'
--重排编码
UPDATE a SET
No=RIGHT(--重排第一层编码
(SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND LEN(No)=1),1)
+CASE --重排第二层编码
WHEN LEN(No)>1
THEN RIGHT(100+
(SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND No LIKE LEFT(a.NO,1)+'__'),2)
ELSE '' END
+CASE --重排第三层编码
WHEN LEN(No)>3
THEN RIGHT(1000+
(SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND No LIKE LEFT(a.NO,3)+'___'),3)
ELSE '' END
FROM @t a
--显示处理结果
SELECT * FROM @t
/*--结果
No
----------
1
2
201
202
202001
202002
3
301
--*/
树形数据编号重排的通用存储过程
--重排编码的存储过程
CREATE PROC p_RTaxisCode
@TableName sysname, --重排编码的表名
@FieldName sysname, --编码字段名
@CodeRule varchar(100) --以逗号分隔的编码规则,每层编码的长度,比如1,2,3,表示有三层编码,第一层长度为1,第二层长度为2,第三层长度为3
AS
--参数检查
IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsUserTable'),0)=0
BEGIN
RAISERROR(N'"%s"不存在,或者不是用户表',1,16,@TableName)
RETURN
END
IF NOT EXISTS(SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TableName) AND name=@FieldName)
BEGIN
RAISERROR(N'列名"%s"在用户表"%s"中无效',1,16,@FieldName,@TableName)
RETURN
END
IF ISNULL(@CodeRule,'')=''
BEGIN
RAISERROR(N'必须编码规则字符串',1,16)
RETURN
END
IF PATINDEX(N'%[^0-9^,]%',@CodeRule)>0
BEGIN
RAISERROR(N'编码规则字符串"%s"中只能包含数字和逗号(,)',1,16,@CodeRule)
RETURN
END
--生成编码重排处理语句
DECLARE @s nvarchar(4000),@len int,@lens int
SELECT
@TableName=QUOTENAME(@TableName),
@FieldName=QUOTENAME(@FieldName),
@len=LEFT(@CodeRule,CHARINDEX(N',',@CodeRule+N',')-1),
@lens=@len,
@CodeRule=STUFF(@CodeRule,1,CHARINDEX(N',',@CodeRule+N','),N'')+N',',
@s=N'RIGHT('+CAST(POWER(10,@len) as varchar)
+N'+(SELECT COUNT(DISTINCT '+@FieldName
+N') FROM '+@TableName
+N' WHERE '+@FieldName+N'<=a.'+@FieldName
+N' AND LEN('+@FieldName+N')='+CAST(@len as varchar)
+N'),'+CAST(@len as varchar)+N')'
WHILE LEN(@CodeRule)>1
BEGIN
SELECT
@len=LEFT(@CodeRule,CHARINDEX(N',',@CodeRule)-1),
@s=@s
+N'+CASE WHEN LEN('+@FieldName
+N')>'+CAST(@lens as varchar)
+N' THEN RIGHT('+CAST(POWER(10,@len) as varchar)
+N'+(SELECT COUNT(DISTINCT '+@FieldName
+N') FROM '+@TableName
+N' WHERE '+@FieldName+N'<=a.'+@FieldName
+N' AND '+@FieldName+N' LIKE LEFT(a.'+@FieldName
+N','+CAST(@lens as varchar)
+N')+'''+REPLICATE(N'_',@len)
+N'''),'+CAST(@len as varchar)
+N') ELSE '''' END',
@lens=@lens+@len,
@CodeRule=STUFF(@CodeRule,1,CHARINDEX(N',',@CodeRule),N'')
END
EXEC(N'UPDATE a SET '+@FieldName+N'='+@s+N' FROM '+@TableName+N' a')
树形数据分级汇总示例
--测试数据
DECLARE @a TABLE(No varchar(10),Name varchar(10))
INSERT @a SELECT '101' ,'现金'
UNION ALL SELECT '102' ,'银行存款'
UNION ALL SELECT '10201','工行'
UNION ALL SELECT '10202','建行'
UNION ALL SELECT '10203','农行'
DECLARE @b TABLE(No varchar(10),[Money] money )
INSERT @b SELECT '101' ,100
UNION ALL SELECT '10201',20
UNION ALL SELECT '10202',120
--逐级汇总查询
SELECT a.No,a.Name,
[Money]=ISNULL(SUM([Money]),0)
FROM @a a
LEFT JOIN @b b ON b.No LIKE a.No+'%'
GROUP BY a.No,a.Name
ORDER BY a.No
/*--结果
No Name Money
---------------- ----------------- ---------------------
101 现金 100.0000
102 银行存款 140.0000
10201 工行 20.0000
10202 建行 120.0000
10203 农行 .0000
--*/