SQL语言艺术 第11章 精于计谋：挽救响应时间 复杂字段的解析
测试数据
create table movies --电影表
(movie_id int, --电影ID
actors varchar(50)) --演员ID
go
insert into movies
values
(1,'123,456,78,96') ,
(2,'23,67,97') ,
(3,'67,456')
查询语句需用到枢轴表 SQL语言艺术 第11章 精于计谋：挽救响应时间 枢轴表
该测试表的问题在于把需要处理的多项数据(演员ID)一个接一个的保存在单一字段内，数据之间以分隔符分开。而用户想看到的效果是抽取actors字段的不同的组成部分，然后以单独的形式依次返回这些不同部分。
最终结果
movie_id actorid
----------- ----------------------------------------------------
1 123
1 456
1 78
1 96
2 23
2 67
2 97
3 67
3 456
(9 row(s) affected)
查询语句
方法一
select movie_id,
substring(
actors,
case id
when 1 then 1
else id + 1
end,
case next_sep
when 1 then len(actors)
else case id
when 1 then next_sep - 1
else next_sep - 2
end
end
) as actorid
from
(select id,movie_id,actors,
charindex(',',substring(actors,id ,len(actors))) first_sep, --分隔符第一次出现的位置
charindex(',',substring(actors,id + 1,len(actors))) + 1 next_sep --将要分离的演员ID的长度(包括分隔符)
from movies,# where id <= 50
and (id = 1 or charindex(',',substring(actors,id,len(actors))) = 1)) as a
子查询结果
id movie_id actors first_sep next_sep
----------- ----------- -------------------------------------------------- ----------- -----------
1 1 123,456,78,96 4 4
4 1 123,456,78,96 1 5
8 1 123,456,78,96 1 4
11 1 123,456,78,96 1 1
1 2 23,67,97 3 3
3 2 23,67,97 1 4
6 2 23,67,97 1 1
1 3 67,456 3 3
3 3 67,456 1 1
(9 row(s) affected)
方法二 在字段两端添加分隔符
本方法比第一个方法好在外部查询时不用分情况取值
select movie_id,substring(actor_id,id + 1,next_sep - 2) as actorid
from
(select id,movie_id,',' + actors + ',' as actor_id,
charindex(',',substring(',' + actors + ',',id ,len(',' + actors + ','))) first_sep, --分隔符第一次出现的位置
charindex(',',substring(',' + actors + ',',id + 1,len(',' + actors + ','))) + 1 next_sep --将要分离的演员ID的长度(包括2个分隔符）
from movies,# where id <= 50
and charindex(',',substring(',' + actors + ',',id ,len(',' + actors + ','))) = 1
and charindex(',',substring(',' + actors + ',',id + 1,len(',' + actors + ','))) + 1 > 1 ) as a
子查询结果
id movie_id actor_id first_sep next_sep
----------- ----------- ---------------------------------------------------- ----------- -----------
1 1 ,123,456,78,96, 1 5
5 1 ,123,456,78,96, 1 5
9 1 ,123,456,78,96, 1 4
12 1 ,123,456,78,96, 1 4
1 2 ,23,67,97, 1 4
4 2 ,23,67,97, 1 4
7 2 ,23,67,97, 1 4
1 3 ,67,456, 1 4
4 3 ,67,456, 1 5
(9 row(s) affected)
