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

港控/mmm° 2021-11-29 07:02 336阅读 0赞

我们经常会遇到想要把一对多关系转换成为一对一关系，以方便显示。例如有如下关系：  
 Class(ClassID,ClassName)和Student(SID,SName,ClassID)，并且，这两个关系存在以下测试数据：

Class:

Student：

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

![None.gif][] SELECT  C.ClassID, C.ClassName, S.SName  FROM  Class C  
![None.gif][] LEFT  JOIN  Student S  ON  C.ClassID = S.ClassID;  
![None.gif][]

得到的结果如下：

<table style="border-right:medium none;border-top:medium none;border-left:medium none;border-bottom:medium none;border-collapse:collapse;"> 
 <tbody> 
 <tr> 
 <td style="border-right:#000000 1pt solid;border-top:#000000 1pt solid;border-left:#000000 1pt solid;width:106.5pt;border-bottom:#000000 1pt solid;"> ClassID </td> 
 <td style="border-right:#000000 1pt solid;border-top:#000000 1pt solid;border-left:medium none;width:106.5pt;border-bottom:#000000 1pt solid;"> ClassName </td> 
 <td style="border-right:#000000 1pt solid;border-top:#000000 1pt solid;border-left:medium none;width:106.55pt;border-bottom:#000000 1pt solid;"> SName </td> 
 </tr> 
 <tr> 
 <td style="border-right:#000000 1pt solid;border-top:medium none;border-left:#000000 1pt solid;width:106.5pt;border-bottom:#000000 1pt solid;"> 001 </td> 
 <td style="border-right:#000000 1pt solid;border-top:medium none;border-left:medium none;width:106.5pt;border-bottom:#000000 1pt solid;"> 语文 </td> 
 <td style="border-right:#000000 1pt solid;border-top:medium none;border-left:medium none;width:106.55pt;border-bottom:#000000 1pt solid;"> 张三 </td> 
 </tr> 
 <tr> 
 <td style="border-right:#000000 1pt solid;border-top:medium none;border-left:#000000 1pt solid;width:106.5pt;border-bottom:#000000 1pt solid;"> 001 </td> 
 <td style="border-right:#000000 1pt solid;border-top:medium none;border-left:medium none;width:106.5pt;border-bottom:#000000 1pt solid;"> 语文 </td> 
 <td style="border-right:#000000 1pt solid;border-top:medium none;border-left:medium none;width:106.55pt;border-bottom:#000000 1pt solid;"> 李四 </td> 
 </tr> 
 <tr> 
 <td style="border-right:#000000 1pt solid;border-top:medium none;border-left:#000000 1pt solid;width:106.5pt;border-bottom:#000000 1pt solid;"> 002 </td> 
 <td style="border-right:#000000 1pt solid;border-top:medium none;border-left:medium none;width:106.5pt;border-bottom:#000000 1pt solid;"> 数学 </td> 
 <td style="border-right:#000000 1pt solid;border-top:medium none;border-left:medium none;width:106.55pt;border-bottom:#000000 1pt solid;"> 张三 </td> 
 </tr> 
 </tbody> 
</table>

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

<table style="border-right:medium none;border-top:medium none;border-left:medium none;border-bottom:medium none;border-collapse:collapse;"> 
 <tbody> 
 <tr> 
 <td style="border-right:#000000 1pt solid;border-top:#000000 1pt solid;border-left:#000000 1pt solid;width:106.5pt;border-bottom:#000000 1pt solid;"> ClassID </td> 
 <td style="border-right:#000000 1pt solid;border-top:#000000 1pt solid;border-left:medium none;width:106.5pt;border-bottom:#000000 1pt solid;"> ClassName </td> 
 <td style="border-right:#000000 1pt solid;border-top:#000000 1pt solid;border-left:medium none;width:106.55pt;border-bottom:#000000 1pt solid;"> SNames </td> 
 </tr> 
 <tr> 
 <td style="border-right:#000000 1pt solid;border-top:medium none;border-left:#000000 1pt solid;width:106.5pt;border-bottom:#000000 1pt solid;"> 001 </td> 
 <td style="border-right:#000000 1pt solid;border-top:medium none;border-left:medium none;width:106.5pt;border-bottom:#000000 1pt solid;"> 语文 </td> 
 <td style="border-right:#000000 1pt solid;border-top:medium none;border-left:medium none;width:106.55pt;border-bottom:#000000 1pt solid;"> 张三,李四 </td> 
 </tr> 
 <tr> 
 <td style="border-right:#000000 1pt solid;border-top:medium none;border-left:#000000 1pt solid;width:106.5pt;border-bottom:#000000 1pt solid;"> 002 </td> 
 <td style="border-right:#000000 1pt solid;border-top:medium none;border-left:medium none;width:106.5pt;border-bottom:#000000 1pt solid;"> 数学 </td> 
 <td style="border-right:#000000 1pt solid;border-top:medium none;border-left:medium none;width:106.55pt;border-bottom:#000000 1pt solid;"> 张三 </td> 
 </tr> 
 </tbody> 
</table>

要达到这样的目的，需要完成一个一对多关系到一对一关系的转换。这样的转换，在数据库中，可以借助函数来进行，因为函数中应用到了游标，故对于Oracle和MSSQL稍有不同，附上两个版本的函数SQL代码：

MS-SQL版：

![None.gif][]\--根据课程ID，返回选此课程的学生的名字，以逗号隔开 
![None.gif][] CREATE function dbo.f\_getStuNamesByClassID (@ClassID int ) 
![None.gif][]RETURNS nvarchar(**512** ) 
![None.gif][]begin 
![None.gif][]     declare @Result nvarchar(**512** ); 
![None.gif][]    declare @stuName nvarchar(**256** ); 
![None.gif][]    Set @Result='' ; 
![None.gif][] 
![None.gif][]    declare cur cursor for 
![None.gif][]    ( 
![None.gif][]         SELECT S.SName FROM  Class C 
![None.gif][]        LEFT JOIN Student S ON C.ClassID= S.ClassID 
![None.gif][]        WHERE C.ClassID=@ClassID 
![None.gif][]    ) 
![None.gif][]    open  cur; 
![None.gif][]    fetch next from cur into @stuName ; 
![None.gif][]    while(**@@fetch\_status**=**0** ) 
![None.gif][]    begin 
![None.gif][]         set @Result=@Result\+@stuName\+',' ; 
![None.gif][]        fetch next from cur into @stuName ; 
![None.gif][]    end ; 
![None.gif][]\--去除最后多余的一个逗号 
![None.gif][]     IF @Result <> ''   
![None.gif][]        SET @Result=SUBSTRING(@Result, **1**, LEN(@Result)\-**1** ); 
![None.gif][]    ELSE 
![None.gif][]         SET @Result=NULL ; 
![None.gif][]    return @Result ; 
![None.gif][]end

ORACLE版：

![None.gif][]create or replace function FUN\_GET\_STUNAMES\_BY\_CLASSID(P\_CLASSID IN VARCHAR2) return varchar2 is   
![None.gif][]  Result  VARCHAR2(**4000** );  
![None.gif][]begin   
![None.gif][]   \--通过游标，查找并拼接此课程下的学生姓名   
![None.gif][]   FOR CUR IN    
![None.gif][]  (  
![None.gif][]          SELECT S.SName FROM  Class C  
![None.gif][]        LEFT JOIN Student S ON C.ClassID= S.ClassID  
![None.gif][]        WHERE C.ClassID=@ClassID ;  
![None.gif][]  )   
![None.gif][]  LOOP  
![None.gif][]      Result := Result||CUR.SName||',' ;  
![None.gif][]  END  LOOP;  
![None.gif][]  \--去掉最后一个逗号   
![None.gif][]   Result:=SUBSTR(Result,**0**,LENGTH(Result)\-**1** );  
![None.gif][]  return (Result);  
![None.gif][]end ;  
![None.gif][]

MS-SQL调用时，通过以下语句实现：

![None.gif][] SELECT  C.ClassID, C.ClassName, dbo.f\_getStuNamesByClassID(C.ClassID)   
![None.gif][] FROM  Class C;  
![None.gif][]

ORACLE中调用方法类似。

Little knowledge is dangerous.

转载于:https://www.cnblogs.com/skywind/archive/2008/04/09/1144676.html

[None.gif]: /images/20211129/b700a9c13f0243338a4f79d3d232dafb.png