Write a subordinate relationship, according to the number of their department, find subordinates, not including their own

Source: Internet
Author: User
Tags dname

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.