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语句怎么写？最好可以不用零时表！
所有回答(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