데이터베이스 질문과 답변
게시판 테이블
CREATE TABLE CMS_BOARD(
cms_bno INT AUTO_INCREMENT PRIMARY key
, cms_title VARCHAR(1000)
, cms_content VARCHAR(1000)
, cms_writer VARCHAR(1000)
, cms_regdate DATETIME DEFAULT CURRENT_TIMESTAMP
);
답글 테이블
CREATE TABLE CMS_REPLY (
cms_bno INT NOT NULL (프라이머리, 포린키 줬습니다)
, cms_rno INT AUTO_INCREMENT PRIMARY KEY NOT NULL
, cms_title VARCHAR(1000) NOT NULL
, cms_content VARCHAR(1000) NOT NULL
, cms_writer VARCHAR(1000) NOT NULL
, cms_regdate DATE DEFAULT CURRENT_TIMESTAMP
);
두개의 테이블에 글을 등록하고
cms_bno 번호에 맞춰서 답변 글까지 리스트를 조회 하려면 쿼리를 어떻게 짜야 하는지 도와주세요...
by 동동동
[2021.04.23 16:34:59]
WITH CMS_BOARD AS (
SELECT '1' AS CMS_BNO, '게시글1' AS CMS_TITLE FROM DUAL UNION ALL
SELECT '2' AS CMS_BNO, '게시글2' AS CMS_TITLE FROM DUAL
)
,
CMS_REPLY AS (
SELECT '1' AS CMS_BNO, '1-1' CMS_RNO, '게시글1 답글1' AS CMS_TITLE FROM DUAL UNION ALL
SELECT '1' AS CMS_BNO, '1-2' CMS_RNO, '게시글1 답글2' AS CMS_TITLE FROM DUAL
)
SELECT CMS_BNO
, UPRN_CMS_BNO
, LPAD(' ',4*(LEVEL-1))||CMS_TITLE
FROM (
SELECT CMS_BNO, '0' UPRN_CMS_BNO, CMS_TITLE FROM CMS_BOARD A
UNION ALL
SELECT CMS_RNO, CMS_BNO, CMS_TITLE FROM CMS_REPLY B
)
START WITH UPRN_CMS_BNO = '0'
CONNECT BY NOCYCLE PRIOR CMS_BNO = UPRN_CMS_BNO
OR PRIOR UPRN_CMS_BNO = CMS_BNO
ORDER SIBLINGS BY CMS_BNO;
by 뉴비디비
[2021.04.23 21:54:33]
-- MySQL
WITH CMS_BOARD AS (
SELECT '1' AS CMS_BNO, '게시글1' AS CMS_TITLE UNION ALL
SELECT '2' AS CMS_BNO, '게시글2' AS CMS_TITLE UNION ALL
SELECT '3' AS CMS_BNO, '게시글3' AS CMS_TITLE
), CMS_REPLY AS (
SELECT '1' AS CMS_BNO, '1' CMS_RNO, '게시글1 답글1' AS CMS_TITLE UNION ALL
SELECT '1' AS CMS_BNO, '2' CMS_RNO, '게시글1 답글2' AS CMS_TITLE UNION ALL
SELECT '3' AS CMS_BNO, '3' CMS_RNO, '게시글3 답글1' AS CMS_TITLE UNION ALL
SELECT '2' AS CMS_BNO, '4' CMS_RNO, '게시글2 답글1' AS CMS_TITLE
)
SELECT
CMS_BNO, CMS_RNO
, CONCAT( LPAD('', CASE WHEN CMS_RNO < 1 THEN 0 ELSE 2 END, ' ') , CMS_TITLE ) AS CMS_TITLE
FROM (
SELECT CMS_BNO, 0 AS CMS_RNO, CMS_TITLE FROM CMS_BOARD UNION ALL
SELECT CMS_BNO, CMS_RNO, CMS_TITLE FROM CMS_REPLY
) AAA
ORDER BY CMS_BNO, CMS_RNO
) 버튼을 클릭하여 작성 하시면 됩니다.