将数据分配给表的列 [英] Assign Data to a column of a Table

问题描述

表结构

ID名称网址
--------------------------------
1个Test1虚拟1
2 Test1 Dummy2
3 Test2 Dummy1

我的查询:-如果用户搜索Url，则假设[Dummy1]然后在结果中显示新列，该列将显示与Url相关的所有名称，并用'〜'"
分隔
我要按此顺序生成结果:-

ID名称网址分配
-----------------------------------
1个Test1虚拟1〜Test1〜Test2〜
2 Test1 Dummy2〜Test1〜
3 Test2虚拟1〜Test1〜Test2〜

数据库中存在数百万条记录.请提出一些更好的性能解决方案?

我尝试使用东西&光标，但需要超过4分钟.

请提出一些解决方案.

在此先感谢.

Table Structure

Id Name Url
--------------------------------
1 Test1 Dummy1
2 Test1 Dummy2
3 Test2 Dummy1

My Query :- "If user Searches for Url suppose [Dummy1] then display new column in result which will display all the Names associated with Url separated by ''~'' "

I want result in this Sequence :-

Id Name Url Assign
-----------------------------------
1 Test1 Dummy1 ~Test1~Test2~
2 Test1 Dummy2 ~Test1~
3 Test2 Dummy1 ~Test1~Test2~

More than millions of record present in database. Please suggest some better performance solution ?

I tried using stuff & cursor but it takes more than 4 minutes.

Please suggest some solution.

Thanks in Advance.

推荐答案

嘿，我现在尝试了其他一些解决方案

Hey I have tried some other solutions now

ALTER FUNCTION dbo.GetURLSName
( 
    @Url VARCHAR(MAX)
) 
RETURNS VARCHAR(8000) 
AS 
BEGIN 

		DECLARE @U VARCHAR(1024) 
		 
		SELECT 
			@U = COALESCE(@U + ''~'', '''') + Name 
		FROM dbo.URLS
        WHERE Url = @Url
		 
    --DECLARE @U VARCHAR(MAX)
    --SELECT @U = ISNULL(@U+''~'', '''') 
    --    + Name  
    --    FROM dbo.URLS
    --    WHERE Url = @Url
    RETURN @U 
END 
GO 

SELECT 
    Id,
    Name, 
    dbo.GetURLSName(Url) 
    FROM dbo.URLS

但这也花费了超过4分钟的时间:(

but this also took more than 4 mins :(

这篇关于将数据分配给表的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！