我们经常会遇到想要把一对多关系转换成为一对一关系，以方便显示。例如有如下关系：
Class(ClassID,ClassName)
和
Student(SID,SName,ClassID)
，并且，这两个关系存在以下测试数据：
Class:
|
001 |
语文 |
|
002 |
数学 |
Student ：
|
031231301 |
张三 |
001 |
|
031231301 |
张三 |
002 |
|
031231302 |
李四 |
001 |
那么，这两个关系表达的意思：选语文的有张三和李四；选数学的有李四。如果想做一个视图（
V_STU_CLA
）来表达这种一对多关系（一门课程，被多个学生所选择），可以使用一个简单的左联语句来完成：
得到的结果如下：
SELECT
C.ClassID,C.ClassName,S.SName
FROM
ClassC
LEFT
JOIN
StudentS
ON
C.ClassID
=
S.ClassID;
|
ClassID |
ClassName |
SName |
|
001 |
语文 |
张三 |
|
001 |
语文 |
李四 |
|
002 |
数学 |
张三 |
这样虽然能够清晰的表达选课关系，但是，某些情况下，它不如下面这种形式来得一目了然：
|
ClassID |
ClassName |
SNames |
|
001 |
语文 |
张三 , 李四 |
|
002 |
数学 |
张三 |
要达到这样的目的，需要完成一个一对多关系到一对一关系的转换。这样的转换，在数据库中，可以借助函数来进行，因为函数中应用到了游标，故对于 Oracle 和 MSSQL 稍有不同，附上两个版本的函数 SQL 代码：
MS-SQL
版：
MS-SQL
调用时，通过以下语句实现：
ORACLE
中调用方法类似。
SELECT
C.ClassID,C.ClassName,dbo.f_getStuNamesByClassID(C.ClassID)
FROM
ClassC;
--
根据课程ID，返回选此课程的学生的名字，以逗号隔开
CREATE
function
dbo.f_getStuNamesByClassID(
@ClassID
int
)
RETURNS
nvarchar
(
512
)
begin
declare
@Result
nvarchar
(
512
);
declare
@stuName
nvarchar
(
256
);
Set
@Result
=
''
;
declare
cur
cursor
for
(
SELECT
S.SName
FROM
ClassC
LEFT
JOIN
StudentS
ON
C.ClassID
=
S.ClassID
WHERE
C.ClassID
=
@ClassID
;
)
open
cur;
fetch
next
from
cur
into
@stuName
;
while
(
@@fetch_status
=
0
)
begin
set
@Result
=
@Result
+
@stuName
+
'
,
'
;
fetch
next
from
cur
into
@stuName
;
end
;
--
去除最后多余的一个逗号
IF
@Result
<>
''
SET
@Result
=
SUBSTRING
(
@Result
,
1
,
LEN
(
@Result
)
-
1
);
ELSE
SET
@Result
=
NULL
;
return
@Result
;
end
ORACLE版：
create
or
replace
function
FUN_GET_STUNAMES_BY_CLASSID(P_CLASSID
IN
VARCHAR2
)
return
varchar2
is
Result
VARCHAR2
(
4000
);
begin
--
通过游标，查找并拼接此课程下的学生姓名
FOR
CUR
IN
(
SELECT
S.SName
FROM
ClassC
LEFT
JOIN
StudentS
ON
C.ClassID
=
S.ClassID
WHERE
C.ClassID
=
@ClassID
;
)
LOOP
Result:
=
Result
||
CUR.SName
||
'
,
'
;
END
LOOP;
--
去掉最后一个逗号
Result:
=
SUBSTR(Result,
0
,LENGTH(Result)
-
1
);
return
(Result);
end
;
