基于多个子表行的 SQL 更新父表字段 [英] SQL Update Parent Table Field based on Multiple Child Table Rows

问题描述

对于 parent/child 关系，我有以下示例表结构.

I have the following sample table structures for a parent / child relationship.

父表

id    LongText
--------------
10    
20    

子表

id    char     value
--------------------
10    COLOR    RED
10    HEIGHT   1FT
20    COLOR    BLUE
20    WIDTH    2FT

我有一个要求，指定子表行中的某些字段需要连接在一起并放置在父表中.如果可能，我想在单个 SQL 查询中完成此操作.我写的更新语句如下.

I have a requirement that specifies that certain fields from the child table rows need to be concatenated together and placed in the parent table. I would like to complete this in a single SQL query if possible. The update statement that I have written is as follows.

UPDATE
  parent
SET
  LongText = COALESCE(LongText, N'')
              + child.char + N': ' + child.val + ','
FROM
  parent INNER JOIN child
    ON
      parent.id = child.id

但我只得到以下结果.

id    LongText
------------------
10    COLOR: RED,
20    COLOR: BLUE,

我希望(或者我应该说我想要)得到这个.

I would expect (or I should say I WANT) to get this.

id    LongText
------------------
10    COLOR: RED,HEIGHT: 1FT
20    COLOR: BLUE,WIDTH: 2FT

这可能吗?关于我如何做到这一点的任何建议?任何帮助表示赞赏！

Is this possible? Any suggestions as to how I can do this? Any help is appreciated!

这是一个 SQLfiddle 供参考.

推荐答案

update parent
set longtext =
    stuff((
    select ',' + c.char + ': ' + c.val
    from child c
    where c.uid = parent.uid
    for xml path(''), type).value('.','nvarchar(max)'),1,1,'');

我已经用解决方案更新了您的 SQLFiddle.

I have updated your SQLFiddle with the solution.

1. STUFF() 函数删除前导 ','(逗号)来自第一个特征.

1. STUFF() function to remove the leading ',' (comma) from the first characteristic.

FOR XML 从查询结果.这是 SQL Server 的一个众所周知的技巧——因为列没有命名，没有元素生成，只有原始文本(每行的)被输出，混合成一行.

FOR XML to create an XML document from a query result. This is a well known trick with SQL Server - because the column is not named, there is no element produced and only the raw text (of each row) is output, mashed together into a single row.

互联网上很少有文章试图详细解释它，因为代码本身几乎就是一种解释.

Very few articles on the internet attempt to explain it in detail, since the code is pretty much left as an explanation in and of itself.

这篇关于基于多个子表行的 SQL 更新父表字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！