http://blog.naver.com/cinqueterre/50009852060
/*---------------------------------------------------
* ROW => COLUMN의 변환
* COLUMN => ROW의 변환
----------------------------------------------------*/
---------------
DEPTNO EMPNO
---------------
10 7782
10 7839
10 7934
20 7369
20 7566
20 7788
30 7499
30 7521
30 7654
------------------------------
DEPTNO EMP1 EMP2 EMP3
------------------------------
10 7782 7839 7934
20 7369 7566 7788
30 7499 7521 7654
/* COLUMN => ROW 시작 */
SELECT A.DEPTNO,
DECODE(C.NO, 1, A.EMP1,
2, A.EMP2,
3, A.EMP3) EMPNO
FROM
(
/* ROW => COLUMN 시작 */
SELECT DEPTNO,
MAX(DECODE(RID, 1, EMPNO)) EMP1,
MAX(DECODE(RID, 2, EMPNO)) EMP2,
MAX(DECODE(RID, 3, EMPNO)) EMP3
FROM (
SELECT DEPTNO,
ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY EMPNO) RID,
EMPNO
FROM EMP
)
GROUP BY DEPTNO
/* ROW => COLUMN 종료 */
) A, COPY_T C
WHERE C.NO <= 3
/* COLUMN => ROW 종료 */
-------------------------------------------------------------------
http://energ.tistory.com/entry/Using-the-DECODE-Function
*DECODE ( 리턴값, 지정된검색값, 결과값, 디폴트값)
리턴값이 지정된검색값과 같은 경우, 특정 결과값을 ,
그렇지 않은경우, 디폴트값을 출력해주는 함수.
=> PL/SQL없이 IF-THEN-ELSE로직구현.
Case문에 비해 depth가 깊어지면 성능이 떨어지니, CASE()와 성능비교필요.
SELECT txt
FROM table
ORDER BY DECODE(txt,
'서울', 1,
'경기',2,
'인천',3,
4)
SELECT txt
FROM table
ORDER BY CASE txt
WHEN '서울' THEN 1
WHEN '경기' THEN 2
WHEN '인천' THEN 3
ELSE 4
END
-------------------------------------------------------------------
ROW_NUMBER 분석함수
http://www.statwith.pe.kr/ORACLE/functions122.htm
Oracle의 그룹별 번호 매기기와 같은 기능 구현하기
이 함수는 1로 시작하는 order_by_clause에서 지정된 행의 순위 순서로,
적용되는 각 행에 unique 순서를 할당한다.(파티션에서 각 행 또는 쿼리에 의해서 반환되는 각 행)
지정된 범위에 대하여 ROW_NUMBER값을 검색하는 쿼리 내의 ROW_NUMBER를 이용하는 중첩하는 서브쿼리에 의해, inner 쿼리의 결과로 부터 정확한 행의 부분집합을 얻을수 있다.
이 방법으로 함수를 이용하면, top-N,bottom-N,inner-N 리포트를 실행할 수 있다.
일관된 결과에 대하여, 쿼리는 결정적인 소트 순서를 확보할수 있다.
expr에 대하여 ROW_NUMBER 또는 다른 분석 함수를 이용할수 없다.
중첩 분석 함수를 이용할수 없으나, expr에 대하여 다른 이미있는 함수를 이용할수 있다.
ROW_NUMBER()는 각 PARTITION내에서 ORDER BY절에 의해 정렬된 순서로 유일한 값을 돌려주는 함수로 ROWNUM과는 관계가 없다.
[Oracle]
SELECT empno, ename,
job, sal, ROW_NUMBER() OVER(PARTITION BY job ORDER BY sal) AS rnum
FROM emp;
[MYSQL]
SELECT empno, ename,
job, sal, rnum
FROM (
SELECT a.*,
(CASE @vjob WHEN a.job THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) rnum,
(@vjob:=a.job) vjob
FROM emp a,
(SELECT @vjob:=0, @rownum:=0 FROM DUAL) b
ORDER BY a.job, a.sal
) c;
--------------------------------------
/*---------------------------------------------------
* CROSSTAB에서 열을 행으로 행을 열로 변환
* ROW => COLUMN, COLUMN => ROW을 한꺼번에 구현
----------------------------------------------------*/
------------------------------
DEPTNO EMP1 EMP2 EMP3
------------------------------
10 7782 7839 7934
20 7369 7566 7788
30 7499 7521 7654
------------------------------
EMP DEPT_10 DEPT_20 DEPT_30
------------------------------
EMP1 7782 7369 7499
EMP2 7839 7566 7521
EMP3 7934 7788 7654
SELECT DECODE(C.NO, 1, 'EMP1',
2, 'EMP2',
3, 'EMP3') EMP,
MAX(DECODE(A.DEPTNO||C.NO2, '1001', A.EMP1,
'1002', A.EMP2,
'1003', A.EMP3)) DEPT_10,
MAX(DECODE(A.DEPTNO||C.NO2, '2001', A.EMP1,
'2002', A.EMP2,
'2003', A.EMP3)) DEPT_20,
MAX(DECODE(A.DEPTNO||C.NO2, '3001', A.EMP1,
'3002', A.EMP2,
'3003', A.EMP3)) DEPT_30
FROM
(
/* 원래의 ROW, COLUMN구조 시작 */
SELECT DEPTNO,
MAX(DECODE(RID, 1, EMPNO)) EMP1,
MAX(DECODE(RID, 2, EMPNO)) EMP2,
MAX(DECODE(RID, 3, EMPNO)) EMP3
FROM (
SELECT DEPTNO,
ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY EMPNO) RID,
EMPNO
FROM EMP
)
GROUP BY DEPTNO
/* 원래의 ROW, COLUMN구조 종료 */
) A, COPY_T C
WHERE C.NO <= 3
GROUP BY DECODE(C.NO, 1, 'EMP1',
2, 'EMP2',
3, 'EMP3')
/*---------------------------------------------------
* 참고) COPY_T 의 생성
----------------------------------------------------*/
CREATE TABLE COPY_T
AS
SELECT ROWNUM NO
,TO_CHAR(ROWNUM, 'FM00') NO2
FROM ALL_OBJECTS
WHERE ROWNUM <= 31
CREATE UNIQUE INDEX COPY_T_IDX1 ON COPY_T(NO)
CREATE UNIQUE INDEX COPY_T_IDX2 ON COPY_T(NO2)
/*---------------------------------------------------
* ROW_NUMBER() 함수의 기능을 구현 => 테이블을 두번 읽기
* ROWNUM이 지원되지 않는 DBMS에서 ROWNUM 구현도 유사
----------------------------------------------------*/
SELECT DEPTNO,
ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY EMPNO) RID,
EMPNO
FROM EMP
SELECT A.DEPTNO,
COUNT(*) RID,
A.EMPNO
FROM EMP A, EMP B
WHERE A.DEPTNO = B.DEPTNO /* PARTITION BY 기능 */
AND A.EMPNO >= B.EMPNO /* ORDER BY 기능 => 반드시 UNIQUE 해야함 */
GROUP BY A.DEPTNO, A.EMPNO /* PARTITION BY 기능 */
ORDER BY A.DEPTNO, A.EMPNO /* ORDER BY 기능 */
/*---------------------------------------------------
* COPY_T 테이블이 없을때 COPY_T 기능 구현방법
----------------------------------------------------*/
SELECT NO, NO2
FROM COPY_T
WHERE NO <= 5
/*------------------------------------------
* 1.USER_OBJECTS 테이블의 이용
* 최대한 가벼운 테이블 이용
* USER_OBJECTS가 가벼운지는 검증할 문제임
-------------------------------------------*/
SELECT ROWNUM NO,
TO_CHAR(ROWNUM, 'FM00') NO2
FROM USER_OBJECTS
WHERE ROWNUM <= 5
/*------------------------------------------
* 2.DUAL 테이블의 이용
* 복사갯수가 적을때 이용(2~3개)
-------------------------------------------*/
SELECT NO,
TO_CHAR(NO, 'FM00') NO2
FROM (
SELECT 1 NO FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL
UNION ALL
SELECT 4 FROM DUAL
UNION ALL
SELECT 5 FROM DUAL
)
/*---------------------------------------------------
* 참고) DUAL 테이블이 없는 경우의 구현(SQL SERVER)
----------------------------------------------------*/
CREATE VIEW DUAL
AS
SELECT 'X' DUMMY_COL
SELECT GETDATE() FROM DUAL[출처] [SQL] 행열변환 방법|작성자 맨티스
Use row_number() in MySQL
MySQL lacks this function but you can easily do this using .
오라클>
selectid,name, row_number() over (orderbyareaname)asresult
from city
selectid,name, @i := @i + 1asresult
fromcity,(select@i := 0)temporderbyareaname;
'SugarCRM > MySQL' 카테고리의 다른 글
| myisam innodb 변경 (0) | 2013.05.22 |
|---|---|
| mysql (0) | 2013.05.22 |
| MySQL:: Backup & Recovery (0) | 2013.05.04 |
| PHP Mysql 기본 (0) | 2012.11.07 |
| [퍼옴] mysql 조인(Join)에 대하여 (0) | 2012.09.22 |
| 각 DB 별 테이블 복사 쿼리 구문 MS-SQL (0) | 2012.08.26 |