Tag: SQL

한컬럼의 값을 다른 컬럼으로 복사하기

데이터 이동 방향
dept_name = dept_code

create query

create table department(
  dept_id bigint,
  dept_code varchar(100),
  dept_name varchar(100)
)

update query

--가장쉬운방법
update department a
set dept_name = b.dept_code
from department b
where a.dept_id = b.dept_id
-- 조인을 이용
update department a
set dept_name = c.dept_code
from department b,
(
  select dept_id, dept_code from department
) c where a.dept_id = c.dept_id
-- 명시적인 inner join 이용... 은 잘 안된다.

한 테이블을 값을 이용해서 다른테이블 업데이트하기

셀렉트 쿼리처럼 해서 사용하면된다.

update user t1
set phone=t2.phone, address=t2.level
from 
	employee t2
where
	t1.email = t2.email

다른테이블의 값을 조인해서 다른 테이블 업데이트하기

update user t1
set phone=t2.phone, address=t2.level
from 
	employee t2,
	seller t3
where
	t1.email = t2.email and
	t2.email = t3.email

밑에서부터시작…

작업툴 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