SQL計(jì)算字符串中最大的遞增子序列的方法

2020-10-30 19:05:46

字體：大中小

來(lái)源：轉(zhuǎn)載

供稿：網(wǎng)友

求字符串中最大的遞增子序列

數(shù)據(jù)庫(kù)環(huán)境：SQL SERVER 2005

如題，求字符串“abcbklmnodfghijkmer”中最大的遞增子序列。這個(gè)字符串有點(diǎn)特別，

只由26個(gè)小寫字母a-z組成。

大概思路如下：

1.將字符串轉(zhuǎn)到一列存儲(chǔ)，并生成行號(hào)

2.設(shè)置一個(gè)遞增計(jì)數(shù)器列，默認(rèn)為1，比較上下行的字符，如果在字典中的順序是遞增，

則計(jì)數(shù)器加1,否則，計(jì)數(shù)器置1

3.找出計(jì)數(shù)器最大的數(shù)及對(duì)應(yīng)的行號(hào)，根據(jù)這2個(gè)數(shù)截取字符串

思路有了，下面直接貼代碼

DECLARE @vtext VARCHAR(255)SET @vtext = 'abcbklmnodfghijkmer'/*講字符串轉(zhuǎn)成一列存儲(chǔ)，并生成行號(hào)*/WITH x0   AS ( SELECT number AS id ,      SUBSTRING(@vtext, number, 1) AS letter    FROM  master.dbo.spt_values    WHERE type = 'P'      AND number <= LEN(@vtext)      AND number >= 1    ),/*實(shí)現(xiàn)計(jì)數(shù)器*/  x1 ( id, letter, clen )   AS ( SELECT id ,      letter ,      1 AS clen    FROM  x0    WHERE id = 1    UNION ALL    SELECT x0.id ,      x0.letter ,      CASE WHEN x1.letter <= x0.letter THEN x1.clen + 1        ELSE 1      END AS clen    FROM  x0 ,      x1    WHERE x0.id = x1.id + 1    ) /*截取字符串*/ SELECT SUBSTRING(@vtext, start, sublen) AS 最大子序列 FROM ( SELECT id ,      clen ,      MAX(clen) OVER ( ) AS maxclen ,      id - MAX(clen) OVER ( ) + 1 AS start ,      MAX(clen) OVER ( ) AS sublen    FROM  x1   ) t WHERE clen = maxclen

求出的最大子序列是