时间戳

首页 > 解决方案 > 将所有以前的列合并到当前列

sql - 将所有以前的列合并到当前列

问题描述

我有一个名为 terms 的表，我正在使用 SQL Server 2014

termID   termname
  1       term1
  2       term2
  3       term3
  4       term4

我想要这样的结果

 termID   termname
  1         term1
  2         term1 and term2
  3         term1 and term2 and term3
  4         term1 and term2 and term3 and term4

我已经使用 完成了此操作LAG()，但我只能获得前一个值。我需要获取所有以前的列值。

 select termid, CASE WHEN ISNULL(prev_word,'')<>'' THEN prev_word+' and '+ termname ELSE termname END
from (
    select termID, 
           lag(termname) over (order by termID) as prev_word,
           termname
    from terms
) as t

标签： sqlsql-serverstringwindow-functionsrecursive-query

解决方案

一些数据库（例如 Postgres）支持string_agg()作为窗口函数，它允许您将其编写为：

select
    temid,
    string_agg(termname, ' and ') over(order by termid) termname
from terms

...不幸的是，SQL Server 不支持这种语法（还没有？）。

另一种方法是递归查询。我不会假设termid总是从 开始1并且总是在没有间隙的情况下递增，所以首先用 对行重新编号row_number()，然后迭代地遍历数据集，逐步累积字符串：

with 
    data as (
        select t.*, row_number() over(order by termid) rn from terms t
    ),
    cte as (
        select termid, cast(termname as varchar(max)) termname, rn
        from data 
        where rn = 1
        union all
        select d.termid, c.termname + ' and ' + d.termname, d.rn
        from cte c
        inner join data d on d.rn = c.rn + 1
    )
select termid, termname from cte order by termid

如果列表中有超过 100 个值，则需要option (maxrecursion 0)在查询的最后添加（否则它将达到 100 次迭代的默认限制）。

DB Fiddle 上的演示：

白蚁 | 术语名                           
-----: | :------------------------------------
     1 | 学期1                              
     2 | 学期 1 和学期 2                    
     3 | term1 和 term2 和 term3          
     4 | term1 和 term2 以及 term3 和 term4

推荐阅读