分配具有不同记录的变量

发布于 2024-11-09 17:25:12 字数 801 浏览 3 评论 0原文

我创建了一个表值函数来返回逗号分隔的列表，但列表中仅包含不同的值时遇到问题。我的功能如下：

CREATE FUNCTION [dbo].[clientContactsConcat] ( @clnum INT )
RETURNS NVARCHAR(4000)
AS BEGIN
    DECLARE @concat NVARCHAR(4000)
    SELECT @concat =COALESCE(@concat, ' ') + clcontact + ', '
    FROM    dbo.client
    WHERE crelated = @clnum
    AND NOT clcontact IS NULL
    SELECT  @concat = SUBSTRING(@concat, 2, LEN(@concat) - 2)
    RETURN ( @concat )
END

当前将列出所有值，当我将 SELECT 添加修改为 SELECT DISTINCT @concat =COALESCE(@concat, ' ') + clcontact + ', '... 时它只会返回 select 返回的第一个结果。

当与 COALESCE 一起分配给变量时，有没有办法只返回不同的记录？

对于以下记录：

clcontact
---------------------
Mr S Smith
Mrs R Jones
Ms L Morrison
Mrs R Jones

我希望返回以下信息：Mr S Smith、Mrs R Jones、Ms L Morrison。

原文

I have created a Table-Valued function to return a comma seperated list, but am having problems in the list to contain only distinct values. My function is as follows:

CREATE FUNCTION [dbo].[clientContactsConcat] ( @clnum INT )
RETURNS NVARCHAR(4000)
AS BEGIN
    DECLARE @concat NVARCHAR(4000)
    SELECT @concat =COALESCE(@concat, ' ') + clcontact + ', '
    FROM    dbo.client
    WHERE crelated = @clnum
    AND NOT clcontact IS NULL
    SELECT  @concat = SUBSTRING(@concat, 2, LEN(@concat) - 2)
    RETURN ( @concat )
END

This will currently list all values, when I add modify the SELECT to SELECT DISTINCT @concat =COALESCE(@concat, ' ') + clcontact + ', '... then it will only return the first result returned by the select.

Is there any way to return only the distinct records when assigning to a variable in conjunction with the COALESCE?

For the following records:

clcontact
---------------------
Mr S Smith
Mrs R Jones
Ms L Morrison
Mrs R Jones

I am wanting the following to be returned: Mr S Smith, Mrs R Jones, Ms L Morrison.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

独﹏钓一江月 2024-11-16 17:25:12

您可以尝试将 SELECT DISTINCT 放入派生表中。

 SELECT @concat =COALESCE(@concat, ' ') +  clcontact
 FROM
 (

    SELECT DISTINCT  clcontact + ', ' AS clcontact
    FROM    dbo.client
    WHERE crelated = @clnum
    AND NOT clcontact IS NULL
  ) T

请注意，这种连接字符串的方法不能保证有效，但我不保证确信 SQL Server 2000 中有任何明显更好的方法。

You could try putting the SELECT DISTINCT into a derived table.

 SELECT @concat =COALESCE(@concat, ' ') +  clcontact
 FROM
 (

    SELECT DISTINCT  clcontact + ', ' AS clcontact
    FROM    dbo.client
    WHERE crelated = @clnum
    AND NOT clcontact IS NULL
  ) T

Note that this method of concatenating strings is not guaranteed to work but I'm not sure of any obviously better way in SQL Server 2000.

回复收藏 0 原文

~没有更多了~