본문 바로가기

프로그래밍/DBMS40

MYSQL 그룹별 RANK함수 구현하기 요구사항 2020년1월~2021년 현재까지 주문상태는 결제완료 상점코드별 월단위 판매금액 순 100위까지의 고객의 정보를 추출 판매금액이 동일할 경우 같은 등수로 처리 추출정보 : 상점코드, 년월, 고객명, 판매금액, 판매수량 환경 : AWS ARORA DB 문제 : RANK() 함수가 지원되지 않아 직접 구현해야 함 SELECT * FROM ( SELECT A.* , CASE @SHOP WHEN SHOP_NO THEN CASE @MON WHEN MON THEN IF(@SALE_AMT > SALE_AMT, @RANK := @RANK + 1, @RANK) ELSE @RANK := 1 END ELSE @RANK := 1 END AS RNK , @SHOP := SHOP_NO , @MON := MON , @SA.. 2021. 3. 16.
Mysql 칼렌더 덤프 데이터 만들기 Mysql 용 일자별 데이터 생성하는 덤프 데이터 만들기 select * from (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select .. 2021. 2. 17.
MSSQL 모든 상위 부서 찾기 프로시저 사원코드로 최상위 부서까지 모든 상위 부서를 구해야 할 때 사용한다. 기본적인 하이라키 구조 예제는 많은데역순 하이라키 구조 예제가 별로 없어서 기록을 남겨둠 SOME_TABLE 이라는 가상의 테이블을 대상으로 하였다. 주요 컬럼은 ID, P_ID, CODE 이며 ID, P_ID 는 부모/자식의 하이라키 구조 핵심CODE 는 사원코드/부서코드 중 하나이다. 즉, SOME_TABLE은 부서와 사원 정보가 같이 들어 있는 테이블이다. 부서 코드만 있는 구조라면 WHERE CODE = @P_CODE 부분을 적절히 바꿔주면 되겠다. IF OBJECT_ID('SP_PROC') IS NOT NULLDROP PROC SP_PROCGO CREATE PROC SP_PROC(@P_CODE VARCHAR(32))ASBEGI.. 2018. 5. 7.
mssql log 파일 축소 USE DBName ALTER DATABASE DBNameSET RECOVERY SIMPLE; DBCC SHRINKFILE (DBName_log, 10); ALTER DATABASE DBNameSET RECOVERY FULL; 2018. 3. 27.
MSSQL 클러스터드 인덱스 생성 스크립트 스크립트로 인덱스 생성하기 USE AdventureWorks2012; GO -- Create a new table with three columns. CREATE TABLE dbo.TestTable (TestCol1 int NOT NULL, TestCol2 nchar(10) NULL, TestCol3 nvarchar(50) NULL); GO -- Create a clustered index called IX_TestTable_TestCol1 -- on the dbo.TestTable table using the TestCol1 column. CREATE CLUSTERED INDEX IX_TestTable_TestCol1 ON dbo.TestTable (TestCol1); GO 2018. 2. 2.
[MSSQL] Create Table 테이블 생성 템플릿 양식 IF OBJECT_ID('.', 'U') IS NOT NULL DROP TABLE .GO CREATE TABLE .( , , , CONSTRAINT PRIMARY KEY ())GO 2017. 10. 31.
[MSSQL] 컬럼 추가 템플릿, Add Column --==========================================================================-- Add column template---- This template creates a table, then it adds a new column to the table.--==========================================================================USE GO IF OBJECT_ID('.', 'U') IS NOT NULL DROP TABLE .GO CREATE TABLE .(column1 int NOT NULL, column2 char(10) NULL)GO -- Add a new column to the tab.. 2017. 10. 19.
MSSQL 테이블 사용하는 프로시저 찾기 CREATE PROC FIND_PROC@TB_NAME NVARCHAR(100) = NULLASBEGINIF @TB_NAME IS NULLRETURN SELECT B.* FROM SYS.SYSCOMMENTS A, SYS.ALL_OBJECTS BWHERE TEXT LIKE '%' + @TB_NAME + '%'AND A.ID = B.OBJECT_ID ENDGO 사용법 FIND_PROC TABLE_NAME 또는 도구 > 옵션 > 환경 > 키보드 > 쿼리 바로가기 에 등록하여 단축키로 사용하기 (등록 후 새쿼리 창에서 실행해야 함) 2017. 9. 21.
Oracle 연결시에 BadImageFormatException 32비트 문제 발생 시 win10 64비트비주얼 스튜디오 2012OracleClient.dll 참조 후 개발 시 나오는 문제이다. "32비트 Oracle 클라이언트 구성 요소가 설치된 상태에서 64비트 모드로 실행할 경우 이러한 문제가 발생합니다" 구글링을 하면 여러가지 해결법이 나오는데.. 다 안된다.. 특히 Any Cpu를 X86으로 바꿔보라는 거~~ 안된다. 아래와 같이 처리해야 함IIS 오픈 -> 응용 프로그램 풀 -> 해당 도메인 선택 -> 고급설정 -> 32비트 응용프로그램 사용 : False 로 수정로 하면 잘 됨 2016. 11. 22.
오라클 IF.. ELSEIF 문 쓰다가 맨붕옴 ㅠㅠ 오라클에서 IF 분기를 했다.조건이 여러가지 있었다.그래서 ELSEIF 를 사용했다.에러 빠바박~ ELSE IF 인가 하고 띄어쓰기도 해봤다. 아니다 붙여쓰는게 맞다.그러다 찾았다.ELSEIF 가 아니고ELSIF 였다. 'E' 가 없는 것이다.ㅎㅎㅎㅎㅎㅎㅎㅎ 주의하시길~ 2016. 1. 28.
오라클 SELECT .. INTO 시 결과 없을 때 처리 방법 오라클 프로시져 작성 중에 변수에 데이터를 담아 처리중에 발생한 오류였다.값이 없을수도 있는 경우를 대비해 예외 처리를 하고 아래 로직이 실행되어야 해서 고민하다 알게 된 방법.물론 구글느님께서 검색해 주셨다. SELECT .. INTO 시 NO DATA FOUND 예외인 경우 아래와 같이 처리한다. CREATE OR REPLACE PROCEDURE PROC_TEST IS BEGIN{ 다른 처리 내용....}BEGIN SELECT COL1 INTO V_COL1 FROM TB_TMP WHERE COND1 = ''EXCEPTION WHEN NO_DATA_FOUND THEN V_COL1 := NULL; IF V_COL1 IS NOT NULL THEN ..프로세스 처리 END IF;END; 아래 다른 로직 처리.. 2016. 1. 28.
MSSQL TRY CATCH 와 커서 (ERROR 16915) MSSQL에서 오라클로 데이터를 밀어넣는 작업중이었다.MSSQL 프로시져에서 트랜잭션을 유지하려고 TRY... CATCH문을 이용했는데...계속 '커서가 이미 있다'는 에러가 떨어지는 것이었다.커서 사용 문법을 한 백번 정도 쳐다봤는데 이상 없었다. 아무리봐도 맞게 썼다. 그러다 찾았다.이 문제는 이게 어플리케이션(C#) 프로그램이었으면 금방 찾았을 것이다. 어플리케이션에서 try catch시 catch문에 들어가면 오브젝트들 자원해제를 해준다. finally에서 하기도 하고..그것 때문이었다. 열려진 커서가 예외상황이 발생하면서 오픈되채로 catch문으로 들어가 버려서 나는 에러였다.아~~그래서 TRY CATCH문에 아래와 같이 처리했다. BEGIN TRY DECLARE CURSOR CUR FOR .... 2014. 12. 9.
ROLLUP 총계만 필요할 때 ROLLUP을 사용하면 소계, 총계가 나온다.그중 총계만 필요할 때 아래 처럼 사용한다.SELECT ISNULL(COL1, '') COL1 ,ISNULL(COL2, '총계') COL2 ,COUNT(COL2) COLCNT FROM TBL_ROOT A GROUP BY ROLLUP(COL1, COL2) HAVING GROUPING(COL1) = 1 OR GROUPING(COL2)=0 위처럼 사용할 수도 있지만 아래처럼 사용해도 된다.SELECT ISNULL(COL1, '') COL1 ,ISNULL(COL2, '총계') COL2 ,COUNT(COL2) COLCNT FROM TBL_ROOT A GROUP BY ROLLUP( ( COL1, COL2 ) ) 아래 쿼리처럼 괄호를 한번 더 싸주면 된다. 2014. 12. 3.
MSSQL 테이블 목록 조회 오라클 테이블 조회는SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE '%테이블명%' MSSQL 테이블 조회는 SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%테이블명%' 2014. 12. 3.
오라클 WITH문과 CREATE TABLE 쓰기 with문을 쓰다가 create table 할려고 보니 한방에 되지 않음.그래서 조금 고민하다가 구글검색하니 역시 오버플로우 닷컴에 답이 있었음.짱임. CREATE TABLE TBL_TEMP AS WITH T_ROOT AS ( SELECT '1' COL1 FROM DUAL ) SELECT * FROM T_ROOT 위와 같이 CREATE TABLE을 최상위에 놓아야 됨 2014. 11. 24.
MSSQL varchar 데이터 형식을 datetime 데이터 형식으로 변환하는 중 값 범위를 벗어났습니다. 쿼리를 작성중에 날짜 비교가 있었다."varchar 데이터 형식을 datetime 데이터 형식으로 변환하는 중 값 범위를 벗어났습니다." 그런데 이런 에러메시지가 출력되었다. 아주 간단한 문장이었는데.DECLARE @MINDT CHAR(10)SET @MINDT = '2014-04-31'위의 @MINDT를 비교문에 쓰면 에러가 나는 것이었다.에러가 난 쿼리는 아래와 같다.SELECT * FROM TABLE_A WHERE CREATEDATE > @MINDT아주 간단한 쿼리였는데 문제를 잡지 못했다. 그러다가 유레카~2014년 4월의 마지막날이 30일이었기 때문이었다.SET @MINDT = '2014-04-30'위와 같이 값을 바꾸니 해결되었다. CREATEDATE 가 DATETIME인데 문자열과 비교하려고 .. 2014. 8. 12.
MSSQL 누적합 구하기 매장별 월 누적 합계가 필요해서 찾아봤더니 아래 같이 셀프조인으로 처리하는 방식이 있어 기재한다. 마트 구성이나 집계 화면 개발 시 자주 사용하니 기억할 것. CTE 쿼리 때문에 mssql 2005 버전이상 부터 사용가능하다.하위 버젼은 임시테이블 또는 커서를 이용해서 해야 하니 다른 자료를 참조해야 한다. WITH ADDSUM AS(selecta.prodkey --상품코드,prodDt --yyyy-mm-dd,sum(a.TotalAmt) sumAmt --판매금액,ROW_NUMBER() OVER(PARTITION BY A.prodkey ORDER BY A.prodkey, pordDt) ROfrom salesmt a --판매테이블where 1=1and a.createdate between '2011-05-0.. 2014. 7. 15.
MSSQL 피벗 사용하기 집계 쿼리 시 빈번하게 사용되는 피벗 쿼리MSSQL에서 피벗은 2005버전 이상부터 사용 가능하다. 하위 버전은 case when으로 구현해야 한다. 동적 피벗을 만들어서 사용하기도 하니 알아두도록 하자. -- Pivot table with one row and five columns SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4] FROM (SELECT DaysToManufacture, StandardCost FROM Production.Product) AS SourceTable PIVOT ( AVG(StandardCost) FOR DaysToManufacture IN ([0], [1], [2], [3], [.. 2014. 7. 10.
SQL 테이블 목록 확인 및 특정 컬럼 포함 테이블 보기 테이블을 검색하고 싶을 때 [MSSQL]SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%테이블%' [ORACLE]SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE '%대소문자 가림%' 특정컬럼을 포함한 모든 테이블 확인 [MSSQL]--필요한 컬럼만 SELECT 하면 됨 --X_TYPE에 따라 테이블, 뷰 구분할 수 있음SELECT * FROM SYS.all_columns A INNER JOIN SYSOBJECTS B ON A.object_id = B.ID WHERE A.NAME = '컬럼명' [ORACLE]SELECT * FROM COLS WHERE COLUMN_NAME = '컬럼명' 2014. 6. 23.
MSSQL 1~100 까지 더미 테이블 생성하고 싶을 때 오라클에서는 1~100까지 더미 테이블을 생성할 때 아래 같이 한다. SELECT LEVEL FROM DUAL CONNECT BY LEVEL 2014. 6. 9.

티스토리툴바