程式師世界 >> 編程語言 >> .NET網頁編程 >> .NET實例教程 >> 函數 sql

函數 sql

編輯：.NET實例教程

這個工作組選擇人員頁面時涉及到七八個表,有人員基本表,人員詳細信息表,人員與部門職位關系表,
部門基本信息表,職位表.
人員與工作組關系表,工作組表.
creat   FUNCTION [myfun]( @mid bigint)
returns varchar(200)
as
begin
    declare @ret varchar(2000)
    set @ret = ''''
select @ret=@ret+'',''+b.Groups from dbo.TOrganization_GroupMember as a inner join
dbo.TOrganization_GroupInfo as b on a.groupid=b.groupid and a.membersid=@mid
    set @ret = stuff(@ret,1,1,'''')
    return @ret
end

select TOrganization_MembersBasicInfo.ID,TOrganization_MembersBasicInfo.MembersID,substring(TOrganization_MembersBasicInfo.MembersName,0,10) as MembersName,
TOrganization_MembersDetailInfo.EmployeesName, case when
(TOrganization_PositionInfo.Positions is null) then ''無職位'' else TOrganization_PositionInfo.Positions
end as Positions,
case when (dbo.myfun(TOrganization_MembersBasicInfo.membersid) is null) then ''沒有工作組'' else
dbo.myfun(TOrganization_MembersBasicInfo.membersid)
end
as groups
,TOrganization_DepartmentBasicInfo.DepartmentName
from TOrganization_MembersDetailInfo INNER JOIN TOrganization_MembersBasicInfo ON
TOrganization_MembersDetailInfo.MembersID = TOrganization_MembersBasicInfo.MembersID
INNER JOIN TOrganization_DepartmentMember ON
TOrganization_MembersDetailInfo.MembersID = TOrganization_DepartmentMember.MembersID
left JOIN TOrganization_DepartmentBasicInfo ON
TOrganization_DepartmentMember.DepartmentID = TOrganization_DepartmentBasicInfo.DepartmentID
left JOIN TOrganization_PositionInfo ON
TOrganization_DepartmentMember.PositionID = TOrganization_PositionInfo.PositionID
where TOrganization_MembersDetailInfo.EmployessStatus=1 <歡迎光臨學網,收藏本篇文章 [1] [2]

$False$

br>