sql server遍历表一般都需要用到游标和临时表，不过下面介绍的例子却不用游标和临时表就可以实现sql server遍历表，让我们一起来了解一下.
表结构如下
id name
---------------------------------------
10001 sky blue water
10002 book apple shirt
10005 cup yellow org
61245 box phone paper
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)=' '
/*
id name
----------- --------------------------------------------------
10001 sky
10001 blue
10001 water
10002 book
10002 apple
10002 shirt
10005 cup
10005 yellow
10005 org
61245 box
61245 phone
61245 paper
*/
Drop table T
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
以上就是sql server遍历表不用游标和临时表的方法介绍。
【编辑推荐】
