update A set descr = (select max(case when descr is null then menutext --加上max,然后有()括起来 else descr + '->' + A.menutext end) from test where menucode = left(A.menucode,len(A.menucode)-2)) from test A
CREATE TABLE tb([id] int, [code] varchar(10), [name] nvarchar(10), [parent] int, [descr] nvarchar(50)) INSERT INTO tb SELECT 369, '02', N'基础资料', NULL, NULL UNION ALL SELECT 38823, '0201', N'住户资料', 369, NULL UNION ALL SELECT 3856, '0202', N'房产资料', 369, NULL UNION ALL SELECT 4302, '020201', N'管理区', 3856, NULL UNION ALL SELECT 10387, '020202', N'楼宇', 3856, NULL UNION ALL SELECT 4303, '020203', N'房屋', 3856, NULL GOCREATE FUNCTION GetDesc(@CODE VARCHAR(10)) RETURNS NVARCHAR(100) AS BEGIN DECLARE @RE NVARCHAR(100) SET @RE = '' DECLARE @I TINYINT SET @I = 1 WHILE 2 * @I <= LEN(@CODE) BEGIN SELECT @RE = @RE + [name] + '->' FROM tb WHERE CODE = LEFT(@CODE, @I * 2) SET @I = @I + 1 END RETURN LEFT(@RE, LEN(@RE) - 2) END GOUPDATE tb SET descr = dbo.GetDesc(CODE)SELECT * FROM tbDROP FUNCTION GetDesc DROP TABLE tb
--不用函数,但是不如用函数的效率高 CREATE TABLE tb([id] int, [code] varchar(10), [name] nvarchar(10), [parent] int, [descr] nvarchar(50)) INSERT INTO tb SELECT 369, '02', N'基础资料', NULL, NULL UNION ALL SELECT 38823, '0201', N'住户资料', 369, NULL UNION ALL SELECT 3856, '0202', N'房产资料', 369, NULL UNION ALL SELECT 4302, '020201', N'管理区', 3856, NULL UNION ALL SELECT 10387, '020202', N'楼宇', 3856, NULL UNION ALL SELECT 4303, '020203', N'房屋', 3856, NULL GODECLARE @LEN TINYINT, @I TINYINT SELECT @LEN = MAX(LEN(CODE)) FROM tb SET @I = 2WHILE @I <= @LEN BEGIN UPDATE A SET descr = CASE WHEN @I = 2 THEN (SELECT [name] FROM tb WHERE CODE = LEFT(A.CODE, @I)) ELSE descr + '->' + (SELECT [name] FROM tb WHERE CODE = LEFT(A.CODE, @I)) END FROM tb A WHERE LEN(CODE) >= @I SET @I = @I + 2 ENDSELECT * FROM tbDROP TABLE tb
CREATE TABLE ta([id] int, [code] varchar(10), [name] nvarchar(10), [parent] int, [descr] nvarchar(50)) INSERT INTO ta SELECT 369, '02', N'基础资料', NULL, NULL UNION ALL SELECT 38823, '0201', N'住户资料', 369, NULL UNION ALL SELECT 3856, '0202', N'房产资料', 369, NULL UNION ALL SELECT 4302, '020201', N'管理区', 3856, NULL UNION ALL SELECT 10387, '020202', N'楼宇', 3856, NULL UNION ALL SELECT 4303, '020203', N'房屋', 3856, NULL declare @i int ,@j int set @i=2 set @j=@i-2 while exists(select 1 from ta where len(code)=@i) begin update a set a.descr=isnull(a.descr+'->','')+b.name from ta a ,ta b where left(a.code,@i)=left(b.code,len(b.code)-@j) --and left()这一段想不出怎样定义,先发出来大家研究 select @i=@i+2 end id code name parent descr ----------- ---------- ---------- ----------- -------------------------------------------------- 369 02 基础资料 NULL 基础资料->基础资料->基础资料 38823 0201 住户资料 369 基础资料->住户资料->住户资料 3856 0202 房产资料 369 基础资料->房产资料->房产资料 4302 020201 管理区 3856 基础资料->房产资料->管理区 10387 020202 楼宇 3856 基础资料->房产资料->楼宇 4303 020203 房屋 3856 基础资料->房产资料->房屋(所影响的行数为 6 行)
搞定了 CREATE TABLE ta([id] int, [code] varchar(10), [name] nvarchar(10), [parent] int, [descr] nvarchar(50)) INSERT INTO ta SELECT 369, '02', N'基础资料', NULL, NULL UNION ALL SELECT 38823, '0201', N'住户资料', 369, NULL UNION ALL SELECT 3856, '0202', N'房产资料', 369, NULL UNION ALL SELECT 4302, '020201', N'管理区', 3856, NULL UNION ALL SELECT 10387, '020202', N'楼宇', 3856, NULL UNION ALL SELECT 4303, '020203', N'房屋', 3856, NULL declare @i int ,@j int set @i=2 set @j=@i-2 while exists(select 1 from ta where len(code)=@i ) begin if exists(select 1 from ta where descr is null) begin update a set a.descr=isnull(a.descr+'->','')+b.name from ta a ,ta b where left(a.code,@i)=left(b.code,len(b.code)-@j) --and a.descr not like ('%'+b.name+'%') end else update a set a.descr=isnull(a.descr+'->','')+b.name from ta a ,ta b where left(a.code,@i)=left(b.code,len(b.code)-@j) and right(a.descr,len(a.name))!=a.name select @i=@i+2 end select * from ta id code name parent descr ----------- ---------- ---------- ----------- -------------------------------------------------- 369 02 基础资料 NULL 基础资料 38823 0201 住户资料 369 基础资料->住户资料 3856 0202 房产资料 369 基础资料->房产资料 4302 020201 管理区 3856 基础资料->房产资料->管理区 10387 020202 楼宇 3856 基础资料->房产资料->楼宇 4303 020203 房屋 3856 基础资料->房产资料->房屋(所影响的行数为 6 行)
简化一下语句: CREATE TABLE ta([id] int, [code] varchar(10), [name] nvarchar(10), [parent] int, [descr] nvarchar(50)) INSERT INTO ta SELECT 369, '02', N'基础资料', NULL, NULL UNION ALL SELECT 38823, '0201', N'住户资料', 369, NULL UNION ALL SELECT 3856, '0202', N'房产资料', 369, NULL UNION ALL SELECT 4302, '020201', N'管理区', 3856, NULL UNION ALL SELECT 10387, '020202', N'楼宇', 3856, NULL UNION ALL SELECT 4303, '020203', N'房屋', 3856, NULLdeclare @i int ,@j int set @i=2 set @j=@i-2 while exists(select 1 from ta where len(code)=@i) begin update a set a.descr=isnull(a.descr+'->','')+b.name from ta a ,ta b where left(a.code,@i)=left(b.code,len(b.code)-@j) and(right(a.descr,len(a.name))!=a.name or a.descr is null) select @i=@i+2 end select * from ta--drop table ta id code name parent descr ----------- ---------- ---------- ----------- -------------------------------------------------- 369 02 基础资料 NULL 基础资料 38823 0201 住户资料 369 基础资料->住户资料 3856 0202 房产资料 369 基础资料->房产资料 4302 020201 管理区 3856 基础资料->房产资料->管理区 10387 020202 楼宇 3856 基础资料->房产资料->楼宇 4303 020203 房屋 3856 基础资料->房产资料->房屋(所影响的行数为 6 行)
id code parent_id
----------- ------ -----------
369 02 NULL
38823 0201 NULL
3856 0202 NULL
4302 020201 NULL
10387 020202 NULL
4303 020203 NULL上面id=38823,是否也是0
(select max(case when descr is null then menutext --加上max,然后有()括起来
else descr + '->' + A.menutext end)
from test where menucode = left(A.menucode,len(A.menucode)-2))
from test A
INSERT INTO tb
SELECT 369, '02', N'基础资料', NULL, NULL
UNION ALL SELECT 38823, '0201', N'住户资料', 369, NULL
UNION ALL SELECT 3856, '0202', N'房产资料', 369, NULL
UNION ALL SELECT 4302, '020201', N'管理区', 3856, NULL
UNION ALL SELECT 10387, '020202', N'楼宇', 3856, NULL
UNION ALL SELECT 4303, '020203', N'房屋', 3856, NULL
GOCREATE FUNCTION GetDesc(@CODE VARCHAR(10))
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @RE NVARCHAR(100)
SET @RE = ''
DECLARE @I TINYINT
SET @I = 1
WHILE 2 * @I <= LEN(@CODE)
BEGIN
SELECT @RE = @RE + [name] + '->' FROM tb WHERE CODE = LEFT(@CODE, @I * 2)
SET @I = @I + 1
END
RETURN LEFT(@RE, LEN(@RE) - 2)
END
GOUPDATE tb SET descr = dbo.GetDesc(CODE)SELECT * FROM tbDROP FUNCTION GetDesc
DROP TABLE tb
这样可以避免多值满足条件冲突
如不存在多值,楼主的方法可以满足
descr + '->' + A.menutext --这两列存不存在都为null的情况
CREATE TABLE tb([id] int, [code] varchar(10), [name] nvarchar(10), [parent] int, [descr] nvarchar(50))
INSERT INTO tb
SELECT 369, '02', N'基础资料', NULL, NULL
UNION ALL SELECT 38823, '0201', N'住户资料', 369, NULL
UNION ALL SELECT 3856, '0202', N'房产资料', 369, NULL
UNION ALL SELECT 4302, '020201', N'管理区', 3856, NULL
UNION ALL SELECT 10387, '020202', N'楼宇', 3856, NULL
UNION ALL SELECT 4303, '020203', N'房屋', 3856, NULL
GODECLARE @LEN TINYINT, @I TINYINT
SELECT @LEN = MAX(LEN(CODE)) FROM tb
SET @I = 2WHILE @I <= @LEN
BEGIN
UPDATE A SET descr = CASE WHEN @I = 2 THEN (SELECT [name] FROM tb WHERE CODE = LEFT(A.CODE, @I))
ELSE descr + '->' + (SELECT [name] FROM tb WHERE CODE = LEFT(A.CODE, @I)) END
FROM tb A
WHERE LEN(CODE) >= @I
SET @I = @I + 2
ENDSELECT * FROM tbDROP TABLE tb
服务器: 消息 536,级别 16,状态 3,过程 GetDesc,行 15
向 substring 函数传递了无效的 length 参数。
语句已终止。只是希望如果能用一条语句搞定就好了...
INSERT INTO ta
SELECT 369, '02', N'基础资料', NULL, NULL
UNION ALL SELECT 38823, '0201', N'住户资料', 369, NULL
UNION ALL SELECT 3856, '0202', N'房产资料', 369, NULL
UNION ALL SELECT 4302, '020201', N'管理区', 3856, NULL
UNION ALL SELECT 10387, '020202', N'楼宇', 3856, NULL
UNION ALL SELECT 4303, '020203', N'房屋', 3856, NULL
declare @i int ,@j int
set @i=2
set @j=@i-2
while exists(select 1 from ta where len(code)=@i)
begin
update a
set a.descr=isnull(a.descr+'->','')+b.name
from ta a ,ta b
where left(a.code,@i)=left(b.code,len(b.code)-@j) --and left()这一段想不出怎样定义,先发出来大家研究
select @i=@i+2
end
id code name parent descr
----------- ---------- ---------- ----------- --------------------------------------------------
369 02 基础资料 NULL 基础资料->基础资料->基础资料
38823 0201 住户资料 369 基础资料->住户资料->住户资料
3856 0202 房产资料 369 基础资料->房产资料->房产资料
4302 020201 管理区 3856 基础资料->房产资料->管理区
10387 020202 楼宇 3856 基础资料->房产资料->楼宇
4303 020203 房屋 3856 基础资料->房产资料->房屋(所影响的行数为 6 行)
CREATE TABLE ta([id] int, [code] varchar(10), [name] nvarchar(10), [parent] int, [descr] nvarchar(50))
INSERT INTO ta
SELECT 369, '02', N'基础资料', NULL, NULL
UNION ALL SELECT 38823, '0201', N'住户资料', 369, NULL
UNION ALL SELECT 3856, '0202', N'房产资料', 369, NULL
UNION ALL SELECT 4302, '020201', N'管理区', 3856, NULL
UNION ALL SELECT 10387, '020202', N'楼宇', 3856, NULL
UNION ALL SELECT 4303, '020203', N'房屋', 3856, NULL
declare @i int ,@j int
set @i=2
set @j=@i-2
while exists(select 1 from ta where len(code)=@i )
begin
if exists(select 1 from ta where descr is null)
begin
update a
set a.descr=isnull(a.descr+'->','')+b.name
from ta a ,ta b
where left(a.code,@i)=left(b.code,len(b.code)-@j) --and a.descr not like ('%'+b.name+'%')
end
else
update a
set a.descr=isnull(a.descr+'->','')+b.name
from ta a ,ta b
where left(a.code,@i)=left(b.code,len(b.code)-@j) and right(a.descr,len(a.name))!=a.name
select @i=@i+2
end
select * from ta
id code name parent descr
----------- ---------- ---------- ----------- --------------------------------------------------
369 02 基础资料 NULL 基础资料
38823 0201 住户资料 369 基础资料->住户资料
3856 0202 房产资料 369 基础资料->房产资料
4302 020201 管理区 3856 基础资料->房产资料->管理区
10387 020202 楼宇 3856 基础资料->房产资料->楼宇
4303 020203 房屋 3856 基础资料->房产资料->房屋(所影响的行数为 6 行)
CREATE TABLE ta([id] int, [code] varchar(10), [name] nvarchar(10), [parent] int, [descr] nvarchar(50))
INSERT INTO ta
SELECT 369, '02', N'基础资料', NULL, NULL
UNION ALL SELECT 38823, '0201', N'住户资料', 369, NULL
UNION ALL SELECT 3856, '0202', N'房产资料', 369, NULL
UNION ALL SELECT 4302, '020201', N'管理区', 3856, NULL
UNION ALL SELECT 10387, '020202', N'楼宇', 3856, NULL
UNION ALL SELECT 4303, '020203', N'房屋', 3856, NULLdeclare @i int ,@j int
set @i=2
set @j=@i-2
while exists(select 1 from ta where len(code)=@i)
begin
update a
set a.descr=isnull(a.descr+'->','')+b.name
from ta a ,ta b
where left(a.code,@i)=left(b.code,len(b.code)-@j) and(right(a.descr,len(a.name))!=a.name or a.descr is null)
select @i=@i+2
end
select * from ta--drop table ta
id code name parent descr
----------- ---------- ---------- ----------- --------------------------------------------------
369 02 基础资料 NULL 基础资料
38823 0201 住户资料 369 基础资料->住户资料
3856 0202 房产资料 369 基础资料->房产资料
4302 020201 管理区 3856 基础资料->房产资料->管理区
10387 020202 楼宇 3856 基础资料->房产资料->楼宇
4303 020203 房屋 3856 基础资料->房产资料->房屋(所影响的行数为 6 行)