MySQL 로 작성한 트리구조 탐색 쿼리를 MSSQL로 바꿔 보았습니다.

https://devse.tistory.com/28<< MySQL 쿼리

✔ 트리 구조

✔ 쿼리

1
2
3
4
5
6
7
8
9
10
11
12
13
declare @v_id varchar(45), @v_lev int
 
set @v_id = 'a20'; -- 시작 node
 
with tree as (
    select id, pid, 0 lev
    from t_test
    where id=@v_id
    union all
    select ta.id, ta.pid, (tb.lev + 1) lev
    from t_test ta inner join tree tb on ta.id = tb.pid
)
select id, pid, lev from tree order by lev desc

✔ 결과

✔ 테스트용 자료 입력

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
USE [TEST_DB]
GO
 
CREATE TABLE [dbo].[t_test](
    [id] [varchar](45) NOT NULL,
    [pid] [varchar](45) NOT NULL,
    [data] [varchar](100) NOT NULL,
 CONSTRAINT [PK_t_test] PRIMARY KEY CLUSTERED 
(
    [id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a1', N'a0', N'그림1')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a10', N'a9', N'그림10')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a11', N'a7', N'그림11')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a12', N'a8', N'그림12')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a13', N'a10', N'그림13')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a14', N'a10', N'그림14')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a15', N'a10', N'그림15')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a16', N'a13', N'그림16')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a17', N'a13', N'그림17')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a18', N'a14', N'그림18')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a19', N'a15', N'그림19')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a2', N'a1', N'그림2')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a20', N'a17', N'그림20')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a21', N'a4', N'그림21')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a22', N'a21', N'그림22')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a23', N'a5', N'그림23')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a24', N'a11', N'그림24')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a25', N'a11', N'그림25')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a26', N'a12', N'그림26')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a27', N'a9', N'그림27')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a28', N'a25', N'그림28')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a29', N'a22', N'그림29')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a3', N'a1', N'그림3')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a30', N'a2', N'그림30')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a4', N'a2', N'그림4')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a5', N'a4', N'그림5')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a6', N'a4', N'그림6')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a7', N'a6', N'그림7')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a8', N'a3', N'그림8')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a9', N'a3', N'그림9')
GO
http://colorscripter.com/info#e" target="_blank" style="color:#e5e5e5text-decoration:none">Colored by Color Scripter

✔ 실행 계획

저작자표시 비영리 변경금지

'Database & Data > MSSQL' 카테고리의 다른 글

Oracle NEXT_DAY 함수를 MSSQL NEXT_DAY 로 만들기  (0) 2020.02.17
SP_LOCK 출력 결과의 정렬 순서 개선 (Mode DESC)  (0) 2019.01.01
MSSQL- 복제 관련 조회 쿼리  (0) 2018.10.28
MSSQL 트랜잭션 로그 축소  (0) 2018.08.12
MSSQL 문자열을 파싱하여 테이블 구조로 변환  (0) 2018.08.05

얼마전 면접을 보다가, 아래 그림과 같은 데이터 구조에서의 탐색 방법에 대한 DB 설계 및 쿼리 작성을 하시오 라는 질문에

최적의 답변을 하지 못해. 집에 와서 테스트 하였다.

 

질문의 핵심은 특정 node 를 선택했을때, 최상위 부모까지 올라가는 흐름을 한줄로 표현하는 것이었다.

 

ex) 그림20 > 그림17 > 그림13 > 그림10 > 그림9 > 그림3 > 그림1

 

데이터의 구조는 아래와 같다.


테이블로 옮기면,


일반적인 재귀호출 용 테이블의 구조가 된다. 여기에 아래 쿼리를 실행하면 


1
2
3
4
5
6
7
8
9
set @id := 26;        -- node id
set @L := 0;
 
 
SELECT @r AS _ID
    , (SELECT @r := pid FROM t_test WHERE id = _ID) AS _PARENT_ID
    , (@L := @L + 1) AS level
FROM ( SELECT @r := @id) AS vars, (SELECT * FROM t_test WHERE id < @id) AS h
WHERE @r <> 0;
cs


그림과 같이 우리가 원하는 결과를 얻을 수 있다.

약간만 수정하면 두루 두루 쓸모가 많을것 같다.

이래서 실무자 면접은 참 재미있는것 같다. 내 실력을 평가받는 자리도 되고 그로인해 공부도 되고.




---------------------------------------------------------- ID , PID 가 문자형인경우 ----------------------------------------------------------

ID , PID 문자형일 경우 어떻게 수정해야 하는지에 대한 문의가 있어 관련 내용 공유 드립니다.

위 데이터에서 각 컬럼을 문자형으로 변경 후 앞에 a를 붙여 a1, a2, a3 형식으로 변경 하였습니다.


1
2
3
4
5
6
7
8
9
set @id := 'a26';         -- node id
set @L := 0;              -- level 컬럼의 시작 값, 0 입력시 1부터 1씩 증가된 값이 표시 됨
 
SELECT @r AS _ID
    , (SELECT @r := pid FROM t_test WHERE id = @r limit 1) AS _PARENT_ID
    , (@L := @L + 1) AS level
FROM ( SELECT @r := @id) AS vars, (SELECT * FROM t_test WHERE id != @id) AS h
WHERE @r <> 'a0';          -- > root node를 만나면 탐색을 중지하기 위함
-- WHERE @r <> '-';        -- > null 이 아닌 임이의 값 (예 : '-') 입력
cs


출력 결과 : 


MSSQL 로 구현하면 >> https://devse.tistory.com/69

'Database & Data > MYSQL' 카테고리의 다른 글

조회 결과를 CSV 파일로 출력  (0) 2017.12.30
Raspberry 에서 사용하는 MariaDB 백업 Shell Script 입니다.  (0) 2017.11.03
[MySQL] mysqldump 스크립트 자동화시 계정에 관한 경고 처리  (0) 2016.03.31
[MySQL] binary log 파일 변환 및 오류 해결  (0) 2016.03.25
[MySQL] 간편한 slow log File 초기화 방법  (0) 2016.03.15

+ Recent posts

티스토리툴바