본문 바로가기
반응형

분류 전체보기557

유용한 쿼리 날짜형식이 잘못된 데이터의 경우 날짜형식으로 바꾸는 쿼리 1. 날짜형식인지 체크 (0이 나오면 날짜타입이 아님) ex) select ISDATE('2011-12-15 오후 5:37:35') -- 한글이 들어간 경우 select ISDATE('2011-02-30') -- 날짜범위에 벗어난 경우 -- 날짜 형식이 잘못된 데이터 찾아내기 select * from 테이블 where isdate(날짜컬럼) = 0 2. 날짜타입에 한글이 들어가는 경우 2011-12-15 오후 5:37:35 2011-12-01 오전 9:50:06 ==> 먼저 한글 들어간 부분을 제거하고, 날짜형식으로 바꾼다. ==> 오후에 해당되면 12시간을 더해준다. SELECT case when CHARINDEX('후', wdate,1) > 0.. 2015. 7. 28.
stuff함수 row로 조회되는 특정 컬럼의 데이터를 ',' 등의 구분값으로 구분된 한개의 컬럼으로 조회하기.SELECT STUFF((SELECT ', ' + ENO FROM MM_TEST WHERE REQ_YMD = '20180717' FOR XML PATH('')) ,1,1,'') AS ENO 2015. 7. 28.
SQL 설정 관련 사용자 패스워드 변경 ALTER LOGIN userWITH PASSWORD=N'P@ssw0rd1' --락 걸린거 볼 때 컬럼 BlkBy 이 컬럼이 숫자 일경우 sp_who2 2015. 7. 28.
대용량에서 사용하는 SQL -- 삭제하는 부분 DECLARE @ROWS int SET @ROWS=10000; - 한번에 처리할 개수 DELETE TOP (@ROWS) 테이블명 WHERE삭제할 조건 ; WHILE @@ROWCOUNT = 10000 BEGIN DELETE TOP (@ROWS) 테이블명 삭제할조건 ; END; -- 업데이트 부분 DECLARE @ROWS int SET @ROWS=1000; - 한번에 처리할 개수 UPDATE TOP(@ROWS) 테이블명 SET gb_type ='Y' -- 수정할 부분 WHERE 조건 ; WHILE @@ROWCOUNT = 1000 BEGIN UPDATE TOP(@ROWS) 테이블명 SET gb_type ='Y' WHERE 조건 ; END; ----------------------------.. 2015. 7. 28.
순위함수 ROW_NUMBER() : 행별로 순위를 정함 DENSE_RANK() : 동일 순번일 경우 같은 순위를 줌 RANK() : 동일 순위가 여럿일 경우, 다음 순위에서 중복된 순위만큼 제거(1, 2, 3, 3, 5....) NTILE(2) : 순위별로 나열한 다음, 그룹으로 나눠야 할경우에 씀 숫자 2는 2개로 구분한다는 의미익 5일경우 5개로 N빵함. --order by 로 순위 지정 select ROW_NUMBER() over(order by document_yymm asc, document_ser desc) , * from MASTER_TBL --partition by로 부문별로 순위 지정(순위는 별도로 partition별로 지정됨) select ROW_NUMBER() over( partition by.. 2015. 7. 28.
오라클과 vs. MSSQL 함수비교 파일 참조 (인터넷상 퍼온자료) 2015. 7. 28.
스칼라 펑션(scalar function) 스칼라 펑션 ALTER FUNCTION [dbo].[UF_GetCodeMst] (@Code int) RETURNS varchar(50) WITH EXECUTE AS CALLER AS BEGIN DECLARE @gCodeName varchar(50) SELECT @gCodeName = Codename FROM CodeMst WHERE Code = @Code RETURN(@gCodeName) END; 2015. 7. 28.
[미완] 페이징 쿼리 TOP 세번 사용하기 Select * from ( Select Top (@TOP) * from ( Select Top (@nowPage * @PageSize) * from Table1 order by a desc, date desc ) A Order by aasc, date asc ) B Order By adesc, date DESC 2015. 7. 28.
문자열로 구성된 고유 시퀸스 코드 만들 때 SET @SEQ1 = '' SET @SEQ2 = '' SELECT @SEQ1 = 'W' + CONVERT(CHAR(6), GETDATE(), 112) SELECT @SEQ2= ISNULL(MAX(SUBSTRING(WRITERNUM, 8, 11)), '0000') FROM DBO.Table1 WHERE WRITERNUM like 'W' + @SEQ1 + '%' SELECT @SEQ2 = @SEQ2 + 1 SET @WRITERNUM = @SEQ1 + @SEQ2 W2012060001로 구성되어 뒤에 숫자만 증가 시켜야 할 때, 시퀸스에 해당되는 문자열 만 잘라내어서. 문자열 + 1(숫자)로 하면 자동으로 1을 증가시킨다. 2015. 7. 28.
[오류] 운영 체제 오류 5(액세스가 거부되었습니다.) 운영 체제 오류 5(액세스가 거부되었습니다.) 발생시 해당 MDF파일의 사용자 권한을 로그인한 사용자로 준다. 아래는 검색해서 나온 것. http://blog.naver.com/neosdh?Redirect=Log&logNo=40158574219 2015. 7. 28.
MDF가 손상되었을 경우 ▧ MDF 파일이 손상이 되어(깨졌을 경우) Database 가 정상적으로 작동하지 않을때의 방법 EX)) MDF 파일이 손상이 되어 Attach 작업이 되지 않을 경우 1. 먼저 문제가 된 MDF 파일을 다른 경로로 백업을 해놓는다 그런다음 문제가 된 Database 를 새로 생성한다. 이때 MDF 파일을 문제가 된 MDF 파일의 사이즈와 같게하여 생성한다. 이 경우 LDF 파일은 1MB 로 설정을 한다. 2. SQL SERVICE 종료하고 문제가 된 MDF파일을 새로 생성한 MDF에 덮어씌운다 3. SQL SERVICE 재시작 4. -- 이 경우 해당 DB가 응급모드 상태로 올라올 것이다. --시스템 테이블 값을 업데이트 할 수 있도록 해당 작업을 수행한다 EXEC sp_configure 'allow.. 2015. 7. 28.
내장함수 -- 숫자 함수 -- 값 , 근사치 select ABS(-1); select ABS(8-9); select CEILING(-9.5), CEILING(9.2); --매개변수와 가까운 큰 정수 출력 select FLOOR(-9.5), FLOOR(9.2); -- 매개변수와 가까운 작은 정수 출력 select POWER(2,3); -- 2에 3승을 출력 select RAND() * 1000; -- 0 과 1 사이의 난수 발생 select ROUND(RAND() * 100,0); select ROUND(3.141592, 1),round(3.141592,2),round(3.141592, 3); --(실수 , 소수점반올림자리수) select ROUND(173.141592,0),round(173.141592,-1),r.. 2015. 7. 28.
세로를 가로로 출력하기 SELECT AA.ACCTUNIT_CD, AA.ACCT_CTG_CD , ISNULL(MAX(Case ROWNUMBER When 1 Then AA.MNG_ITEM_CD end),'') MNG_ITEM_CD_1, ISNULL(MAX(Case ROWNUMBER When 2 Then AA.MNG_ITEM_CD end),'') MNG_ITEM_CD_2, ISNULL(MAX(Case ROWNUMBER When 3 Then AA.MNG_ITEM_CD end),'') MNG_ITEM_CD_3, ISNULL(MAX(Case ROWNUMBER When 4 Then AA.MNG_ITEM_CD end),'') MNG_ITEM_CD_4, ISNULL(MAX(Case ROWNUMBER When 5 Then AA.MNG_ITEM_.. 2015. 7. 28.
누계 샘플 WITH CTE (PAY_DT, DEPO_CLS_CD,PAY_YM ,DETL_CTG_CD, PAY_CHR_CD,PAY_AMT ,SUMM) AS( SELECT A.PAY_DT AS PAY_DT ,dbo. FN_FA_GET_CODE_NM('6016' ,MAX( B.DEPO_CLS_CD )) AS DEPO_CLS_CD , MAX (LEFT(B. PAY_YM,4 )) AS PAY_YM , dbo.FN_FA_GET_DETL_CTG_NM (A. ACCTUNIT_CD,MAX (B. ACCT_CTG_CD),MAX (B. DETL_CTG_CD)) AS DETL_CTG_CD , dbo .FN_FA_GET_ORG_NM( MAX(B .PAY_CHR_CD)) AS PAY_CHR_CD , SUM (B. PAY_AMT) AS PA.. 2015. 7. 28.
JOIN 걸어 UPDATE UPDATE Table1 SET a = B.a , b = @b , c = @c , d = @d FROM Table1 A JOIN ( SELECT PSNL_NO , DDC_PAYM_YM , DEPT_CD , PAYM_TARG_AMT FROM Table1 WHERE DDC_PAYM_YM = @DDC_PAYM_DT ) B ON A.a = B.a AND A.b = B.b AND A.c = B.c WHERE A.a = @DDC_PAYM_DT AND A.b =@b 2015. 7. 28.
테이블 코멘트 조회 쿼리 SELECT * FROM INFORMATION_SCHEMA.COLUMNS T1 LEFT OUTER JOIN ( SELECT T.NAME TABLE_NAME, TD.VALUE TABLE_DESC , C.NAME COLUMN_NAME, CD.VALUE COLUMN_DESC FROM SYSOBJECTS T INNER JOIN SYSUSERS U ON U.UID = T.UID LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES TD ON TD.MAJOR_ID = T.ID AND TD.MINOR_ID = 0 AND TD.NAME = 'MS_Description' INNER JOIN SYSCOLUMNS C ON C.ID = T.ID LEFT OUTER JOIN SYS.EXTENDED_PROPER.. 2015. 7. 27.
일괄 insert시 자동 순번 순번의 마지막 번호를 구한 다음에 ROW_NUMBER()를 사용하여 insert를 한다. --순번 따기 SELECT @SEQ = MAX(SEQ)FROM DBO.TB_INSERT IF @SEQ IS NULL SET @SEQ = 0 INSERT TB_INSERT ( seq , a , b , c ) SELECT ROW_NUMBER()OVER (ORDER BY a ASC ) + @SEQ , a , b , c FROM TB_TARGET 2015. 7. 27.
[MS 발췌] SQL Server for Developer: 개발자를 위한 튜닝 가이드 SQL Server for Developer: 개발자를 위한 튜닝 가이드 본 튜닝 가이드는 경험 있는 SQL관리자와 초보자 모두에게 도움이 되도록 기초부터 고급까지 튜닝에 꼭 필요한 내용을 최대한 쉬운 예제를 통해 기술했습니다. 예제는 쉽더라도 그 내용은 무척 중요하므로 꼭 따라 하면서 본 튜닝 가이드를 실습하여 봅시다. 쿼리 디자인 수칙1. SELECT는 필요한 결과값만을 요구하는가? 수칙2. 적절한 WHERE조건을 사용하는가? 수칙3. COUNT(컬럼명) 대신 COUNT(*)을 사용하는가? 수칙4. 커서 및 임시 테이블의 내용을 최대한 자제하는가? 수칙5. VIEW의 총 사용을 줄였는가? 수칙6. 저장 프로시저를 사용하는가? 수칙7. 저장 프로시저를 적절하게 리컴 파일 하며 사용하는가 ? 수칙8. 작.. 2015. 7. 27.
Merge Using(데이터 인서트 할 때 테이블에 있는지 비교) 단일 업데이트및 수정MERGE INTO dbo.TBTable AS A USING ( SELECT @a AS a ,@b AS b ,@c AS c ,@d AS d ) B ON A.a = B.a WHEN MATCHED AND @SaveFlag = 'D' THEN DELETE WHEN MATCHED AND @SaveFlag = 'U' THEN UPDATE SET a = @b , a = @b WHEN NOT MATCHED THEN INSERT (a, c, b, d) VALUES (@a, @c, @b, @d) ; 일괄 업데이트및 수정 MERGE INTO dbo.TABLE T USING( SELECT aa ,ss ,dd ,gg FROM TABLE ) S ON T.gg = S.gg WHEN MATCHED THEN UP.. 2015. 7. 27.
PIVOT SELECT NATION , [M] MAN , [W] WAMEN , [X] X , [M] + [W] + [X] TOTAL FROM ( SELECT NATION , GENDER_TYPE FROM DBO.Table1 ) P PIVOT ( COUNT(GENDER_TYPE) FOR GENDER_TYPE IN ([M], [W], [X]) ) PIVOTTABLE ORDER BY NATION NATION MAN WAMEN X TOTAL korea 0 6 3 9 japna 1 2 3 6 http://msdn.microsoft.com/ko-kr/library/ms177410(v=sql.105).aspx ----------------------------------------------------------------- .. 2015. 7. 27.
[설정] 배경 검은색 저장 파일 sql2008용 CurrentSettings-2013-05-20 sql2012용 Exported-2013-06-06 2015. 7. 27.
구분값으로 구분된 키값을 테이블로 변환 *2016부터는 펑션을 만들어서 사용할 필요가 없음. 아래와 같이 하면 됨.2016부터 추가된 함수SELECT * FROM String_Split('56940840542,56940150020' ,',') ALTER FUNCTION [dbo].[FN_Split] ( @Separator CHAR(1), @List VARCHAR(1000) ) RETURNS @RTN_TABLE TABLE(Item VARCHAR(30)) AS BEGIN DECLARE @IDX INT; DECLARE @vNewText VARCHAR(4000); IF @List = null RETURN; SET @IDX = CHARINDEX(@Separator, @List); WHILE NOT(@IDX = 0) BEGIN SET @vNewText .. 2015. 7. 27.
숫자로 구성된 문자열 시퀸스에 특정 자리를 특정 문자로 채우기(REPLICATE 함수를 사용해서 특정 문자로 채움) REPLICATE 함수를 사용해서 특정 문자로 채웁니다. * 특정 문자 : '0' * 대상 문자 : '4321' * 자리수 : 7자리 * 출력 결과 : '0004321' (좌측으로 붙이기), '4321000' (우측으로 붙이기) 좌측으로 붙이기 SELECT REPLICATE(특정 문자, 자리수 - LEN(대상 문자)) + 대상문자 SELECT REPLICATE('0', 7 - LEN('4321')) + '4321' 우측으로 붙이기 SELECT 대상문자 + REPLICATE(특정 문자, 자리수 - LEN(대상 문자)) SELECT '4321' + REPLICATE('0', 7 - LEN('4321')) 2015. 7. 27.
sp적용시 기존 sp 삭제하고 생성 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.UP_SPNAME) AND type in (N'P', N'PC')) DROP PROCEDURE dbo.UP_SPNAME GO 2015. 7. 27.
마이그레이션 관련 쿼리 예 DateTime이 아닌 String으로 들어가 있는 데이터를 DateTime로 변환 할 때 예)날짜시간(20120720153559)->DateTime(2013-10-01 17:03:20.987) update TBL_PLACE set INPUT_DATE = convert(datetime, substring(INPUT_DT, 1, 4) + '-' + substring(INPUT_DT, 5, 2) + '-' + substring(INPUT_DT, 7, 2) + ' ' + substring(INPUT_DT, 9, 2) + ':' + substring(INPUT_DT, 11, 2) + ':' + substring(INPUT_DT, 13, 2)) , CHANGE_DATE = convert(datetime, subs.. 2015. 7. 27.
채번 리딩제로 -- 연 단위 MAX COUNT + 1 = 시퀀스 SELECT @SeqOfYear = COUNT(*) + 1 FROM BMS.dbo.TB_BW_WorkMgmt WHERE SUBSTRING(WorkNo,1,4) = YEAR(GETDATE()) -- 리딩제로 적용(세자리) SELECT @SeqLeadingZero = RIGHT('000'+ CONVERT(VARCHAR,@SeqOfYear),3) SELECT @WorkNo = CONVERT(char(8),GETDATE(),112) + @SeqLeadingZero -- WorkNo = 등록일자 + 리딩제로 적용된 일년 치 카운트 + 1 (ex 20130101+001) SELECT @WorkNo --채번 DECLARE @seq CHAR(4) , @FileID CH.. 2015. 7. 27.
[프로파일러] 내 것만 조회해서 보기 Evfents Selection 탭에서 Column Filter 버튼 클릭 후 ClientProcessID에 Equal 속성에 자신의 ID를 준다. ID는 작업 관리자에서 W3WP를의 ID를 주면 된다. 2015. 7. 27.
[설정] Table 변경 수정시 에러 메시지 테이블에 컬럼및 속성등이 추가되거나 변경될 대 발생하는 에러 대처법. SQL Server Management Studio (SSMS) 옵션 ->디자이너 -> 테이블 다시 생성 해야 하는 변경 내용을 저장 안 함 옵션을 비활성화 2015. 7. 27.
[SELECT] 함수등을 이용한 데이터 조회 SELECT --시작일부터 지금까지 경과일 구하기, DATEDIFF(DD, HIRE_DT, GETDATE()) WORKING_DAY --금액(숫자)데이터에 콤마찍기, REPLACE(CONVERT(VARCHAR(50), CAST(20000 AS MONEY), 1) , '.00', '') --문자인지 숫자인지 판단해야될 때SELECT ISNUMERIC('sdfs') --문자인지, 숫자인지 판단 문자는 0SELECT ISNUMERIC('1111') --문자인지, 숫자인지 판단 숫자는 1을 반환한다. 2015. 7. 27.
WHERE 밑으로 쓰는 조건 모음 WHERE 1=1 --CASE WHEN 조건 AND (a.JobGrpCode = CASE WHEN @IsSearchType= '1' THEN '99' ELSE a.JobGrpCode END ) AND (@BelongCode = '' OR CASE WHEN @IsSearchType = '1' THEN b.CoCode ELSE a.DeptCode END = @BelongCode AND (a.JobGrpCode = CASE WHEN @IsSearchType= '1' THEN '99' ELSE a.JobGrpCode END ) --게시판등의 검색 타입 등에 따른 검색이 달라지는 경우 OR검색일 경우 앞이 참이면 뒤는 검색 안함. AND (@Keyword ='' OR @LikeSearchType IN (1, 2,.. 2015. 7. 27.
반응형

티스토리툴바