숀패밀리

카테고리에 적합한 쿼리 형식

· 2011-12-17 (토) 11:20:05 · 4255
-- 카테고리 테이블을 작성한다.
DROP TABLE IF EXISTS category ;
CREATE TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent INT DEFAULT NULL);

INSERT INTO category
VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),
(7,'MP3 PLAYERS',6),(8,'FLASH',7),
(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);
SELECT * FROM category ORDER BY category_id;

-- self 조인을 이용한 DEPTH ( LEVEL ) 구현 쿼리
-- 일반적인 방법이다.
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';
-- 레벨의 마지막 leaf node 만을 조회
 
SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;

-- 한개의 카테고리에 대해 상위 카테고리들 조회
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';

-- SELF 조인이 아닌 방법을 이용하기 위한 테이블 생성 
DROP TABLE IF EXISTS  nested_category ;
 
CREATE TABLE nested_category (
 category_id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(20) NOT NULL,
 lft INT NOT NULL,
 rgt INT NOT NULL
);
INSERT INTO nested_category
VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
(4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),
(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
(9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);

-- 당 카테고리가 지니고 있는 하위 카테고리 출력 
SELECT node.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = 'PORTABLE ELECTRONICS'
ORDER BY node.lft;
-- 모든 마지막 레벨 조회 
SELECT name
FROM nested_category
WHERE rgt = lft + 1;
-- 해당 카테고리가 소속되는 상위 카테고리 출력
SELECT parent.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'FLASH'
ORDER BY parent.lft;
-- 카테고리별 TREE 의 레벨 출력
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
댓글을 작성하시려면 로그인이 필요합니다.

프로그램

8,187건
+
제목 글쓴이 날짜 조회
13-03-03 조회 6,284
13-03-01 조회 5,612
13-03-01 조회 8,354
13-03-01 조회 6,360
13-02-26 조회 3,973
13-02-26 조회 3,924
13-02-26 조회 4,796
13-02-24 조회 8,201
13-02-24 조회 4,304
13-02-24 조회 3,534
13-02-24 조회 5,348
13-02-24 조회 5,339
13-02-24 조회 4,399
13-02-24 조회 5,735
13-02-24 조회 9,433
13-02-14 조회 5,178
13-02-13 조회 9,105
13-01-29 조회 7,723
13-01-24 조회 5,070
13-01-24 조회 4,951
13-01-17 조회 4,373
13-01-16 조회 4,404
13-01-08 조회 4,430
12-12-27 조회 4,122
12-12-20 조회 1.1만
12-12-20 조회 3,425
12-12-06 조회 4,432
12-12-01 조회 4,191
12-11-28 조회 5,762
12-11-25 조회 5,472
12-10-20 조회 4,703
12-10-03 조회 4,869
12-09-26 조회 5,025
12-09-15 조회 8,468
12-09-14 조회 5,483
12-09-11 조회 4,003
12-09-10 조회 3,996
12-09-05 조회 8,885
12-08-29 조회 4,710
12-08-26 조회 3,832
12-08-23 조회 3,533
12-08-22 조회 3,615
12-08-22 조회 4,429
12-08-21 조회 3,571
12-08-20 조회 3,496
12-08-20 조회 9,304
12-08-15 조회 4,423
12-08-13 조회 3,330
12-08-11 조회 4,745
12-08-05 조회 4,609
12-08-03 조회 3,382
12-08-03 조회 4,078
12-08-02 조회 5,066
12-07-29 조회 4,184
12-07-26 조회 3,749
12-07-26 조회 3,613
12-07-22 조회 3,537
12-07-20 조회 3,365
12-07-19 조회 4,234
12-07-19 조회 3,500
12-07-16 조회 3,608
12-07-16 조회 4,531
12-07-15 조회 3,611
12-07-12 조회 7,424
12-07-12 조회 5,070
12-07-11 조회 3,122
12-07-09 조회 3,277
12-07-05 조회 3,889
12-07-04 조회 4,470
12-07-04 조회 3,870
12-07-04 조회 4,400
12-06-29 조회 3,869
12-06-27 조회 3,464
12-06-24 조회 3,931
12-06-22 조회 4,242
12-06-22 조회 8,646
12-06-22 조회 3,305
12-06-21 조회 3,321
12-06-17 조회 3,967
12-06-14 조회 4,056
12-06-14 조회 3,351
12-06-08 조회 4,486
12-06-06 조회 3,437
12-06-04 조회 4,219
12-05-26 조회 5,018
12-05-26 조회 3,972
12-05-25 조회 3,266
12-05-24 조회 3,364
12-05-22 조회 3,424
12-05-20 조회 3,061
12-05-16 조회 3,177
12-05-15 조회 3,562
12-05-10 조회 3,987
12-05-08 조회 3,530
12-05-04 조회 3,372
12-05-03 조회 4,329
12-05-02 조회 3,464
12-05-02 조회 3,222
12-05-02 조회 3,331
12-04-26 조회 3,492