一、前言:
处理一对多关系,有两种方式
(1)创建关系表,将对应关系保存在物理表中。
(2)表中添加一个字段,将多关系的值以特殊符号隔开进行保存.
本例使用的就是,以逗号隔开(InterestID='1,2,3,4')
二、示例语句
1、测试数据表的创建语句
--学生表
CREATE table Student
(
Name nvarchar(50),--姓名
InterestID varchar(50)--兴趣编号
)
--基础信息表
CREATE table BaseInfo
(
InterestID int,--兴趣编号
InterestName varchar(10)--兴趣名称
)
--测试数据
insert INTO BaseInfo
SELECT 1,'乒乓球'
UNION
SELECT 2,'篮球'
UNION
SELECT 3,'足球'
UNION
SELECT 4,'网球'
UNION
SELECT 5,'羽毛球'
--一对多关系示例数据
INSERT INTO Student
SELECT '李健康','1,3,4'
UNION
SELECT '方时赫','2'
--一对一关系示例数据
INSERT INTO Student
SELECT 'Mike','1'
UNION
SELECT 'Mike','2'
UNION
SELECT 'Mike','4'
2、查询方式
(1)字段中保存多个值(一条记录对应多条记录),关联仍为一条数据
SQL语句:
SELECT a.Name,a.InterestID
,InterestName=
STUFF
(
(
SELECT ','+LTRIM(b.InterestName)
FROM BaseInfo b
WHERE CHARINDEX(','+LTRIM(b.InterestID),','+a.InterestID)>0 FOR XML PATH('')
)
,1,1,''
)
FROM Student a
原结果集:
Name InterestID
李健康 1,3,4
方时赫 2
关联结果集:
Name InterestID InterestName
李健康 1,3,4 乒乓球,足球,网球
方时赫 2 篮球
(2)字段中保存多个值(一条记录对应多条记录),关联为多条数据
SQL语句:
SELECT x.Name,y.InterestID FROM
(
SELECT Name
,InterestID = CONVERT(xml,'<root><v>' + REPLACE(InterestID, ',', '</v><v>') + '</v></root>')
FROM Student
) x
OUTER APPLY
(
SELECT InterestID = N.v.value('.', 'varchar(100)') FROM x.InterestID.nodes('/root/v') N(v)
) y
原结果集:
Name InterestID
李健康 1,3,4
方时赫 2
关联结果集:
Name InterestID
李健康 1
李健康 3
李健康 4
方时赫 2
(3)字段中保存一个值,关联为一对多的关系
SQL语句:
select Name,
stuff
(
(
select ','+ InterestID from Student as b where b.Name = a.Name for xml path('')
)
,1,1,''
) as InterestID
from Student as a
group by Name
原结果集:
Name InterestID
李健康 1,3,4
方时赫 2
Mike 1
Mike 2
Mike 4
关联结果集:
Name InterestID
Mike 1,2,4
方时赫 2
李健康 1,3,4