본문 바로가기

SQL

LEFT OUTER JOIN을 이용한 컬럼 합치기(?) SELECT * FROM(SELECT '10:00' AS DT, 'A' AS GUBUN FROM DUALUNION ALLSELECT '11:00' AS DT, 'A' AS GUBUN FROM DUALUNION ALLSELECT '14:00' AS DT, 'A' AS GUBUN FROM DUALUNION ALLSELECT '15:00' AS DT, 'A' AS GUBUN FROM DUALUNION ALLSELECT '16:00' AS DT, 'A' AS GUBUN FROM DUAL) ALEFT OUTER JOIN(SELECT 'B' AS gubun2, 'C' AS gubun3 FROM dual) BON 1=1 결과DT | GUBUN | GUBUN2 | GUBUN3 10:00ABC11:00ABC14:00AB.. 더보기
oracle listagg 이용한 union 항목들 구분하기 SELECTDT, MAX(GUBUN), LISTAGG(GUBUN,',') WITHIN GROUP(ORDER BY GUBUN) as GUBUN2FROM(SELECT A.* FROM(SELECT '10:00' AS DT, 'A' AS GUBUN FROM DUALUNION ALLSELECT '11:00' AS DT, 'A' AS GUBUN FROM DUALUNION ALLSELECT '14:00' AS DT, 'A' AS GUBUN FROM DUALUNION ALLSELECT '15:00' AS DT, 'A' AS GUBUN FROM DUALUNION ALLSELECT '16:00' AS DT, 'A' AS GUBUN FROM DUAL) AUNION ALLSELECTB.*FROM(SELECT '12:00' A.. 더보기
[비공개] Oracle 대용량 insert -- 대용량 INSERTDECLARETYPE TBL_INS IS TABLE OF [테이블명]%ROWTYPE INDEX BY BINARY_INTEGER; W_INS TBL_INS;BEGINFOR I IN 1 .. 10 LOOP W_INS(I).[컬럼명]:= I; W_INS(I).[컬럼명] := SYSDATE - (interval '1' second)*I; ...이런식으로 컬럼에 값 매핑... END LOOP;FORALL I IN 1 .. 10 INSERT INTO [테이블명] VALUES W_INS(I); COMMIT;END; 오라클 레벨업에서 보고 사용해봤습니다.효과는 내일 더 해본 후에 공개전환하는것으로! 더보기
Oracle dynamic date 오라클 동적 시간 만들기 SELECT level AS id, current_timestamp - interval '1' second*level,SYSDATE - (interval '1' second)*levelFROM dual CONNECT BY level 더보기
IINNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN 요즘 너무 글을 안올려서 간만에 포스팅합니다.쌩뚱맞지만 갑자기 생각나서.. 급히 짜본 쿼리;;;어려운 내용은 아니지만 기본기를 다진다는 차원에서 ^^ SELECT T1.PK AS T1_PK, T2.PK AS T2_PK, T1.COL1, T2.COL2FROM( SELECT * FROM ( SELECT '1' AS PK, 'A' AS COL1 FROM DUAL UNION ALL SELECT '2' AS PK, 'B' AS COL1 FROM DUAL UNION ALL SELECT '3' AS PK, 'C' AS COL1 FROM DUAL UNION ALL SELECT '5' AS PK, 'E' AS COL1 FROM DUAL ) ) T1 INNER JOIN -- LEFT OUTER JOIN-- RIGHT OU.. 더보기
ORACLE WITH oracle with문.. 요즘 잘 안쓰다보니 사용법을 까먹게 생겨서 기록해놓는다. with A as ( select ...필요한 데이터 A ), B as ( select ... 필요한 데이터 B ), C as ( select ... 필요한 데이터 C )RESULT as (select A.*, B.*, C.*from A, B, Cwhere A.id = B.idand A.id = C.id)select * from RESULT 이런식으로 쓰면 유용하게 써먹을 수 있겠다.. 더보기
ms-sql 재귀쿼리 WITH recursive_query(S_MENU_ID,S_U_MENU_ID,S_MENU_NM,sort,dept_S_MENU_NM) AS (SELECTS_MENU_ID, S_U_MENU_ID, S_MENU_NM, convert(varchar(255), S_MENU_ID) sort, convert(varchar(255),S_MENU_NM) dept_S_MENU_NMFROM TBL_MENU_INFOWHERE S_U_MENU_ID IS NULLUNION ALLSELECTb.S_MENU_ID, b.S_U_MENU_ID, b.S_MENU_NM, convert(varchar(255), c.sort + ' > ' + b.S_MENU_ID) sort, convert(varchar(255),c.dept_S_MENU_NM.. 더보기
MySql에서 날짜 테이블 비슷한거 만들기 MSSQL에는 master..spt_values라는게 있어서 날짜를 뽑을 수 있다.MySql에는 이런 게 없어서.. 어떻게 할까 고민하다가 구글링 중 발견한 방법! 1. 일단 테이블이 필요하다. CREATE TABLE TBL_NUMBERS ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`)); 2. 데이터를 넣어준다. 나는 100개 넣었음.. 필요에 따라~! DELIMITER $$ DROP PROCEDURE IF EXISTS FILL_NUMBERS;CREATE PROCEDURE FILL_NUMBERS()BEGIN/* MYSQL에는 MSSQL의 master spt_values 같은 날짜 테이블이 없으므로 NUMBERS 테이블을 기준으로S.. 더보기

티스토리툴바