게시판 댓글개수 보이는 쿼리 짜는 과정 연습장
밑에서부터시작…
작업툴 pgadmin, 작업대상db : postgresql, 만들어야할 것 : 게시판 목록에서 댓글 개수 보이게 하는 쿼리
오랜만에 만지니 쿼리를 어떻게 만들어야할지 도무지 생각이 나지 않아서 검색시작…
레프트 조인문마저 생각이 안남..;;;
실행순서 정하기
페이징을 할 때 인덱스를 타고 스캔해서 코스트를 최소화하기 위해 나름대로 생각했는데 잘 되는건지 모르겠다. 데이터 누적되도 앞쪽부터 조회하게 돼 있으니까 별 문제는 없을것같다.
SELECT b.board_seq AS boardSeq, b.username, b.title, b.content, b.ip, b.reg_date AS regDate, b.read_count AS readCount, b.recommend_count AS recommendCount, b.last_modified_date AS lastModifiedDate, b.parent_seq AS parentSeq, b.depth, c.n_cmt AS nCmt FROM board b LEFT OUTER JOIN (SELECT c.board_seq, count(c.board_seq) AS n_cmt FROM board_comments c WHERE c.board_seq BETWEEN 0 AND 10 GROUP BY c.board_seq) AS c ON (c.board_seq = b.board_seq) WHERE b.board_seq BETWEEN 0 AND 10
( SELECT b.board_seq AS boardSeq, b.username, b.title, b.content, b.ip, b.reg_date AS regDate, b.read_count AS readCount, b.recommend_count AS recommendCount, b.last_modified_date AS lastModifiedDate, b.parent_seq AS parentSeq, b.depth, COUNT(board_comments.comment_seq) over () AS comment__count FROM board b LEFT OUTER JOIN board_comments ON (b.board_seq = board_comments.board_seq) GROUP BY b.board_seq, board_comments.comment_seq ORDER BY b.board_seq )
SELECT b.board_seq AS boardSeq, b.username, b.title, b.content, b.ip, b.reg_date AS regDate, b.read_count AS readCount, b.recommend_count AS recommendCount, b.last_modified_date AS lastModifiedDate, b.parent_seq AS parentSeq, b.depth FROM board b LEFT OUTER JOIN board_comments ON (b.board_seq = board_comments.board_seq) GROUP BY b.board_seq ORDER BY b.board_seq (SELECT b.board_seq AS boardSeq, b.username, b.title, b.content, b.ip, b.reg_date AS regDate, b.read_count AS readCount, b.recommend_count AS recommendCount, b.last_modified_date AS lastModifiedDate, b.parent_seq AS parentSeq, b.depth –COUNT(cmt.comment_seq) over () AS comment__count FROM board b, (SELECT * FROM board_comments WHERE board_seq BETWEEN 0 AND 10) cmt WHERE b.board_seq = cmt.board_seq
LIMIT 10)
select * from board; select * from board_comments
SELECT b.board_seq AS boardSeq, b.username, b.title, b.content, b.ip, b.reg_date AS regDate, b.read_count AS readCount, b.recommend_count AS recommendCount, b.last_modified_date AS lastModifiedDate, b.parent_seq AS parentSeq, b.depth, COUNT(board_comments.comment_seq) over () AS comment__count FROM board b LEFT OUTER JOIN board_comments ON (b.board_seq = board_comments.board_seq) LIMIT 10
(퍼온쿼리)
SELECT articles.id, articles.datestamp, articles.title, articles.shorttitle, articles.description, articles.markdown, articles.body, articles.idxfti, articles.published, articles.type, COUNT(comments.id) over () AS comment__count FROM articles LEFT OUTER JOIN comments ON (articles.id = comments.article_id) WHERE (articles.type = ‘NEWS’) ORDER BY articles.datestamp DESC LIMIT 1