[SQL] 자주사용되는 쿼리 및 문구

1. 오늘날짜

Oracle	sysdate
MySQL	now()
PostgreSQL	now()

2. 행번호

Oracle	rownum
MySQL	@ROWNUM := @ROWNUM + 1 AS RNUM  @ROWNUM := 0
PostgreSQL	ROW_NUMBER () OVER (ORDER BY 정렬문장)

3. 단독실행

Oracle	select * from dual
MySQL	select 1
PostgreSQL	select 1

4. 문장연결 및 LIKE(예제)

Oracle	like '%' || '' || '%'
MySQL	like CONCAT('%', '', '%')
PostgreSQL	like '%' || '' || '%'  like concat('%', '', '%')

5. NULL 체크, 검토

Oracle	NVL(원문, 'NULL 일경우 대체값')  NVL2(원문, 'NULL 아닌 경우값', 'NULL 인 경우 값')  DECODE(원문, NULL, '매칭시', '비매칭시')  CASE          WHEN 원문 = NULL THEN {NULL 대체 값}         ELSE {NULL 아닌값} END
MySQL	ifnull('원문' , '대체값')  CASE          WHEN 원문 = NULL THEN {NULL 대체 값}         ELSE {NULL 아닌값} END
PostgreSQL	COALESCE(원문, '대체값')  CASE          WHEN 원문 = NULL THEN {NULL 대체 값}         ELSE {NULL 아닌값} END

※ 호환성을 생각하면 CASE... SQL 문이 진리다

6. 페이징 (Paging)

Oracle	SELECT *   FROM   (SELECT ROWNUM AS rnum,                  dual.*           FROM   dual) a   WHERE  rnum >= 1 rnum <= 10
MySQL	정렬문장 다음... LIMIT 시작번호 , 가져올 수  예) LIMIT 0 , 10
PostgreSQL	정렬문장 다음... offset 시작번호 limit 가져올 수  예) offset 0 limit 10

※ 프로그램단 페이징 값 추출은 아래와 같이 (자바기반이지만 결국 다 똑같다)

int page = 5; // 호출페이지

int totalResultCnt = 21; // 전체 게시물 수

int pageSize = 10; // 페이지당 게시물 수

int pageBlock = 10; // 페이징 노출 수

int totalPageCount = totalResultCnt / pageSize + (totalResultCnt % pageSize > 0 ? 1 : 0); // 전체페이지수

if(totalPageCount == 0)

totalPageCount = 1; // 전체페이지가 0 이면 1페이지로 고정

if(page > totalPageCount)

page = totalPageCount; // 페이지 범위가 벗어 날 경우 마지막 페이지로...

// SQL 쿼리수행부

int sqlPageStart = (page - 1) * pageSize + 1; // MySQL, PostgreSQL은 - 1 을 추가로 빼줍니다

int sqlPageEnd = sqlPageStart + pageSize - 1; // Oracle 일경우만 사용(pageBlock 을 쿼리에 삽입)

System.out.format("쿼리시작 :  %s\n", sqlPageStart);

System.out.format("쿼리종료 :  %s\n", sqlPageEnd);

// 화면 노출부

int pageStart = page / pageBlock * pageBlock + 1;

if (page % pageBlock == 0)

pageStart -= pageBlock;

int pageEnd = pageStart + pageBlock - 1;

if (pageEnd > totalPageCount)

pageEnd = totalPageCount;

System.out.format("반복노출시작 :  %s\n", pageStart);

System.out.format("반복노출마지막 :  %s\n", pageEnd);

System.out.format("마지막페이지 :  %s\n", totalPageCount);

PS. MSSQL은 사회생활 1년차 이외에는 12년 경력동안 사용해본적이 없는 듯....