숀패밀리

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

· 2011-12-17 (토) 11:20:05 · 4279
-- 카테고리 테이블을 작성한다.
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건
+
제목 글쓴이 날짜 조회
12-01-21 조회 3,364
12-01-21 조회 4,156
12-01-21 조회 3,556
12-01-21 조회 3,228
12-01-20 조회 3,120
12-01-20 조회 2,762
12-01-20 조회 3,516
12-01-20 조회 3,350
12-01-20 조회 2,737
12-01-20 조회 3,180
12-01-20 조회 6,266
12-01-19 조회 3,297
12-01-19 조회 4,953
12-01-19 조회 3,228
12-01-19 조회 3,233
12-01-19 조회 3,190
12-01-19 조회 2,815
12-01-18 조회 3,752
12-01-18 조회 2,965
12-01-18 조회 2,710
12-01-18 조회 3,279
12-01-18 조회 2,933
12-01-18 조회 3,125
12-01-17 조회 2,919
12-01-17 조회 4,010
12-01-17 조회 4,443
12-01-17 조회 3,297
12-01-17 조회 3,412
12-01-17 조회 3,166
12-01-17 조회 5,238
12-01-17 조회 3,031
12-01-16 조회 2,768
12-01-16 조회 3,620
12-01-16 조회 4,110
12-01-15 조회 2,915
12-01-15 조회 3,733
12-01-15 조회 3,691
12-01-14 조회 3,580
12-01-14 조회 3,727
12-01-14 조회 3,459
12-01-14 조회 1.1만
12-01-14 조회 3,673
12-01-14 조회 3,992
12-01-14 조회 2,946
12-01-14 조회 2,791
12-01-13 조회 2,933
12-01-13 조회 3,517
12-01-13 조회 4,583
12-01-13 조회 2,927
12-01-12 조회 4,237
12-01-12 조회 4,375
12-01-12 조회 3,387
12-01-12 조회 3,211
12-01-11 조회 2,801
12-01-11 조회 3,248
12-01-11 조회 3,460
12-01-11 조회 3,179
12-01-11 조회 3,189
12-01-11 조회 3,294
12-01-11 조회 3,505
12-01-11 조회 4,285
12-01-11 조회 3,370
12-01-11 조회 2,827
12-01-11 조회 1.2만
12-01-11 조회 3,151
12-01-10 조회 2,963
12-01-10 조회 3,677
12-01-10 조회 4,743
12-01-10 조회 3,435
12-01-10 조회 3,148
12-01-10 조회 2,889
12-01-10 조회 3,799
12-01-10 조회 3,062
12-01-10 조회 4,578
12-01-10 조회 2,954
12-01-10 조회 2,997
12-01-10 조회 3,190
12-01-10 조회 3,823
12-01-10 조회 2,889
12-01-10 조회 4,521
12-01-10 조회 2,905
12-01-10 조회 5,377
12-01-10 조회 5,565
12-01-10 조회 5,442
12-01-10 조회 5,341
12-01-09 조회 2,861
12-01-09 조회 2,786
12-01-09 조회 3,319
12-01-09 조회 3,841
12-01-09 조회 2,794
12-01-09 조회 2,957
12-01-09 조회 2,757
12-01-08 조회 7,098
12-01-08 조회 2,773
12-01-08 조회 7,184
12-01-08 조회 3,708
12-01-08 조회 3,306
12-01-08 조회 2,709
12-01-08 조회 2,864
12-01-08 조회 3,499