--lz 参考一下拆分列值部分。 drop table tb 拆分列值
有表tb, 如下:
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','DROP TABLE #2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)BDROP TABLE tb/*
id value
----------- ------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc(5 行受影响)
*/
有表tb, 如下:
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','DROP TABLE #2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)BDROP TABLE tb/*
id value
----------- ------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc(5 行受影响)
*/
INSERT INTO R_TeacherClass(teacherId,classId)
SELECT t1.classId,t2.teacherId FROM class t1
INNER JOIN teacher t2 ON CHARINDEX(','+t2.teacherId+',',','+t1.teacher+',')>0
IF OBJECT_ID('R_TeacherClass')IS NOT NULL
DROP TABLE R_TeacherClass
GO
--开始创建R_TeacherClass
SELECT
T2.id teacherId
,T1.id classId
INTO R_TeacherClass
FROM
class T1
LEFT JOIN teacher T2 ON ','+T1.teacher+','LIKE'%,'+CAST(T2.id AS VARCHAR(10))+',%'
--查看结果
SELECT * FROM R_TeacherClass
sql语句格式很好 不过Left Join应为Inner Join ,中间表的任何一个关联表id为null都是没意义的两个语句都是join连接 不过on后不是两个表的字段相等我还是头一次见 让我长见识了 最后两个语句的查询结果一致一楼也让我学习到了列值拆分这个名词
但是执行是正确的
不过有人测试过了http://bbs.csdn.net/topics/370065385#post-372209977
http://q.cnblogs.com/q/55678/
两个例子开始都在GetHashCode()出了问题