我们经常会遇到想要把一对多关系转换成为一对一关系,以方便显示。例如有如下关系:
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;
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
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 ;
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调用时,通过以下语句实现:
ORACLE中调用方法类似。
SELECT
C.ClassID, C.ClassName, dbo.f_getStuNamesByClassID(C.ClassID)
FROM Class C;
FROM Class C;