[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;
'# 7) 데이타베이스 > Ms-Sql' 카테고리의 다른 글
[MS-SQL] 쿼리로 도메인 명 가져오기 . (0) | 2011.05.02 |
---|---|
[MS-SQL] 합계 쿼리. (0) | 2011.03.21 |
[ms-sql] Xml <--> DataTable 변환 쿼리... (0) | 2010.12.31 |
[MS-SQL]쿼리에 대한 성능 비교 해보기.. (0) | 2010.11.05 |
[MS- SQL] 한방에 두개의 범위 데이타 그룹바이 걸기 (0) | 2010.11.05 |
[ms-sql] Xml <--> DataTable 변환 쿼리...
옛날 꼰날... 작업하던중
XML을 통째로 DataTable에 쿼리 하려고 했던적이 있었는데...
잊어버렸다가... 지금 하는 일에 다시 적용해야 될 사항이 생겨서 찾아서 예제로 남김.
어려운거 없다...
-- 예제 결과
-- XML 데이타
<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<INFO>
<SEQ>1</SEQ>
<OLDNM>a군</OLDNM>
<NEWNM />
</INFO>
<INFO>
<SEQ>2</SEQ>
<OLDNM>b군</OLDNM>
<NEWNM />
</INFO>
<INFO>
<SEQ>3</SEQ>
<OLDNM>c군</OLDNM>
<NEWNM />
</INFO>
</ROOT>
-- 예제 소스
DECLARE @INFO TABLE
(
SEQ VARCHAR(100),
NM VARCHAR(100)
)
INSERT INTO @INFO VALUES ('1', 'a군' )
INSERT INTO @INFO VALUES ('2', 'b군' )
INSERT INTO @INFO VALUES ('3', 'c군' )
SELECT SEQ, NM as OLDNM, '' [NEWNM] FROM @INFO
DECLARE @XML XML
SET @XML = REPLACE( (SELECT SEQ, NM as OLDNM, '' [NEWNM] FROM @INFO FOR XML PATH('INFO'), ROOT ('ROOT')) , '<ROOT>' , '<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" >')
SELECT @XML AS [XML]
declare @RegistXML XML
set @RegistXML = '
<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<INFO>
<SEQ>1</SEQ>
<OLDNM>a군</OLDNM>
<NEWNM>x군</NEWNM>
</INFO>
<INFO>
<SEQ>2</SEQ>
<OLDNM>b군</OLDNM>
<NEWNM>b군</NEWNM>
</INFO>
<INFO>
<SEQ>3</SEQ>
<OLDNM>c군</OLDNM>
<NEWNM></NEWNM>
</INFO>
</ROOT>'
SELECT
M.Item.query('./SEQ').value('.','VARCHAR(100)') SEQ,
M.Item.query('./OLDNM').value('.','VARCHAR(100)') [OLDNM],
M.Item.query('./NEWNM').value('.','VARCHAR(100)') [NEWNM]
FROM @RegistXML.nodes('/ROOT/INFO') AS M(ITEM)
UPDATE @INFO
SET NM = T.[NEWNM]
FROM (
SELECT
M.Item.query('./SEQ').value('.','VARCHAR(100)') SEQ,
M.Item.query('./OLDNM').value('.','VARCHAR(100)') [OLDNM],
M.Item.query('./NEWNM').value('.','VARCHAR(100)') [NEWNM]
FROM @RegistXML.nodes('/ROOT/INFO') AS M(ITEM)
) T
INNER JOIN @INFO B
ON T.SEQ = B.SEQ and T.[NEWNM] <> ''
SELECT * FROM @INFO
/* XML을TABLE로*/
--SELECT * FROM TB_BASE_USER
--WHERE USER_NM LIKE 'KCC%'
반대로 table을 다시 xml로 바꿀때
declare @xxx xml = '<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">' + ( SELECT * FROM @INFO FOR XML PATH ('INFO') ) + '</ROOT>'
select @xxx
'# 7) 데이타베이스 > Ms-Sql' 카테고리의 다른 글
[MS-SQL] 합계 쿼리. (0) | 2011.03.21 |
---|---|
[MS-SQL] CTE 로 부서 이하직원들 불러올리는 쿼리... (0) | 2011.01.05 |
[MS-SQL]쿼리에 대한 성능 비교 해보기.. (0) | 2010.11.05 |
[MS- SQL] 한방에 두개의 범위 데이타 그룹바이 걸기 (0) | 2010.11.05 |
[MS-SQL] 누적 계산에 사용한 쿼리... (0) | 2010.10.23 |
자바 스크립트] XOR 암호화.
Simple Xor 암호화 관련해서 전에 만들어 사용했었던...
자바스크립트로 찾아보니 있었다.
String
.charAt
.charCodeAt
.fromCharCode
이하 예제및 소스..
출처의 소스에서 예외를 찾아 수정하였음.
키2개를 넣으면서 키가 중복일 경우 일부 코드가 풀린체로 보이는 xor의 특징이 있으므로 키값 중복을 피하거나
다른 암호화 처리방법과 혼용하면 좋겠다.
전에 C#으로 만들어서 사용했던 로직과 유사하나 자바스크립트로 작성하였을 뿐이다.
[\0] 값을 주의!! xor 값 ^ 값 인데 같은 값이면.. \0으로 표시가 안되는 문자열로... 희안하게 나타남...
스크립트를 작성해보면 알게됨.
변환 중 내용보기
첨부파일 : html 소스가 안올라가서.. 올림.
'# 6) JavaScript' 카테고리의 다른 글
javascript] OOP정리. function 이용법 (0) | 2014.05.23 |
---|---|
javascript] OOP정리. class 처럼 생성해보기. (0) | 2014.05.23 |
[javascript] 정규식 관련.. (0) | 2010.11.18 |
메뉴] 자바스크립트로 만든 메뉴 객체 (0) | 2009.05.09 |
자바스크립트 Array사용 (0) | 2009.05.08 |