使用CTE在CROSS APPLY中拆分结果 [英] Using a CTE to split results across a CROSS APPLY
问题描述
我有一些数据需要以包含标记标签的行的形式输出，这是我在一个表值函数内所做的。
I have some data that I need to output as rows containing markup tags, which I'm doing inside a table valued function.
此功能一直很好使用以下格式的代码，使用 search 查询收集我的数据，然后使用 results的输出将其插入返回的表中。
This has been working fine up to a point using code in the format below, using the search query to gather my data, and then inserting into my returned table using the output from results.
我现在需要使用更长的数据字段并将其拆分为多行，而我处于
I now need to take a longer data field and split it up over a number of rows, and I'm at something of a loss as to how to achieve this.
我最初的想法是想使用CTE处理查询中的数据，但看不到从我的搜索查询获取数据到我的CTE以及从那里到我的结果集的方法。
I started with the idea that I wanted to use a CTE to process the data from my query, but I can't see a way to get the data from my search query into my CTE and from there into my results set.
我想我可以通过在数据库中创建另一个表值函数（如果我输入 comment_text ）返回结果集来看到另一种方法， code>列，但这样做似乎很浪费。
I guess I can see an alternative way of doing this by creating another table valued function in the database that returns a results set if I feed it my comment_text column, but it seems like a waste to do it that way.
有人看到过通往解决方案的路线吗？
Does anyone see a route through to a solution?
示例真实表：
DECLARE @Comments TABLE
(
id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
comment_date DATETIME NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
comment_title VARCHAR(50) NOT NULL,
comment_text char(500)
);
添加评论行：
INSERT INTO @Comments VALUES(CURRENT_TIMESTAMP, 'Bob', 'Example','Bob''s Comment', 'Text of Bob''s comment.');
INSERT INTO @Comments VALUES(CURRENT_TIMESTAMP, 'Alice', 'Example','Alice''s Comment', 'Text of Alice''s comment that is much longer and will need to be split over multiple rows.');
返回结果表的格式：
DECLARE @return_table TABLE
(
comment_date DATETIME,
commenter_name VARCHAR(101),
markup VARCHAR(100)
);
天真查询（由于变量 comment_text
Naive query (Can't run because the variable comment_text in the SplitComment CTE can't be identified.
WITH SplitComment(note,start_idx) AS
(
SELECT '<Note>'+SUBSTRING(comment_text,0,50)+'</Note>', 0
UNION ALL
SELECT '<Text>'+SUBSTRING(note,start_idx,50)+'</Text>', start_idx+50 FROM SplitComment WHERE (start_idx+50) < LEN(note)
)
INSERT INTO @return_table
SELECT results.* FROM
(
SELECT
comment_date,
CAST(first_name+' '+last_name AS VARCHAR(101)) commenter,
comment_title,
comment_text
FROM @Comments
) AS search
CROSS APPLY
(
SELECT comment_date, commenter, '<title>'+comment_title+'</title>' markup
UNION ALL SELECT comment_date, commenter, SplitComment
) AS results;
SELECT * FROM @return_table;
R结果（在没有CTE的情况下运行该函数）：
Results (when the function is run without the CTE):
comment_date commenter_name markup
2017-07-07 11:53:57.240 Bob Example <title>Bob's Comment</title>
2017-07-07 11:53:57.240 Alice Example <title>Alice's Comment</title>
理想情况下，我想再增加一行以供Bob的评论，以及两行以供Alice的评论使用。像这样的东西：
Ideally, I'd like to get one additional row for Bob's comment, and two rows for Alice's comment. Something like this:
comment_date commenter_name markup
2017-07-07 11:53:57.240 Bob Example <title>Bob's Comment</title>
2017-07-07 11:53:57.240 Bob Example <Note>Bob's Comment</Note>
2017-07-07 11:53:57.240 Alice Example <title>Alice's Comment</title>
2017-07-07 11:53:57.240 Alice Example <Note>Text of Alice''s comment that is much longer and w</Note>
2017-07-07 11:53:57.240 Alice Example <Text>ill need to be split over multiple rows.</Text>
推荐答案
也许您正在寻找这样的东西（它一个简化的版本，我只使用名字和comment_date作为标识符）。
我使用此数据对其进行了测试，并且-暂时-对最大len 50进行成像以拆分文本列。
提示：将comment_text数据类型更改为VARCHAR（500）
May be you are looking for something like this (it' a simplified version, I used only first name and comment_date as "identifier"). I tested it using this data and - for the moment - imaging max len 50 to split text column. Tip: change comment_text datatype to VARCHAR(500)
DECLARE @Comments TABLE
(
id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
comment_date DATETIME NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
comment_title VARCHAR(50) NOT NULL,
comment_text VARCHAR(500)
);
INSERT INTO @Comments VALUES(CURRENT_TIMESTAMP, 'Bob', 'Example','Bob''s Comment', 'Text of Bob''s comment.');
INSERT INTO @Comments VALUES(CURRENT_TIMESTAMP, 'Alice', 'Example','Alice''s Comment'
, 'Text of Alice''s comment that is much longer and will need to be split over multiple rows aaaaaa bbbbbb cccccc ddddddddddd eeeeeeeeeeee fffffffffffff ggggggggggggg.');
WITH CTE AS (SELECT comment_date, first_name, '<Note>'+CAST( SUBSTRING(comment_text, 1, 50) AS VARCHAR(500)) +'</Note>'comment_text, 1 AS RN
FROM @Comments
UNION ALL
SELECT A.comment_date, A.first_name, '<Text>'+CAST( SUBSTRING(A.comment_text, B.RN*50+1, 50) AS VARCHAR(500)) +'</Text>'AS comment_text, B.RN+1 AS RN
FROM @Comments A
INNER JOIN CTE B ON A.comment_date=B.comment_date AND A.first_name=B.first_name
WHERE LEN(A.comment_text) > B.RN*50+1
)
SELECT A.comment_date, A.first_name, '<title>'+ comment_title+'</title>' AS markup
FROM @Comments A
UNION ALL
SELECT B.comment_date, B.first_name, B.comment_text AS markup
FROM CTE B ;
输出：
comment_date first_name markup
2017-07-07 14:30:51.117 Bob <title>Bob's Comment</title>
2017-07-07 14:30:51.117 Alice <title>Alice's Comment</title>
2017-07-07 14:30:51.117 Bob <Note>Text of Bob's comment.</Note>
2017-07-07 14:30:51.117 Alice <Note>Text of Alice's comment that is much longer and wi</Note>
2017-07-07 14:30:51.117 Alice <Text>ll need to be split over multiple rows aaaaaa bbbb</Text>
2017-07-07 14:30:51.117 Alice <Text>bb cccccc ddddddddddd eeeeeeeeeeee fffffffffffff g</Text>
2017-07-07 14:30:51.117 Alice <Text>gggggggggggg.</Text>
这篇关于使用CTE在CROSS APPLY中拆分结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
