sql server遍历表不用游标和临时表的方法
表结果如图
CREATE TABLE T(id INT,NAME VARCHAR(50)) INSERT INTO T SELECT 10001,'Sky,Blue,Water' INSERT INTO T SELECT 10002,'Book,Apple,Shirt' INSERT INTO T SELECT 10005,'Cup,Yellow,org' INSERT INTO T SELECT 61245,'box,phone,paper' GO SELECT id,SUBSTRING(A.NAME,B.number,CHARINDEX(',',a.NAME+',',b.number)-b.number) AS [name] FROM T AS A JOIN master.dbo.spt_values AS B ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.NAME) AND SUBSTRING(','+A.NAME,b.number,1)=','
查询结果如下
乌龟才背着房子过一辈子