【Evil 域】SQL函式——將一對多關係轉換成一對一關係

iDotNetSpace發表於2008-07-08
SQL函式
我們經常會遇到想要把一對多關係轉換成為一對一關係，以方便顯示。例如有如下關係：
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 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中呼叫方法類似。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12639172/viewspace-374432/，如需轉載，請註明出處，否則將追究法律責任。

相關文章