CREATE TABLE [dbo].[tree_tbl](
[idx] [int] IDENTITY(1,1) NOT NULL,
[tree_up] [int] NOT NULL,
[tree_depth] [int] NOT NULL,
[tree_name] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
insert into tree_tbl (tree_up, tree_depth, tree_name) values (0, 1, '패션의류');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (0, 1, '식품');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (0, 1, '생활용품');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (0, 1, '자동차용품');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (1, 2, '남성패션');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (1, 2, '여성패션');;
insert into tree_tbl (tree_up, tree_depth, tree_name) values (2, 2, '과일');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (2, 2, '과자');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (2, 2, '커피');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (3, 2, '세탁세제');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (3, 2, '욕실용품');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (3, 2, '생활잡화');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (4, 2, '인테리어');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (4, 2, '세차용품');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (4, 2, '차량용전자기기');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (5, 3, '의류');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (5, 3, '신발');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (5, 3, '가방');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (7, 3, '사과');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (7, 3, '포도');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (7, 3, '수박');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (7, 3, '참외');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (17, 4, '운동화');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (17, 4, '축구화');
insert into tree_tbl (tree_up, tree_depth, tree_name) values (17, 4, '등산화');
테이블 구조는 위와 같고 데이터를 insert 했습니다.
그리고 asp 소스는 아래와 같습니다.
============================================================
<!--#include virtual ="dbconn.asp"-->
<!DOCTYPE html>
<html lang="ko">
<head>
<title></title>
<meta charset="UTF-8" />
<style type="text/css">
.tree_1 { display:block; margin-left:100px; }
.tree_2 { display:block; margin-left:200px; }
.tree_3 { display:block; margin-left:300px; }
.tree_4 { display:block; margin-left:400px; }
</style>
</head>
<body>
<%
Function sub_tree(tree_up, tree_depth)
sql = "select * from tree_tbl where tree_up = " & tree_up & " and tree_depth = " & tree_depth & " order
by idx asc;"
set rs = server.createobject("ADODB.Recordset")
rs.Open sql,db,1
Do until rs.BOF or rs.EOF
rs_idx = rs("idx")
rs_tree_depth = rs("tree_depth")
rs_tree_name = rs("tree_name")
%>
<div class="tree_<%=rs_tree_depth %>"><%=rs_tree_name %></div>
<%
Call sub_tree(rs_idx, rs_tree_depth+1)
rs.MoveNext
Loop
End Function
%>
<%
Call sub_tree(0, 1)
%>
</body>
</html>
============================================================
결과가 원하는 대로 나오기는 하는데 데이터 양이 많아질 경우 함수 호출을 그만큼 많이 하게 되니 속도가 매우 느려집니다.
같은 트리구조가 나오게 한방에 쿼리를 작성할 수는 없을까요?
단계적으로 불러오게 만들면 되긴 하는데
소스가 다 보여야 한다고 해서 데이터를 미리 다 불러와야 합니다.
:{3} 
({4})