[轉]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，如需轉載請自行聯系原作者