Did Pdid dname CIndex
1923 Business 1 1
2024 Business 2 1
2125 Business 3 1
2226 Business 4 1
2328 Manager 1 1
2429 Manager 2 1
2530 Manager 3 1
On the table, the specific content, when a person belonging to multiple departments, take out the most senior department to deal with
--select uid from DBO.FUNCTION_SXJ (21)
--select * from Ddept
---Hierarchy of authority tables
Create function Function_sxj (@ddid int)
RETURNS @Table Table (uid int)
As
Begin
Declare @zzjg table (ID int identity (), did int,dname varchar (), JB Int)
Declare @temp table (ID int identity (), did int)
DECLARE @i int, @did int
INSERT into @temp (did)
Select did from ddept where [email protected]
Set @i=1
--if exists (select top 1 1 from ddept a inner joins @temp B on A.pdid=b.did)
--begin
while (IsNull (select top 1 1 from ddept a inner joins @temp B on A.pdid=b.did), 0) <>0)
Begin
Insert into @zzjg (DID,DNAME,JB)
Select A.did,a.dname,@i from Ddept a inner joins @temp B on A.pdid=b.did
If exists (select top 1 1 from ddept a inner joins @zzjg B on A.pdid=b.did where [email protected])
Begin
Delete @temp
INSERT into @temp (did)
Select did from @zzjg where [email protected]
Set @[email protected]+1
End
Else
Begin
Delete @temp
End
End
Select top 1 @did =b.did from Dudd a inner join @zzjg B to a.did=b.did ORDER by JB
Insert @Table
Select B.uid from @zzjg a inner join Dudd B in A.did=b.did where jb>= (select JB from @zzjg where [email protected])
Return
End
Write a subordinate relationship, according to the number of their department, find subordinates, not including their own