我想用表中的等效文本替换@CommentsTable列"注释"中的数字@ModTable而不在单个 SELECT 中使用 UDF。可能伴有 CTE。尝试了带有替换的东西,但没有运气。
任何建议都会有很大帮助!
样本:
DECLARE @ModTable TABLE
(
ID INT,
ModName VARCHAR(10),
ModPos VARCHAR(10)
)
DECLARE @CommentsTable TABLE
(
ID INT,
Comments VARCHAR(100)
)
INSERT INTO @CommentsTable
VALUES (1, 'MyFirst 5 Comments with 6'),
(2, 'MySecond comments'),
(3, 'MyThird comments 5')
INSERT INTO @ModTABLE
VALUES (1, '[FIVE]', '5'),
(1, '[SIX]', '6'),
(1, '[ONE]', '1'),
(1, '[TWO]', '2')
SELECT T1.ID, <<REPLACED COMMENTS>>
FROM @CommentsTable T1
GROUP BY T1.ID, T1.Comments
**Expected Result:**
ID Comments
1 MyFirst [FIVE] Comments with [SIX]
2 MySecond comments
3 MyThird comments [FIVE]
创建一个光标,跨越@ModTable
并执行每次替换一次
DECLARE replcursor FOR SELECT ModPos, ModName FROM @ModTable;
OPEN replcursor;
DECLARE modpos varchar(100) DEFAULT "";
DECLARE modname varchar(100) DEFAULT "";
get_loop: LOOP
FETCH replcursor INTO @modpos, @modname
SELECT T1.ID, REPLACE(T1.Comments, @modpos, @modname)
FROM @CommentsTable T1
GROUP BY T1.ID, T1.Comments
END LOOP get_loop;
当然,您可以将结果存储在临时表中,并在循环结束时完全获取结果。
您可以使用 while 循环来迭代记录和模组。 我稍微修改了您的@ModTable,使其具有 ID 的唯一值。 如果这不是您的数据结构,则可以使用像 ROW_NUMBER() 这样的窗口函数来获取可以迭代的唯一值。
修改后的脚本示例:
DECLARE @ModTable TABLE
(
ID INT,
ModName VARCHAR(10),
ModPos VARCHAR(10)
)
DECLARE @CommentsTable TABLE
(
ID INT,
Comments VARCHAR(100)
)
INSERT INTO @CommentsTable
VALUES (1, 'MyFirst 5 Comments with 6'),
(2, 'MySecond comments'),
(3, 'MyThird comments 5')
INSERT INTO @ModTABLE
VALUES (1, '[FIVE]', '5'),
(2, '[SIX]', '6'),
(3, '[ONE]', '1'),
(4, '[TWO]', '2')
declare @revisedTable table (id int, comments varchar(100))
declare @modcount int = (select count(*) from @ModTable)
declare @commentcount int = (select count(*) from @CommentsTable)
declare @currentcomment varchar(100) = ''
while @commentcount > 0
begin
set @modcount = (select count(*) from @ModTable)
set @currentcomment = (select Comments from @CommentsTable where ID = @commentcount)
while @modcount > 0
begin
set @currentcomment = REPLACE( @currentcomment,
(SELECT TOP 1 ModPos FROM @ModTable WHERE ID = @modcount),
(SELECT TOP 1 ModName FROM @ModTable WHERE ID = @modcount))
set @modcount = @modcount - 1
end
INSERT INTO @revisedTable (id, comments)
SELECT @commentcount, @currentcomment
set @commentcount = @commentcount - 1
end
SELECT *
FROM @revisedTable
order by id
我认为即使我通常避免递归查询,也会起作用。不过,它假设您有连续的 ID:
with Comments as
(
select ID, Comments, 0 as ConnectID
from @CommentsTable
union all
select ID, replace(c.Comments, m.ModPos, m.ModName), m.ConnectID
from Comments c inner join @ModTable m on m.ConnectID = c.ConnectID + 1
)
select * from Comments
where ConnectID = (select max(ID) from @ModTable)
=> CLR Function()
由于我在"CommentsTable"中有很多记录,并且"ModTable"的每个注释都会有多个ModName,因此最终决定使用CLR函数。感谢大家的建议和指点。