[转]SQL函数——将一对多关系转换成一对一关系

我们经常会遇到想要把一对多关系转换成为一对一关系，以方便显示。例如有如下关系：

Class(ClassID,ClassName)和Student(SID,SName,ClassID)，并且，这两个关系存在以下测试数据：

Class:

001

语文

002

数学

Student：

031231301

张三

031231302

李四

那么，这两个关系表达的意思：选语文的有张三和李四；选数学的有李四。如果想做一个视图（V_STU_CLA）来表达这种一对多关系（一门课程，被多个学生所选择），可以使用一个简单的左联语句来完成：

SELECT C.ClassID, C.ClassName, S.SName FROM Class C

LEFT JOIN Student S ON C.ClassID=S.ClassID;

得到的结果如下：

ClassID

ClassName

SName

这样虽然能够清晰的表达选课关系，但是，某些情况下，它不如下面这种形式来得一目了然：

SNames

张三,李四

要达到这样的目的，需要完成一个一对多关系到一对一关系的转换。这样的转换，在数据库中，可以借助函数来进行，因为函数中应用到了游标，故对于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);

  --通过游标，查找并拼接此课程下的学生姓名

  FOR CUR IN 

  (

          SELECT S.SName FROM Class C

        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，如需转载请自行联系原作者