숀패밀리

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

· 2011-12-17 (토) 11:20:05 · 4276
-- 카테고리 테이블을 작성한다.
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건
+
제목 글쓴이 날짜 조회
11-09-19 조회 2,595
11-09-19 조회 2,519
11-09-19 조회 5,535
11-09-19 조회 6,175
11-09-16 조회 2,885
11-09-16 조회 3,154
11-09-16 조회 6,350
11-09-15 조회 3,116
11-09-15 조회 2,879
11-09-15 조회 2,549
11-09-15 조회 2,544
11-09-15 조회 2,748
11-09-15 조회 2,703
11-09-15 조회 2,823
11-09-14 조회 2,732
11-09-14 조회 3,291
11-09-14 조회 2,401
11-09-14 조회 2,326
11-09-14 조회 2,315
11-09-13 조회 2,496
11-09-10 조회 2,968
11-09-10 조회 2,584
11-09-09 조회 3,169
11-09-09 조회 3,036
11-09-09 조회 3,130
11-09-09 조회 3,163
11-09-08 조회 3,848
11-09-08 조회 5,042
11-09-08 조회 3,440
11-09-08 조회 4,370
11-09-08 조회 3,873
11-09-08 조회 3,677
11-09-08 조회 2,772
11-09-08 조회 2,344
11-09-08 조회 2,748
11-09-08 조회 2,869
11-09-07 조회 3,531
11-09-07 조회 3,591
11-09-07 조회 3,013
11-09-07 조회 2,714
11-09-07 조회 2,808
11-09-07 조회 2,833
11-09-07 조회 3,066
11-09-07 조회 2,335
11-09-07 조회 2,432
11-09-07 조회 2,595
11-09-07 조회 3,534
11-09-07 조회 2,806
11-09-06 조회 2,631
11-09-06 조회 2,919
11-09-06 조회 2,481
11-09-05 조회 2,697
11-09-05 조회 2,643
11-09-05 조회 2,925
11-09-05 조회 2,438
11-09-04 조회 2,409
11-09-04 조회 2,372
11-09-03 조회 2,547
11-09-03 조회 2,705
11-09-02 조회 3,427
11-09-02 조회 2,803
11-09-02 조회 2,670
11-09-02 조회 3,581
11-09-02 조회 2,610
11-09-02 조회 2,498
11-09-02 조회 2,496
11-09-01 조회 2,697
11-09-01 조회 2,846
11-09-01 조회 2,719
11-09-01 조회 2,727
11-09-01 조회 2,870
11-09-01 조회 2,731
11-09-01 조회 4,171
11-09-01 조회 3,773
11-09-01 조회 4,443
11-08-31 조회 2,326
11-08-31 조회 2,631
11-08-31 조회 2,915
11-08-31 조회 2,633
11-08-31 조회 2,488
11-08-31 조회 3,300
11-08-30 조회 3,036
11-08-30 조회 4,267
11-08-29 조회 3,026
11-08-29 조회 2,609
11-08-29 조회 5,244
11-08-29 조회 3,370
11-08-29 조회 2,764
11-08-29 조회 3,116
11-08-28 조회 2,509
11-08-28 조회 3,661
11-08-28 조회 2,622
11-08-28 조회 2,987
11-08-26 조회 2,517
11-08-26 조회 3,024
11-08-26 조회 3,261
11-08-26 조회 2,759
11-08-25 조회 2,594
11-08-25 조회 2,907
11-08-25 조회 2,629