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
以上就是sql server遍历表不用游标和临时表的方法介绍。
【编辑推荐】
