SQL 在SQL查询中选择多行到一个变量中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5262513/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select multiple rows into one variable in SQL Query
提问by Joakim
SELECT ft.ThreadTitle AS Title,
fr.ReplyText + ' ' + ua2.Username + ' ' + ft.ThreadText + ' ' +
-- THIS NEXT LINE IS WHAT I WANT TO ACHIEVE:
(Select ReplyText from ForumReply Where ThreadID=ft.ThreadID)
-- THE ABOVE LINE HAVE MULTIPLE ROWS/VALUES THAT I WANT TO JOIN INTO ONE VARIABLE. HOW?
AS [Content],
ss.Domain,
ss.SiteID,
ft.ThreadID AS ObjectId
FROM dbo.ForumReply AS fr INNER JOIN
dbo.ForumThreads AS ft ON fr.ThreadID = ft.ThreadID INNER JOIN
dbo.User_Account AS ua1 ON ft.CreateByUserID = ua1.UserID INNER JOIN
dbo.User_Account AS ua2 ON fr.ReplyUserID = ua2.UserID INNER JOIN
dbo.SysT_Site AS ss ON ua1.SiteID = ss.SiteID
This query gives error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
此查询给出错误： 子查询返回了 1 个以上的值。当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的。
How do I rewrite this query to work so that I get all the values into one variable? The end result need to be a "View" that looks like this:
如何重写此查询以使其工作，以便将所有值放入一个变量中？最终结果需要是一个如下所示的“视图”：
.ObjectID int
.ObjectID 整数
.Content (string with all text that exists in the Forumthread.threadText and forumReply.ReplyText)
.Content（包含 Forumthread.threadText 和 forumReply.ReplyText 中存在的所有文本的字符串）
.Domain string
.域字符串
.SiteID int
.SiteID 整数
回答by jfollas
Building upon Martin's comment:
基于马丁的评论：
DECLARE @t TABLE (id int, ReplyText varchar(100))
INSERT INTO @t (id, ReplyText) VALUES (1, 'So Long,')
INSERT INTO @t (id, ReplyText) VALUES (2, 'And Thanks for')
INSERT INTO @t (id, ReplyText) VALUES (3, 'All of the Fish!')
SELECT (SELECT replytext + ' ' FROM @t FOR XML PATH('')) AS CONTENT
回答by Richard Friend
回答by Khuzaima Shajapurwala
You can assign to local variable this way.
您可以通过这种方式分配给局部变量。
SELECT @TempVariable = (SELECT replytext + ' ' FROM @t FOR XML PATH(''))
回答by Ace Indy
I used STUFF:
我用过的东西：
SELECT
MyTable.Number AS Number,
(SELECT stuff(
(
select ', ' + x from (SELECT MyOtherTable.Name AS x FROM MyOtherTable WHERE MyOtherTable.ID = MyTable.ID) tb
FOR XML PATH('')
)
, 1, 2, ''))AS AllNames
FROM
MyTable
Result:
结果：
Number: AllNames:
1 Andrew, Carl
2 Bobby, Dave
