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

た入场券 2022-12-20 01:49 165阅读 0赞

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

Class:

Student：

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

![731655954c7be9d8835ece551b5385f8.png][]

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

![731655954c7be9d8835ece551b5385f8.png][]

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

![731655954c7be9d8835ece551b5385f8.png][]

得到的结果如下：

<table> 
 <tbody> 
  <tr> 
   <td> <p>ClassID</p> </td> 
   <td> <p>ClassName</p> </td> 
   <td> <p>SName</p> </td> 
  </tr> 
  <tr> 
   <td> <p>001</p> </td> 
   <td> <p>语文</p> </td> 
   <td> <p>张三</p> </td> 
  </tr> 
  <tr> 
   <td> <p>001</p> </td> 
   <td> <p>语文</p> </td> 
   <td> <p>李四</p> </td> 
  </tr> 
  <tr> 
   <td> <p>002</p> </td> 
   <td> <p>数学</p> </td> 
   <td> <p>张三</p> </td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>ClassID</p> </td> 
   <td> <p>ClassName</p> </td> 
   <td> <p>SNames</p> </td> 
  </tr> 
  <tr> 
   <td> <p>001</p> </td> 
   <td> <p>语文</p> </td> 
   <td> <p>张三,李四</p> </td> 
  </tr> 
  <tr> 
   <td> <p>002</p> </td> 
   <td> <p>数学</p> </td> 
   <td> <p>张三</p> </td> 
  </tr> 
 </tbody> 
</table>

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

MS-SQL版：

![731655954c7be9d8835ece551b5385f8.png][]

\--根据课程ID，返回选此课程的学生的名字，以逗号隔开

![731655954c7be9d8835ece551b5385f8.png][]

CREATE function dbo.f\_getStuNamesByClassID (@ClassID int )

![731655954c7be9d8835ece551b5385f8.png][]

RETURNS nvarchar(512 )

![731655954c7be9d8835ece551b5385f8.png][]

begin

![731655954c7be9d8835ece551b5385f8.png][]

declare @Result nvarchar(512 );

![731655954c7be9d8835ece551b5385f8.png][]

declare @stuName nvarchar(256 );

![731655954c7be9d8835ece551b5385f8.png][]

Set @Result='' ;

![731655954c7be9d8835ece551b5385f8.png][]

declare cur cursor for

![731655954c7be9d8835ece551b5385f8.png][]    (

![731655954c7be9d8835ece551b5385f8.png][]

SELECT S.SName FROM  Class C

![731655954c7be9d8835ece551b5385f8.png][]

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

![731655954c7be9d8835ece551b5385f8.png][]

WHERE C.ClassID=@ClassID

![731655954c7be9d8835ece551b5385f8.png][]    )

![731655954c7be9d8835ece551b5385f8.png][]

open  cur;

![731655954c7be9d8835ece551b5385f8.png][]

fetch next from cur into @stuName ;

![731655954c7be9d8835ece551b5385f8.png][]

while(@@fetch\_status=0 )

![731655954c7be9d8835ece551b5385f8.png][]

begin

![731655954c7be9d8835ece551b5385f8.png][]

set @Result=@Result+@stuName+',' ;

![731655954c7be9d8835ece551b5385f8.png][]

fetch next from cur into @stuName ;

![731655954c7be9d8835ece551b5385f8.png][]

end ;

![731655954c7be9d8835ece551b5385f8.png][]

\--去除最后多余的一个逗号

![731655954c7be9d8835ece551b5385f8.png][]

IF @Result <> ''

![731655954c7be9d8835ece551b5385f8.png][]

SET @Result=SUBSTRING(@Result, 1, LEN(@Result)-1 );

![731655954c7be9d8835ece551b5385f8.png][]

ELSE

![731655954c7be9d8835ece551b5385f8.png][]

SET @Result=NULL ;

![731655954c7be9d8835ece551b5385f8.png][]

return @Result ;

![731655954c7be9d8835ece551b5385f8.png][]

end

ORACLE版：

![731655954c7be9d8835ece551b5385f8.png][]

create or replace function FUN\_GET\_STUNAMES\_BY\_CLASSID(P\_CLASSID IN VARCHAR2) return varchar2 is

![731655954c7be9d8835ece551b5385f8.png][]  Result

VARCHAR2(4000 );

![731655954c7be9d8835ece551b5385f8.png][]

begin

![731655954c7be9d8835ece551b5385f8.png][]

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

![731655954c7be9d8835ece551b5385f8.png][]

FOR CUR IN

![731655954c7be9d8835ece551b5385f8.png][]  (

![731655954c7be9d8835ece551b5385f8.png][]

SELECT S.SName FROM  Class C

![731655954c7be9d8835ece551b5385f8.png][]

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

![731655954c7be9d8835ece551b5385f8.png][]

WHERE C.ClassID=@ClassID ;

![731655954c7be9d8835ece551b5385f8.png][]  )

![731655954c7be9d8835ece551b5385f8.png][]  LOOP

![731655954c7be9d8835ece551b5385f8.png][]      Result :

= Result||CUR.SName||',' ;

![731655954c7be9d8835ece551b5385f8.png][]

END  LOOP;

![731655954c7be9d8835ece551b5385f8.png][]

\--去掉最后一个逗号

![731655954c7be9d8835ece551b5385f8.png][]

Result:=SUBSTR(Result,0,LENGTH(Result)-1 );

![731655954c7be9d8835ece551b5385f8.png][]

return (Result);

![731655954c7be9d8835ece551b5385f8.png][]

end ;

![731655954c7be9d8835ece551b5385f8.png][]

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

![731655954c7be9d8835ece551b5385f8.png][]

SELECT  C.ClassID, C.ClassName, dbo.f\_getStuNamesByClassID(C.ClassID)

![731655954c7be9d8835ece551b5385f8.png][]

FROM  Class C;

![731655954c7be9d8835ece551b5385f8.png][]

ORACLE中调用方法类似。

Little knowledge is dangerous.

[731655954c7be9d8835ece551b5385f8.png]: /images/20221120/dd4aa3b334bb4ace91ba84680c65bfae.png

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关一对多关系映射

相关 Mybatis中一对多、多对一关系

相关 django 一对多关系

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

相关【HibernateHibernate实体关系映射——双边的多对一、一对多关系

相关 Mybatis 一对多关系

相关 hibernate关联关系（一对多、多对一）

相关 hibernate 映射关系一对多/多对一

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

相关 mybatis 一对多关系