如何在查询的时候再追加一个字段^_^

最新推荐文章于 2021-12-04 10:14:21 发布

原创最新推荐文章于 2021-12-04 10:14:21 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#null #function #image #insert #go #table

本文介绍了一种使用SQL处理树状结构数据的方法，通过创建表Doctree并填充数据，再利用递归函数f_getparent处理数据，最终实现了对树状结构中节点与其父节点关系的正确展示。

已知:select * from Doctree输出结果:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++

2_0_B.城市勘察类12
5_2_1.工程地质12
6_2_2.水文地质12
7_2_3.控制测量12
10_2_4.地形测量12
11_10_1.文字材料12
12_10_2.500地形图12
13_10_3.1000地形图12
14_10_4.其它比例尺12

经过函数function f_getparent

select [key],
[parent],
[text],
[image],
[selectedimage],
case when parent='0_'
then left([text],1)
else dbo.f_getparent([parent])+'-'+left([text],1) end as str
from Doctree处理后的输出结果:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2_0_B.城市勘察类12   B
5_2_1.工程地质12   B-1
6_2_2.水文地质12   B-2
7_2_3.控制测量12   B-3
10_2_4.地形测量12   B-4
11_10_1.文字材料12   B-4-1
12_10_2.500地形图12   B-4-2
13_10_3.1000地形图12   B-4-3
14_10_4.其它比例尺12   B-4-4
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

下面是实现方法:

CREATE TABLE [dbo].[Doctree] (
[key] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[parent] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[text] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[image] [smallint] NULL ,
[selectedimage] [smallint] NULL
) ON [PRIMARY]
GO
insert into Doctree
select '2_','0_','B.城市勘察类',1,2
union all select '5_','2_','1.工程地质',1,2
union all select '6_','2_','2.水文地质',1,2
union all select '7_','2_','3.控制测量',1,2
union all select '10_','2_','4.地形测量',1,2
union all select '11_','10_','1.文字材料',1,2
union all select '12_','10_','2.500地形图',1,2
union all select '13_','10_','3.1000地形图',1,2
union all select '14_','10_','4.其它比例尺',1,2
go
create function f_getparent(@parent nvarchar(40))
returns varchar(1000)
as
begin
declare @key nvarchar(40)
declare @str varchar(1000)
select @str=left([text],1)+'-',@key=[parent] from doctree where [key]=@parent
while isnull(@key,'')<>'0_'
begin
select @str=@str+left([text],1)+'-',@key=[parent] from doctree where [key]=@key
end
return reverse(left(@str,len(@str)-1))
end