将多个html行合并为一个 [英] Combine multiple html rows into one

问题描述

我使用的是 SQL Server 2012，我有以下两个表

I am using SQL Server 2012 and I have the following two tables

博客

Id Title
1  My Blog Title

博客内容

Id Blogid Content
1    1   <p>My First Paragraph</p>
2    1   <p>My Second Paragraph</p>

每个博客条目可能有多个内容条目.这是一个包含 HTML 内容的 varchar 字段.我需要选择一个博客并将其所有内容组合在一起.

Each Blog Entry may have multiple Content entries. This is a varchar field that contains HTML content. I need to select a blog and all of its content combined together.

这是我尝试过的:

SELECT B.Id, B.Title, 
STUFF(( SELECT '' + BC.Content 
        FROM BlogContent BC 
        WHERE B.Id = BC.Blogid
        ORDER BY BC.Id ASC OFFSET 0 ROWS
        FOR XML PATH('')),1,0,'') AS Content
FROM Blog B
WHERE B.Id = 1
ORDER BY B.PublishDate DESC

它几乎奏效了，这是我的结果:

And it almost worked, this is my result:

Id   Title             Content
1    My Blog Title     <p>This is the first Message</p><p>This is the second Message</p>

我对上面代码的问题是一切都变成了 HTML 编码，我认为这是因为 FOR XML.如何在不添加此部分的情况下实现组合?如果我不添加它，则会出现错误.

My problem with the code above is that everything became HTML Encoded, I assume its because of FOR XML. How can I achieve combining without adding this part? If I don't add it then I get an error.

子查询返回了 1 个以上的值.当子查询跟在 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

我怎样才能得到以下结果:

How can I get the following result:

Id   Title             Content
1    My Blog Title     <p>This is the first Message</p><p>This is the second Message</p>

推荐答案

假设 BlogContent 中的 ID 是正确的顺序.

Assuming ID in BlogContent is the proper sequence.

示例

Declare @Blog table (ID int, Title varchar(max))
Insert Into @Blog values
(1,'My Blog Title')

Declare @BlogContent table (ID int,Blogid int, Content varchar(max))
Insert Into @BlogContent values
(1,1,'<p>My First Paragraph</p>'),
(2,1,'<p>My Second Paragraph</p>')


Select A.*
      ,Content  = Stuff((Select '' +Content 
                           From  @BlogContent 
                           Where Blogid=A.ID 
                           Order by ID 
                           For XML Path(''),TYPE).value('(./text())[1]','varchar(max)')
                        ,1,0,'') 
 From @Blog A

退货

ID  Title           Content
1   My Blog Title   <p>My First Paragraph</p><p>My Second Paragraph</p>

这篇关于将多个html行合并为一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！