题目：
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
*/
