我们经常会遇到想要把一对多关系转换成为一对一关系，以方便显示。例如有如下关系：
Class(ClassID,ClassName)和Student(SID,SName,ClassID)，并且，这两个关系存在以下测试数据：
Class:
Student：
|
031231301
|
张三
|
001
|
|
031231301
|
张三
|
002
|
|
031231302
|
李四
|
001
|
那么，这两个关系表达的意思：选语文的有张三和李四；选数学的有李四。如果想做一个视图（V_STU_CLA）来表达这种一对多关系（一门课程，被多个学生所选择），可以使用一个简单的左联语句来完成：
![[转]SQL函数——将一对多关系转换成一对一关系](/default/index/img?u=aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY= "[转]SQL函数——将一对多关系转换成一对一关系")
SELECT C.ClassID, C.ClassName, S.SName FROM Class C
LEFT JOIN Student S ON C.ClassID=S.ClassID;
得到的结果如下：
|
ClassID
|
ClassName
|
SName
|
|
001
|
语文
|
张三
|
|
001
|
语文
|
李四
|
|
002
|
数学
|
张三
|
这样虽然能够清晰的表达选课关系，但是，某些情况下，它不如下面这种形式来得一目了然：
|
ClassID
|
ClassName
|
SNames
|
|
001
|
语文
|
张三,李四
|
|
002
|
数学
|
张三
|
要达到这样的目的，需要完成一个一对多关系到一对一关系的转换。这样的转换，在数据库中，可以借助函数来进行，因为函数中应用到了游标，故对于Oracle和MSSQL稍有不同，附上两个版本的函数SQL代码：
MS-SQL版：
--根据课程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 Class C
LEFT JOIN Student S 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 Class C
LEFT JOIN Student S 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;
MS-SQL调用时，通过以下语句实现：
SELECT C.ClassID, C.ClassName, dbo.f_getStuNamesByClassID(C.ClassID)
FROM Class C;
ORACLE中调用方法类似。
本文转自Jack Niu博客园博客，原文链接：http://www.cnblogs.com/skywind/archive/2008/04/09/1144676.html，如需转载请自行联系原作者