SQL存储过程获取所有以前年份记录的串联字符串

debugcn 发表于 Dev

基绍

我有一个带有时间列的表。我正在尝试创建一个存储过程（SQL Server 2008），以便为每行包含前几年所有的上一个值和当前行的值获取一个字符串。例如，如果我有下表。

_________________
   时间值
_________________ 
MAR-2009 1 
_________________ 
MAY-2009 2 
_________________
一月-2010 3 
_________________ 
APR-2011 4 
_________________ 
FEB-2011 5 
_________________
扬2012 6 
_________________

我正在尝试获得以下结果

____________________________________________________
   时间值结果
____________________________________________________ 
MAR-2009 1 “2009,1” 
____________________________________________________ 
MAY-2009 2 “2009,2” 
____________________________________________________
扬-2010 3 “2009,2,2010,3” 
____________________________________________________ 
APR-2011 4“2009,2,2010， 3,2011,4“ 
____________________________________________________ 
2011年2月5” 2009,2,2010,3,2011,5“ 
____________________________________________________ 
jan-2012 6” 2009,2,2010,3,2011,5,2012,6“
____________________________________________________

斯坦

下面是示例，但出于性能目的，最好对原始表进行一些更改。

-- first we need to extract months/years to get comparable and sortable values
-- otherwise we can't select "all previous years" and "last value".
;with converted as (
    select
        time,
        right(time, 4) as year,
        datepart(m, '2000-' + left(time, 3) + '-01') as month,
        right(time, 4) + ',' + convert(varchar(16), value) as concatenated,
        value
    from
        YourTableName --TODO: Replace this with your table name
)
select
    time,
    value,
    -- using xml for string concatenation
    isnull((
        select
            prev.concatenated + ',' as 'text()'
        from
            converted as prev
        where
            prev.year < main.year
            and prev.month = (select max(month) from converted lookup where lookup.year = prev.year)
        for
            xml path('')
    ),'')
    + main.concatenated
from
    converted as main

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。