O a MSSQL function implementation effect as follows-CodePudding

Id the father serial number sequence number son content
11 1 test 1
11 1 2 test 2
11 1 3 test 3
11 1 4 test 4
11 1 5 test
11 1 6 test
11 1 7 test 7
11 2 test 1
11 2 2 2 test
2, 3, 11 test 3
11 2 4 test 4
22 1 test 1
33 1 test 1
33 1 2 test 2
44 1 test 1
44 1 2 test 2
44 1 3 test 3
44 1 4 test 4

Implementation effect is as follows: (0 item 1 head 2 and 3 tail)
With ID number of conditions, and the father son only 1 record serial number is 0;
More than one record, blade number 1 as child number 1;
The last right number 3 in the middle of the child serial number is 2
Id the father serial number sequence number son content
11 1 test 1
11 1 2 test 2
11 1 2 test 3
11 1 2 test 4
11 1 2 test 5
11 1 2 test 6
11 1 3 test 7
11 2 test 1
11 2 2 2 test
11 2 2 test 3
2, 3, 11 test 4
22 1 0 test 1
33 1 test 1
33 1 2 3 test
44 1 test 1
44 1 2 test 2
44 1 2 test 3
44 1 3 test 4

Pray god to help write a function, thank you!

CodePudding user response:

; With t1 as (
Select *, row_number () over (partition by id, parentid order by getdate ()) as rids the from tableName 
), t2 as (
Select id, parentid, Max (rid) as mx from t1 group by id, parentid 
) 
Select a. *, c. * from t1 a 
Left the join t2 on Anderson, d=b and Amy polumbo arentid b.i d=p. arentid 
Cross the apply (
The select 0 as sub_id where mx=1 
Union all 
Select 1 where rids=1 and mx> 1 
Union all 
Select 3 where rids=mx and mx> 1 
Union all 
Select 2 where mx> 1 and rid<> 1 and rid<> Mx 
C)