;with t as
(
    select b.* ,lvl=0  from b b,a a
    where a.cate_node_id=b.[cate_node_id]
        
    union all
    select n.* ,lvl+1 from t m,b n where m.parent_id=n.cate_node_id
)
,t1 as
(
    select c.[User_Id],c.Prop_id Prop_Id,--t.parent_id,
        c.cate_node_id Cate_Node_Id,
        t.lvl,cnt=count(1)over(partition by [User_Id])
    from t 
        join c  c
            on t.cate_node_id=c.cate_node_id
            
)
,t2 as
(
    select 
        t1 .*,abs(t1.Cate_Node_Id-a.cate_node_id) 差值,
    --    top 1 with ties [User_Id]
        AProp_Id=a.Prop_id,ACate_Node_Id=a.cate_node_id,ALevel_No=a.Level_No
    from t1 
        join a a
            on t1.prop_id=a.Prop_id 
            
    where cnt=(select count(1) from a 
                
                )
)
,t3 as
(
    select *,px=(select ','+right(10000+lvl,4) from t2 x where [User_Id]=t2.[User_Id] order by ALevel_No,差值 for xml path('')) 
    from t2
)
,t4 as
(
    select top 1 with ties [User_id] 
    from t3
    order by px
)
select distinct * from t4