tsql - T-SQL - 从一组四行的选择中将新行插入表中

Question

我正在记录用户在我的 Web 应用程序上选择的过滤器，并且需要创建一个新行来显示他们选择的过滤器组。

由于过滤器在表中记录的方式，它们以四个一组的形式出现，如下面的屏幕截图所示：

基于这些四组，我需要在 : 符号之后的值的 Message 字段中插入一个包含逗号分隔字符串的新行。

标签： tsqliterationsql-insert

Answer 1

似乎没有将四行组合在一起的标识符（在您的示例中看不到完整的时间戳）。这是一个基于 mod 4 和递归 cte 计算的分组列的示例

DECLARE @tab TABLE (id int identity(1,1), msg varchar(100))

INSERT INTO @tab
VALUES
 ('selected: a')
,('selected: b')
,('selected: c')
,('selected: d')
,('selected: e')
,('selected: f')
,('selected: g')
,('selected: h')
,('selected: i')
,('selected: j')
,('selected: k')
,('selected: l')

;with src --add grouping column
as
(
SELECT 
    id
    ,msg = left(msg, charindex(':', msg, 1)-1)
    ,val = LTRIM(SUBSTRING(msg, CHARINDEX(':', msg, 1)+1, len(msg) ))
    ,nid = ROW_NUMBER() over (partition by id % 4 order by id)  
FROM @tab
)
,source --add column for recursion
as
(
    SELECT 
        *,
        rn = ROW_NUMBER() OVER (PARTITION BY nid ORDER BY id)
    FROM src
)
,rcdef -- define recursive cte
AS (
    SELECT 
        nid
        ,msg
        ,val = convert(varchar(max), val)
        ,rn = 1 
    FROM source 
    WHERE rn = 1
    UNION ALL 
    SELECT 
        rcdef.nid
        , rcdef.msg
        ,val = convert(varchar(max), rcdef.val +',' + source.val) --concatenate vals
        ,rn = rcdef.rn + 1
    FROM rcdef
        INNER JOIN source on source.nid = rcdef.nid  and source.rn = rcdef.rn + 1
)
SELECT 
    nid, 
    val = msg + ': ' + max(val)
FROM rcdef
GROUP BY nid, msg