首页 新闻 赞助 找找看

SQL查询

0
[已关闭问题] 关闭于 2009-12-21 08:32

有一表:

StudentNames           TeacherName

Sa1,Sb1,Sc1       Ta

Sa2, Sb2            Tb

需要得到的结果是:

StudentName    TeacherName

Sa1          Ta

Sb1          Ta

Sc1          Ta

Sa2          Tb

Sb2          Tb

这样的SQL语句怎么写?最好可以不用零时表!

沈啣结的主页 沈啣结 | 初学一级 | 园豆:0
提问于:2009-12-11 13:56
< >
分享
所有回答(1)
0
代码
IF OBJECT_ID('student') IS NOT NULL
DROP TABLE student
GO
CREATE TABLE student(studentname varchar(100),teachername varchar(10))
go
insert into student
select 'Sa1,Sb1,Sc1','ta' union all
select 'Sa2,Sb2','tb'
go
SELECT TOP 1000 id = IDENTITY(int, 1, 1) INTO a FROM syscolumns a, syscolumns b

SELECT SUBSTRING(A.studentname, B.id, CHARINDEX(',', A.studentname + ',', B.id) - B.id) as value,teachername
FROM student A, a B
WHERE SUBSTRING(',' + A.studentname, B.id, 1) = ','
order by teachername
江峰 | 园豆:178 (初学一级) | 2009-12-12 14:44
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册