You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
bf/doc/create_sql_comment.txt

107 lines
4.0 KiB

DROP TABLE IF EXISTS `babyfeed`.`tbl_blog_comment` ;
CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_blog_comment` (
`bc_id` INT NOT NULL AUTO_INCREMENT,
`p_bc_id` INT NOT NULL DEFAULT 0,
`b_no` INT NOT NULL DEFAULT 0,
`title` VARCHAR(300) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
`reg_dt` DATETIME NOT NULL,
`member_id` VARCHAR(30) NOT NULL,
PRIMARY KEY (`bc_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 1;
DROP FUNCTION IF EXISTS fn_b_comment;
DELIMITER $$
CREATE FUNCTION fn_b_comment(v_b_no INT, s_p_bc_id INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE v_bc_id INT;
DECLARE v_p_bc_id INT;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @bc_id = NULL;
SET v_p_bc_id = @bc_id;
SET v_bc_id = -1;
IF @bc_id IS NULL THEN
RETURN NULL;
END IF;
FIND:LOOP
SELECT MIN(bc_id) INTO @bc_id
FROM tbl_blog_comment
WHERE p_bc_id = v_p_bc_id
AND bc_id > v_bc_id
AND b_no = v_b_no;
IF (@bc_id IS NOT NULL) OR (v_p_bc_id = @start_with) THEN
SET @level = @level + 1;
RETURN @bc_id;
END IF;
SET @level := @level - 1;
SELECT bc_id, p_bc_id INTO v_bc_id, v_p_bc_id
FROM tbl_blog_comment
WHERE bc_id = v_p_bc_id
AND b_no = v_b_no;
END LOOP;
END
$$
DELIMITER ;
select fn_b_comment(2)
select * from tbl_blog_comment
insert into tbl_blog_comment(b_no, bc_id, title, reg_dt, member_id) values (1, 1,'안녕1', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, title, reg_dt, member_id) values (1, 2,'안녕2', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, title, reg_dt, member_id) values (1, 3,'안녕3', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (1,4,2, '안녕2-1', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (1,5,2, '안녕2-2', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (1,6,4, '안녕2-4-1', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (1,7,3, '안녕2-2-3', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (1,8,3, '안녕3-1', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (1,9,7, '안녕3-7-1', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, title, reg_dt, member_id) values (2, 10,'글 2번', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (2,11,10, '독-10-1', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (2,12,10, '독-10-2', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (2,13,10, '독-10-3', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (2,14,12, '독-10-12-1', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, p_bc_id,title, reg_dt, member_id) values (2,15,14, '독-10-12-14-1', now(), 'renamaestro');
insert into tbl_blog_comment(b_no, bc_id, title, reg_dt, member_id) values (6,16,'고흐의 별이 빛나는 밤에', now(), 'renamaestro');
SELECT
CASE WHEN LEVEL-1 > 0
THEN CONCAT(CONCAT(REPEAT(' ', level - 1),'┗'), bc.title)
ELSE bc.title END AS title_step
, bc.title
, bc.b_no
, bc.bc_id
, bc.p_bc_id
, result.level
FROM (
SELECT fn_b_comment(1,3) AS bc_id, @level AS level, bc.b_no
FROM (
SELECT @start_with:=3, @bc_id:=@start_with, @level:=0
) tbl
JOIN tbl_blog_comment bc ON bc.b_no = 1
) result
LEFT OUTER JOIN tbl_blog_comment bc ON bc.bc_id = result.bc_id
WHERE bc.bc_id IS NOT NULL