日期:2014-05-18 浏览次数:20503 次
;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