SQL：如何在派生表中声明变量？

发布于 2025-01-03 14:06:15 字数 460 浏览 3 评论 0原文

我正在尝试创建一个逗号分隔的列表，并且正在使用派生表。但我无法在 LEFT OUTER JOIN 中声明变量...我该怎么做？

    LEFT OUTER JOIN (

               DECLARE @String AS VARCHAR(MAX) = NULL
               SELECT @String = COALESCE(@String + ', ','') + Name
                 FROM MyTable
               SELECT @String, Col1
                 FROM MyTable
                GROUP BY Col1

    ) AS T8
   ON This = That

它在 Declare 关键字上给我一个错误，提示语法错误。

谢谢！

原文

I'm trying to create a comma separated list and I'm using a derived table. But I cannot declare the variable within the LEFT OUTER JOIN... how can I do this?

    LEFT OUTER JOIN (

               DECLARE @String AS VARCHAR(MAX) = NULL
               SELECT @String = COALESCE(@String + ', ','') + Name
                 FROM MyTable
               SELECT @String, Col1
                 FROM MyTable
                GROUP BY Col1

    ) AS T8
   ON This = That

It gives me an error on the Declare keyword that says, Incorrect Syntax.

Thanks!

分享到QQ

分享到微博

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

发布评论

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

淡淡的优雅 2025-01-10 14:06:15

您不能在派生表内声明变量。

但是您可以在语句之外声明它并以与示例中相同的方式使用它

回复收藏 0 原文

半寸时光 2025-01-10 14:06:15

您的要求没有意义，因为该变量不能真正在表变量内部使用。如果您想在表变量之后使用它，它仍然没有意义...您是否期望变量的多个实例，对于 Col1 的每个不同值一次？也许你的意思是这样的：

LEFT OUTER JOIN
(
  SELECT Col1, String = STUFF((
     SELECT ',' + Name
        FROM dbo.MyTable AS i 
        WHERE i.Col1 = o.Col1 
        FOR XML PATH(''), 
        TYPE).value(N'./text()[1]', N'nvarchar(max)'),1,1,'')
  FROM dbo.MyTable AS o
  GROUP BY Col1
) AS T8
ON This = That

但是，T8 有点让我害怕。此连接已涉及多少张表？

Your requirement doesn't make sense because the variable can't really be used inside the table variable. And if you want to use it after the table variable, it still doesn't make sense... do you expect multiple instances of the variable, once for each distinct value of Col1? Maybe you meant this:

LEFT OUTER JOIN
(
  SELECT Col1, String = STUFF((
     SELECT ',' + Name
        FROM dbo.MyTable AS i 
        WHERE i.Col1 = o.Col1 
        FOR XML PATH(''), 
        TYPE).value(N'./text()[1]', N'nvarchar(max)'),1,1,'')
  FROM dbo.MyTable AS o
  GROUP BY Col1
) AS T8
ON This = That

However, T8 kind of scares me a little. How many tables are already involved in this join?

回复收藏 0 原文

~没有更多了~