T-SQL 挑战62

T-SQL 挑战62

题目：

http://beyondrelational.com/puzzles/challenges/96/break-a-long-sentence-in-groups-of-5-words.aspx

数据：

id comment
-- ---------------------------------------------------------------------
1  The upcoming release of SQL Server is codenamed Denali. It is 
   also called SQL11 which refers to version 11 and peple misunderstand 
   it to be SQL Server 2011.
2  SQL Server Denali CTP 3 introduced a number of TSQL enhancements. 

希望的结果：

id sentence group text
-- -------- ----- -----------------------------------
1         1     1 The upcoming release of SQL
1         1     2 Server is codenamed Denali
1         2     1 It is also called SQL11
1         2     2 which refers to version 11
1         2     3 and people misunderstand it to
1         2     4 be SQL Server 2011
2         1     1 SQL Server Denali CTP 3
2         1     2 introduced a number of TSQL
2         1     3 enhancements

方法一：

 
USE master 

IF OBJECT_ID('TC62', 'U') IS NOT NULL 
BEGIN
DROP TABLE TC62
END
GO

CREATE TABLE TC62
    (
      id INT IDENTITY ,
      comment VARCHAR(MAX)
    )
GO

INSERT INTO TC62
        ( comment
        )

SELECT  'The upcoming release of SQL Server is codenamed Denali. It is also called SQL11 which refers to version 11 and peple misunderstand it to be SQL Server 2011.'
UNION ALL
SELECT  'SQL Server Denali CTP 3 introduced a number of TSQL enhancements.' 




 ;WITH   cte
AS ( SELECT   id ,
SUBSTRING(comment, 1, CHARINDEX('.', comment)) AS sentence ,
CHARINDEX('.', comment) AS start ,
1 AS lvl
FROM     TC62
UNION ALL
SELECT   a.id ,
SUBSTRING(b.comment, start + 1,
CHARINDEX('.', b.comment, start + 1) - start
+ 1) AS sentence ,
CHARINDEX('.', b.comment, start + 1) AS start ,
                        lvl + 1
FROM     cte a ,
                        TC62 b
WHERE    a.id = b.id
AND CHARINDEX('.', b.comment, start + 1) = LEN(b.comment)
             ),
        cte2
AS ( SELECT   id ,
                        lvl ,
LTRIM(REPLACE(sentence, '.', '')) + ' ' AS comment
FROM     cte
             ),
        cte3
AS ( SELECT   id ,
                        lvl ,
SUBSTRING(comment, 1, CHARINDEX(' ', comment, 1)) AS word ,
CHARINDEX(' ', comment) AS beginIdex ,
1 AS wordlvl
FROM     cte2
UNION ALL
SELECT   a.id ,
                        a.lvl ,
SUBSTRING(b.comment, beginIdex + 1,
CHARINDEX(' ', b.comment, beginIdex + 1)
- beginIdex - 1) AS word ,
CHARINDEX(' ', b.comment, beginIdex + 1) AS beginIdex ,
                        wordlvl + 1 AS wordlvl
FROM     cte3 a ,
                        cte2 b
WHERE    a.id = b.id
AND a.lvl = b.lvl
AND beginIdex + 1 <= LEN(b.comment)
             ),
        cte4
AS ( SELECT   id ,
                        lvl ,
                        word ,
                        grp
FROM     cte3 a
CROSS APPLY ( SELECT    ( a.wordlvl - 1 ) / 5 + 1 AS grp
FROM      cte3 b
WHERE     a.id = b.id
AND a.lvl = b.lvl
AND a.wordlvl = b.wordlvl
                                    ) c
             )
SELECT  DISTINCT
            b.id ,
            b.lvl ,
            b.grp ,
            ( SELECT    ' ' + word
FROM      cte4 a
WHERE     a.id = b.id
AND a.lvl = b.lvl
AND a.grp = b.grp
ORDER BY  id ,
                        lvl ,
                        grp
FOR
              XML PATH('')
            ) sen
FROM    cte4 b
ORDER BY id ,
            lvl ,
            grp



/*
    results:
id    lvl    grp    sen
1    1    1     The  upcoming release of SQL
1    1    2     Server is codenamed Denali
1    2    1     It  is also called SQL11
1    2    2     which refers to version 11
1    2    3     and peple misunderstand it to
1    2    4     be SQL Server 2011
2    1    1     SQL  Server Denali CTP 3
2    1    2     introduced a number of TSQL
2    1    3     enhancements

*/ 

posted on 2011-08-17 11:38  Henry.Lau 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/Henry1225/archive/2011/08/17/2142908.html