谁能给我解释一下下面这段sql代码的意思
WITH roy
AS ( SELECT a,
b = CAST(LEFT(b, CHARINDEX(',',b + ',') - 1) AS NVARCHAR(max)) ,
Split = CAST(STUFFb+ ',', 1,
CHARINDEX(',',b+ ','), '') AS NVARCHAR(max))
FROM c_tb
UNION ALL
SELECT a,
b= CAST(LEFT(Split, CHARINDEX(',', Split) - 1) AS NVARCHAR(max)) ,
Split = CAST(STUFF(Split, 1, CHARINDEX(',', Split), '') AS NVARCHAR(max))
FROM Roy
WHERE split > ''
)
a,b是表c_tb中的两个字段，b里面的数据都是用逗号隔开的，“1，2，3”这种，现在想要把逗号分开，形成多条记录。上面的代码可以解决这个问题，但我不是很明白意思，求高手具体解释一下。
[解决办法]
SELECT a,
b = CAST(LEFT(b, CHARINDEX(',',b + ',') - 1) AS NVARCHAR(max)) ,
Split = CAST(STUFFb+ ',', 1,
CHARINDEX(',',b+ ','), '') AS NVARCHAR(max))
FROM c_tb
这是递归初值,获取逗号前面的数据和截去前面的字符后剩下的字符.
SELECT a,
b= CAST(LEFT(Split, CHARINDEX(',', Split) - 1) AS NVARCHAR(max)) ,
Split = CAST(STUFF(Split, 1, CHARINDEX(',', Split), '') AS NVARCHAR(max))
FROM Roy
WHERE split > ''
对递归中的后一个字符串再截取逗号前的字符,直到剩下的字符串为空串.
