ROUND
ROUND는 반올림 함수이다.
형식: ROUND(숫자, 자릿수)
ROUND는 자릿수만 조심하면 된다. 2번째 결과는 8을 반올림하여 그 앞의 9에 1이 더해진것이고 3번째 결과는 9가 반올림된것이다.
CEILING, FLOOR
CEILING은 소수점 첫번째 값을 기준으로 올림을 하고 FLOOR는 소수점 첫번째 값을 기준으로 내림을 한다.
형식: CEILING(숫자) ... FLOOR도 동일
CEILING과 FLOOR는 소수 첫번째값이 5 이상이든 이하든간에 무조건 올림, 내림을 수행한다.
REPLACE
REPLACE함수는 지정한 문자열을 다른 문자열로 바꾸어준다.
형식: REPLACE(문자, 타겟문자, 바꿀문자)
REPLICATE
REPLICATE함수는 문자열을 지정된 횟수만큼 반복한다.
형식: REPLICATE(문자, 횟수)
STUFF
STUFF함수는 문자열에서 특정 시작위치에서 지정된 길이만큼 문자를 바꾸어준다.
형식: STUFF(문자, 시작위치, 길이, 바꿀문자)
SUBSTRING
SUBSTRING은 STUFF와 비슷하지만 문자를 바꾸는 것이 아니라 그 문자를 반환한다.
형식: SUBSTRING(문자, 시작위치, 길이)
PATINDEX, CHARINDEX
PATINDEX와 CHARINDEX는 문자열에서 지정한 패턴이 시작되는 위치를 뽑아준다.
형식: PATINDEX(문자패턴, 문자) - 문자패턴은 Like 사용과 같다.
형식: CHARINDEX(문자패턴, 문자) - 문자패턴은 일반형식을 사용한다.
SPACE
SPACE함수는 지정한 수 만큼 공백을 추가한다.
형식: SPACE(횟수)
시간 및 날짜 함수
GETDATE()
GETDATE()는 현재 시간을 표시해준다.
DATEADD
DATEADD함수는 날자에 지정한 만큼을 더한다.
형식: DATEADD(날자형식, 더할 값, 날자)
DATEDIFF
DATEDIFF함수는 두 날자사이의 날자형식에 지정된 부분을 돌려준다.
형식: DATEDIFF(날자형식, 시작 날자, 끝 날자)
DATENAME
DATENAME함수는 지정한 날자의 날자형식의 이름을 돌려준다.
형식: DATENAME(날자형식, 날자)
DATEPART
DATEPART함수는 날자에서 지정한 날자형식부분만 추출해줍니다.
형식: DATEPART(날자형식, 날자)
주일은 일요일부터 1로 시작해서 토요일날 7로 끝나게 된다.
--*******************************************************************
-- 함수(Conversion)
--*******************************************************************
--cast, convert : 문자로 치환/number와 date를 문자로 치환.
-- /간단한 형 변환(CAST)
-- /날짜를 문자로 변환 시 여러가지 형식이 가능하므로 주로 사용하는 함수는(CONVERT)이다.
select convert(char, 123456789) result1,
cast(123456789 as char) result2
--
select convert(varchar,456789) result1,
cast(456789 as varchar) result2,
str(456789, 10) result3
/* 숫자를 바꾸는 convert함수를 썼는데 2번째줄의 경우는 '300567'이라는 6섯자를
'char(4)' 4자로 바꾸라는 것: 이것은 말이 안되므로 * 표시가 프린트됨!(주의 사항) */
select convert(char(7),300567) result1,
convert(char(4),300567) result2
--
select convert(char,getdate(),0) result1,
cast(getdate() as char) result2
--
select convert(CHAR, GETDATE(), 109) RESULT
select convert(CHAR, GETDATE(), 111) RESUlT
select convert(CHAR(10), GETDATE(), 121) RESULT
select convert(CHAR, GETDATE(), 114) RESULT
--1. 방법1
select mem_id "회원ID",
left(mem_id,1) + right(convert(char(4),right(mem_id,3) + 1010),3) "조합회원ID"
from member
where mem_name = '이쁜이'
--2. 방법2
select mem_id "회원ID",
left(mem_id,1) + right(convert(char(4),substring(mem_id,2,4)+1010),3) "조합회원ID"
from member
where mem_name = '이쁜이'
--***********************************************************************
-- 함수(conversion) : 날짜로 치환
--***********************************************************************
--날짜로 치환/모양만 날짜형인 문자를 DATE로 치환
select'19990101'result1,'1999-01-01'result2,
'1999-01-01 00:00:00.000'result3
--
select convert(datetime,'20201025',112)" result1",
convert(datetime,'20201025')" result2",
convert(datetime,'2020-10-25 10:15:20.000') " result3",
cast('2020-10-25 10:15:20.000' as datetime) " result4"
--
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 검색!
--(Alias는 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
convert(datetime,'19'+mem_regno1) 치환날짜
from member
--1900년대 사람들만 있다고 가정했을 경우 19를 더해서 밀레니엄버그를 없앨 수 가 있다.
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 500일을 더한 날짜를 검색!
--(Alias는 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
convert(datetime,'19'+mem_regno1) + 500 치환날짜
from member
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 '1974-01-01'부터 '1975-12-31'사이의 날짜를 검색!
--(Alias 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
convert(datetime,'19'+mem_regno1) 치환날짜
from member
where convert(datetime,'19'+mem_regno1)between'1974-01-01'and'1975-12-31'
--회원테이블에서 생일을 문자로 치환한 후 LIKE '1975%'의 조건을 사용하여 해당회원을 검색!
--Alias는 회원명, 생일)
select mem_name 회원명, mem_bir 생일
from member
where convert(char, mem_bir, 121) like '1975%'
--******************************************************************
-- 함수(NULL)
--******************************************************************
/* ## 데이터를 처리할 때 NULL값의 사용은 최대한 줄여야 하지만 사용해야 할 경우가 있다.
## 학생에 대한 정보를 입력할 때 '전화번호' 속성은 전화번호가 없는 학생이 있을 수가 있다 .이런 경우에는 NULL값을 사용한다.
## NULL값은 0.1과 같은 특정한 값이 아니고 아무 것도 없는 것을 뜻한다.
## SQL에서 NULL값을 허용하지 않는 속성에 대해 NULL값으로 수정하려 한다면 에러가 발생한다. */
--null값을 찾을 때에는 is null
select buyer_name buyer_charger
from buyer
where buyer_charger is null
--null인 값을 찾을 때 '='은 성립되지 않는다.
select buyer_name buyer_charger
from buyer
where buyer_charger=null
--
select buyer_name buyer_charger
from buyer
where buyer_charger =''
--null값이 아닌 값을 찾으려 할 때 is not null
select buyer_name buyer_charger
from buyer
where buyer_charger is not null
--ISNULL(c,d) / c가 NULL값이면 d값으로 치환
--null값일 때 '이름없다' 로 출력!
select buyer_name, buyer_charger, isnull(buyer_charger, '이름없다')
from buyer
--null에다 100을 더하면 null이다.
select null + 100
--
select isnull(null,0)+100
--
select mem_name, mem_mileage, mem_mileage+100
from member
--
select mem_name, mem_mileage, mem_mileage + 100
from member
where mem_name like '[바-
WHILE @i>=@j
BEGIN
INSERT INTO @temp(date) values(@temp_date)
SET @j=@j +1;
SET @temp_date= DATEADD(dd,@j,@s_date)
END
/*SELECT date FROM @temp*/
INSERT INTO DUAL_CALENDAR
SELECT
DATENAME(yy, date), DATENAME(mm, date),
/*DATENAME(dd, date), */
SUBSTRING(CONVERT(char(10), date,121), 9, 10),
CONVERT(char(10), date,121) AS date
FROM @temp
/*여기까지 한번에 실행할 것. 100년까지는 한번에 안되는듯. 50년은 됨.
50년 마다 한번씩 실행되게 스케줄러에 등록 또는 1년마다 한번씩 되도록 등록*/
다소 쌩뚱맞긴 합니다만... ^^;;
통계작업에 유용하게 사용할 수 있는 달력테이블을 만들어서 사용하기도 합니다.
create table 달력 (년 varchar2(4), 월 varchar2(2), 일 varchar2(2), 년월일 varchar2(8));
요로코롬 말이지요.
말씀하시는바와 같이 화면에 임의의 년에대한 모든 월데이터를 보인다던가.. 할때 유용하게 사용할 수 있지요.
통계화면이 한두개가 아니다보니.. 요로코롬 미리 테이블을 구성한 후 join으로 이용하는것도 꽤 괜찮은 방법입니다.
DBA님이나 사수분께 의논해보심은 어떠시련지요. ^^;;
아니면.. 제 오래된 기억에 mssql 2000의 경우엔 템프테이블을 만들 수 있는 방법이 있었는데요..
요 템프테이블을 달력테이블처럼 구성하여 사용하셔도 될듯 합니다.
그럼 ^^;;
통계 시 실제 데이터가 없는 일/월/연을 0건으로 표시하기 위해서 위와 같은 처리가 필요.
아님
select 1 as month, 0 as cnt
from dual
union all
select 2 as month, 0 as cnt
from dual
union all
select 3 as month, 0 as cnt
from dual
union all
select 4 as month, 0 as cnt
from dual
union all
select 5 as month, 0 as cnt
from dual
....
으로 가라로 뷰를 하나 만든다음에 outer join 하면 되지 않을까요?
mgcos
이건 넘 쿼리가 길어지는 듯....
/*OUTER JOIN 테스트용. 임시로 월간 캘린더 테이블을 만들어서 실제 데이타와 조인. 그런 다음 갯수를 세면 데이터가 없는 월의 것도 카운트 가능해진다 임시 테이블을 만들어서 데이타를 뽑아내긴 하였으나, 검색기간이 동적으로 변하므로 그것에 대한 처리는 어떻게 하는가? 실제 물리적인 테이블에 모든 데이타를 넣어두어 그것을 가지고 해야하지 않나? */ SELECT COUNT(EVT_CODE), MONTHLY FROM ( SELECT R5E.EVT_CODE, /*CASE //이것은 필요없을 듯. MSSQL서 COUNT시 NULL은 자동으로 0으로 인식됨 WHEN R5E.EVT_CODE IS NULL THEN 0 WHEN R5E.EVT_CODE IS NOT NULL THEN 1 END AS CODE_COUNT,*/ CASE WHEN R5E.MON IS NULL THEN TM2.MON WHEN R5E.MON IS NOT NULL THEN R5E.MON END AS MONTHLY FROM ( ( SELECT EVT_CODE, CONVERT(CHAR(7), EVT_DATE, 121) AS MON FROM R5EVENTS WHERE CONVERT(CHAR(7), EVT_DATE, 121) BETWEEN '2008-12' AND '2009-01' ) R5E FULL OUTER JOIN ( SELECT MON, CNT FROM ( SELECT '2009-01' AS MON, 0 AS CNT FROM DUAL UNION ALL SELECT '2009-02' AS MON, 0 AS CNT FROM DUAL UNION ALL SELECT '2009-03' AS MON, 0 AS CNT FROM DUAL UNION ALL SELECT '2009-04' AS MON, 0 AS CNT FROM DUAL UNION ALL SELECT '2009-05' AS MON, 0 AS CNT FROM DUAL UNION ALL SELECT '2009-06' AS MON, 0 AS CNT FROM DUAL UNION ALL SELECT '2009-07' AS MON, 0 AS CNT FROM DUAL UNION ALL SELECT '2009-08' AS MON, 0 AS CNT FROM DUAL UNION ALL SELECT '2009-09' AS MON, 0 AS CNT FROM DUAL UNION ALL SELECT '2009-10' AS MON, 0 AS CNT FROM DUAL UNION ALL SELECT '2009-11' AS MON, 0 AS CNT FROM DUAL UNION ALL SELECT '2009-12' AS MON, 0 AS CNT FROM DUAL ) TM ) TM2 ON TM2.MON = R5E.MON ) ) A GROUP BY MONTHLY
