[MS-SQL] CTE 로 부서 이하직원들 불러올리는 쿼리...

문제가 좀 있는 쿼리로.. 해결방법을 고민중...


부서 정보를 끌어올리고  사용자 정보를 모아서

부서별 직원정보를 만든 후

>>  각 직원별 자기 상급자들을 하나의 컬럼에 순서대로 나열하고자 만든 쿼리이나..

>> 중간 부서에 다수의 상급자가 존재 시 이하 부서직원들의 갯수가 뻥튀기 되는 현상이 CTE에서 발생하는데

이를 해결할 다른 방법은 없는 것일까???



declare @BaseDept table
(
 deptcode varchar(40 ),
 parentdeptcode varchar(40),
 deptname varchar(40)
)

insert into @BaseDept values( '000', null ,  '플랜트')
insert into @BaseDept values( 'a00', '000' , '공장1')
insert into @BaseDept values( 'b00', '000' , '공장2')
insert into @BaseDept values( 'c00', 'a00' , '검사')
insert into @BaseDept values( 'd00', 'a00' , '조립')
insert into @BaseDept values( 'e00', 'c00' , '수리')
insert into @BaseDept values( 'f00', 'b00' , '출하')

declare @BaseUser table
(
 userId varchar(40),
 deptcode varchar(40),
 userName varchar(100)
)

insert into @BaseUser values ( 'u01', '000' , '사장1' )
insert into @BaseUser values ( 'u02', 'a00' , '공장1장장' )
insert into @BaseUser values ( 'u03', 'b00' , '공장2장장' )
insert into @BaseUser values ( 'u04', 'c00' , '테스터' )
insert into @BaseUser values ( 'u05', 'd00' , '조립맨' )
insert into @BaseUser values ( 'u06', 'e00' , 'A/s맨' )
insert into @BaseUser values ( 'u07', 'f00' , '오라이' )
insert into @BaseUser values ( 'u08', 'a00' , '잉여' )

declare @ttt table ( userid varchar(40) )

;with dept( deptcode , parentdeptCode , deptname , lvl , sort )as
( 
 select  deptcode , parentdeptCode , deptname , 0 , cast( deptcode as varbinary(900))
 from @BaseDept
 where deptcode = '000'
 union all
 select a.deptcode , a.parentdeptCode , a.deptname , lvl + 1 , cast( sort + cast( a.deptcode as binary(3) ) as varbinary(900))
 from @BaseDept a inner join dept b
 on a.parentdeptCode = b.deptcode
)
, [user]( userid , deptcode , userName )as
( 
 select userid , deptcode , userName from @BaseUser
)

, dept_user( deptcode , parentdeptCode , deptname , lvl , sort,   userid , userName)as
(
 
 select a.deptcode , a.parentdeptCode , a.deptname , a.lvl , a.sort,   b.userid , b.userName
 from dept a left outer join [user] b on a.deptcode = b.deptcode
)
 
select  row_number() over( order by b.deptcode ),  replicate( '.------> ',  lvl ) + deptName [Tree] , b.deptcode , parentdeptCode , deptname , lvl , sort,   userid , userName, cnt, no
from  dept_user  b
left outer join
(
 select  row_number() over (order by deptcode, userid ) no,  deptcode , count( 1 ) cnt
 from  dept_user
 group by deptcode, userid
) c
on b.deptcode = c.deptcode
order by sort

--/* 각자의 상급자 컬럼을 추가 */
--
--,aboveDept_user ( [no], [no2], deptcode , parentdeptCode , deptname , ltl , srt ,   userid , userName , aboveuser  ) as
--(
-- select  cast( row_number() over( order by userid ) as int), 100,  deptcode , parentdeptCode , deptname , 0 , cast( deptcode as varbinary(900)) , userid ,  userName , cast( '/' + userName as varchar(3000))
-- from dept_user
-- where deptcode = '000'
-- union all
-- select  au.no , cast( row_number() over( order by du.userid ) as int) no2,  du.deptcode, du.parentdeptCode, du.deptname , ltl +1 , cast( srt + cast( du.deptcode as binary(3) ) as varbinary(900)) ,   du.userid , du.userName, cast( aboveuser + '/' + du.username as varchar( 3000 ))
-- from dept_user du inner join aboveDept_user au
-- on  du.parentdeptcode = au.deptcode
--)
--
--select replicate( '.------>',  ltl ) + deptName [Tree] , *
--from aboveDept_user ms
--inner join
--(
-- select deptcode, max( isnull( case when no = 1 then  userid+'('+ username+')' else '' end  , '') ) +  max( isnull( case when no = 2 then  userid+'('+ username+')' else '' end  , '') ) as [desc]
-- from aboveDept_user
-- group by deptcode 
--) sl
--on ms.deptcode = sl.deptcode
----order by srt;