62,041
社区成员
发帖
与我相关
我的任务
分享求树状列表
数据库字段为：
a/1/2/1.txt
a/1/2.txt
b/c/c/3.jpg
我要的效果
a
1
2.txt
2
1.txt
b
c
c
3.jpg
请问怎么实现？
a/1/2/1.txt
a/1/2.txt
b/c/c/3.jpg
我要的效果
a
1
2.txt
2
1.txt
b
c
c
3.jpg
请问怎么实现？
...全文
请发表友善的回复…
发表回复
Hello World, 2013-02-07
- 打赏
- 举报
代码有点~~
DECLARE @code VARCHAR(1000)
DECLARE @msgTable TABLE ( txt VARCHAR(1000) )
SET @code = '' ;
WITH tb ( txt )
AS ( SELECT 'a/b/1.txt'
UNION ALL
SELECT 'a/b/2.txt'
UNION ALL
SELECT 'a/b/c/2.txt'
UNION ALL
SELECT 'a/2.jpg'
UNION ALL
SELECT 'b/1/5.jpg'
UNION ALL
SELECT 'c/2.jpg'
UNION ALL
SELECT 'c/1/5.jpg'
),
cte
AS ( SELECT DISTINCT
* ,
CASE WHEN txt <> code
THEN SUBSTRING(code, 1, LEN(code) - LEN(txt) - 1)
ELSE ''
END AS parentCode
FROM ( SELECT Code = SUBSTRING(t.txt + '/', 1,
number - 1) ,
txt = ( CASE WHEN CHARINDEX('/',
REVERSE(SUBSTRING(t.txt
+ '/', 1,
number - 1)))
- 1 < 0
THEN SUBSTRING(t.txt + '/', 1,
number - 1)
ELSE RIGHT(SUBSTRING(t.txt
+ '/', 1,
number - 1),
CHARINDEX('/',
REVERSE(SUBSTRING(t.txt
+ '/', 1,
number - 1)))
- 1)
END )
FROM tb t ,
master..spt_values
WHERE type = 'p'
AND number BETWEEN 1 AND LEN(t.txt) + 1
AND SUBSTRING(t.txt + '/', number, 1) = '/'
) t
)
SELECT *
INTO #tb
FROM cte
DECLARE Mycursor CURSOR
FOR
SELECT DISTINCT
code
FROM #tb
WHERE parentCode = ''
OPEN Mycursor
FETCH NEXT FROM Mycursor INTO @code
PRINT @@FETCH_STATUS
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @code ;
WITH outmsg
AS ( SELECT DISTINCT
* ,
newTxt = REPLICATE(' ',
CASE parentCode
WHEN '' THEN 0
ELSE LEN(parentCode)
- LEN(REPLACE(parentCode,
'/', '')) + 1
END) + txt
FROM #tb
WHERE Code = @code
UNION ALL
SELECT c.* ,
newTxt = REPLICATE(' ',
CASE c.parentCode
WHEN '' THEN 0
ELSE LEN(c.parentCode)
- LEN(REPLACE(c.parentCode,
'/', '')) + 1
END) + c.txt
FROM #tb c
INNER JOIN outmsg ON outmsg.code = c.parentCode
)
INSERT INTO @msgTable
( txt )
SELECT newTxt
FROM outmsg
FETCH NEXT FROM Mycursor INTO @code
END
CLOSE Mycursor
DEALLOCATE Mycursor
DROP TABLE #tb
SELECT * FROM @msgTableHello World, 2013-02-07
- 打赏
- 举报
4楼里面5.jpg本来应该在c\1\下面的,跑到了c\2.jpg\下面了
Hello World, 2013-02-07
- 打赏
- 举报
上面一个有问题,重新修改下
结果:
创建2个表用来存放临时数据(也可以用临时表):
创建存储过程,递归调用得到结果
测试:
结果:
创建2个表用来存放临时数据(也可以用临时表):
USE [test]
GO
/****** Object: Table [dbo].[treeView] Script Date: 02/07/2013 15:50:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[treeView](
[code] [varchar](1000) NULL,
[txt] [varchar](1000) NULL,
[parentCode] [varchar](1000) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [test]
GO
/****** Object: Table [dbo].[treeViewSelected] Script Date: 02/07/2013 15:51:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[treeViewSelected](
[id] [int] IDENTITY(1,1) NOT NULL,
[txt] [varchar](1000) NULL,
[code] [varchar](1000) NULL,
[parentCode] [varchar](1000) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
创建存储过程,递归调用得到结果
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[getTreeView] @code VARCHAR(1000)
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @newCode VARCHAR(1000) ,
@txt VARCHAR(1000) ,
@parentCode VARCHAR(1000)
DECLARE Mycursor CURSOR LOCAL
FOR
SELECT DISTINCT
code ,
txt ,
parentCode
FROM treeView
WHERE parentCode = @code
OPEN Mycursor
FETCH NEXT FROM Mycursor INTO @newCode, @txt, @parentCode
PRINT @@FETCH_STATUS
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO treeViewSelected
( code ,
txt ,
parentCode
)
VALUES ( @newCode ,
REPLICATE(' ',
CASE @parentCode
WHEN '' THEN 0
ELSE LEN(@parentCode)
- LEN(REPLACE(@parentCode, '/', ''))
+ 1
END) + @txt ,
@parentCode
)
EXEC getTreeView @newCode
FETCH NEXT FROM Mycursor INTO @newCode, @txt, @parentCode
END
CLOSE Mycursor
DEALLOCATE Mycursor
END测试:
WITH tb ( txt )
AS ( SELECT 'a/b/1.txt'
UNION ALL
SELECT 'a/b/2.txt'
UNION ALL
SELECT 'a/b/c/2.txt'
UNION ALL
SELECT 'a/2.jpg'
UNION ALL
SELECT 'b/1/5.jpg'
UNION ALL
SELECT 'c/2.jpg'
UNION ALL
SELECT 'c/1/5.jpg'
),
cte
AS ( SELECT DISTINCT
* ,
CASE WHEN txt <> code
THEN SUBSTRING(code, 1, LEN(code) - LEN(txt) - 1)
ELSE ''
END AS parentCode
FROM ( SELECT Code = SUBSTRING(t.txt + '/', 1,
number - 1) ,
txt = ( CASE WHEN CHARINDEX('/',
REVERSE(SUBSTRING(t.txt
+ '/', 1,
number - 1)))
- 1 < 0
THEN SUBSTRING(t.txt + '/', 1,
number - 1)
ELSE RIGHT(SUBSTRING(t.txt
+ '/', 1,
number - 1),
CHARINDEX('/',
REVERSE(SUBSTRING(t.txt
+ '/', 1,
number - 1)))
- 1)
END )
FROM tb t ,
master..spt_values
WHERE type = 'p'
AND number BETWEEN 1 AND LEN(t.txt) + 1
AND SUBSTRING(t.txt + '/', number, 1) = '/'
) t
)
SELECT *
INTO #tb
FROM cte
DELETE FROM treeView
INSERT INTO treeView
( txt ,
code ,
parentCode
)
SELECT txt ,
code ,
parentCode
FROM #tb
DELETE FROM treeViewSelected
EXEC [dbo].[getTreeView] ''
SELECT txt
FROM dbo.treeViewSelected
DROP TABLE #tb
joyhen 2013-02-07
- 打赏
- 举报
楼上几位犀利了，
一缕青烟 2013-02-02
- 打赏
- 举报
有人吗？还有没没回家过年的
夜色镇歌 2013-02-02
- 打赏
- 举报
骑着蜗牛去爬山 2013-02-02
- 打赏
- 举报
with tb(txt)
as(
select 'a/b/1.txt' union all
select 'a/b/2.txt' union all
select 'a/2.jpg' union all
select 'b/1/5.jpg')
,cte
as(
select
[all]=substring(t.txt+'/',1,number-1),
txt=(case when charindex('/',reverse(substring(t.txt+'/',1,number-1)))-1<0 then substring(t.txt+'/',1,number-1) else
right(substring(t.txt+'/',1,number-1),
charindex('/',reverse(substring(t.txt+'/',1,number-1)))-1)
end)
from tb t,master..spt_values where type='p' and number between 1 and len(t.txt)+1 and substring(t.txt+'/',number,1)='/'
)
select distinct txt,[all] from cte order by [all]
