这个语句怎么写呢？

[sxssg]

表结构如下
id value class
1 3,2,4,5 no
2 5,1,1+2 yes
将value列按逗号拆分后加一列code为排序，结果如下
id value class code
1 3 no 1
1 2 no 2
1 4 no 3
1 5 no 4
2 5 yes 1
2 1 yes 2
2 1+2 yes 3

[ruixin3212]

create table TB_1 (ID INT ,VALUE VARCHAR(10),CLASS VARCHAR(10))
GO
INSERT INTO TB_1 VALUES (1,'3,2,4,5,','NO')
GO
INSERT INTO TB_1 VALUES (2,'5,1,1+2,','YES')
GO
SELECT * FROM TB_1

WITH TB_2 AS (
SELECT ID,VALUE ,CLASS,CHARINDEX(',',VALUE) STA,CHARINDEX(',',VALUE)-1 LENS FROM TB_1  
UNION ALL
SELECT ID,VALUE ,CLASS,CHARINDEX(',',VALUE,STA+1) STA,CHARINDEX(',',VALUE,STA+1)-STA-1 LENS FROM TB_2 WHERE STA<>0)
--SELECT * FROM TB_2

SELECT ID,SUBSTRING(VALUE,STA-LENS,LENS),class FROM TB_2
WHERE STA<>0

[sxssg]

引用 1 楼 ruixin3212 的回复:

create table TB_1 (ID INT ,VALUE VARCHAR(10),CLASS VARCHAR(10))
GO
INSERT INTO TB_1 VALUES (1,'3,2,4,5,','NO')
GO
INSERT INTO TB_1 VALUES (2,'5,1,1+2,','YES')
GO
SELECT * FROM TB_1

WITH TB_2 AS (
SELECT ID,VALUE ,CLASS,CHARINDEX(',',VALUE) STA,CHARINDEX(',',VALUE)-1 LENS FROM TB_1  
UNION ALL
SELECT ID,VALUE ,CLASS,CHARINDEX(',',VALUE,STA+1) STA,CHARINDEX(',',VALUE,STA+1)-STA-1 LENS FROM TB_2 WHERE STA<>0)
--SELECT * FROM TB_2

SELECT ID,SUBSTRING(VALUE,STA-LENS,LENS),class FROM TB_2
WHERE STA<>0

谢谢， code可以也加上吗？

[縱頭開始]

[learningcoder]

--创建表结构
IF(OBJECT_ID('TA') IS NOT NULL) DROP TABLE TA
CREATE TABLE TA(id INT,[value] VARCHAR(100), class VARCHAR(10))
INSERT INTO TA(id,[value],class)
SELECT 1,'3,2,4,5','no' UNION ALL
SELECT 2,'5,1,1+2','yes'  
--查询结果
SELECT T.id
  ,SUBSTRING(T.[value],R.number,CHARINDEX(',',T.[value]+',',R.number)-R.number) AS value
  ,T.class 
  ,ROW_NUMBER() OVER (PARTITION BY T.id ORDER BY T.id) AS code
FROM TA AS T
JOIN (
	SELECT TOP 10 number FROM  [master].dbo.spt_values  
	WHERE [type]='P' ORDER BY number)AS R ON (CHARINDEX(',',','+T.[value],R.number)=R.number)
--运行结果
id      value     class      code
----------- ---------------------------- ---------- --------------------
1        3         no         1
1        2         no         2
1        4         no         3
1        5         no         4
2        5         yes        1
2        1         yes        2
2        1+2       yes        3

(7 行受影响)

[longai123]

if you like me then i like you toghter