728x90
숫자함수

ABS
ABS함수는 절대값을 나타낸다.
형식: ABS(숫자값)

POWER
POWER함수는 제곱근을 표현해 준다.
형식: POWER(숫자값, 승수값)

SQRT
SQRT는 루트값을 표현해준다.
형식: SQRT(숫자값)

RAND
RAND는 0 에서 1사이의 난수를 발생시킨다.
형식: RAND(초기값)

ROUND
ROUND는 반올림 함수이다.
형식: ROUND(숫자, 자릿수)
ROUND는 자릿수만 조심하면 된다. 2번째 결과는 8을 반올림하여 그 앞의 9에 1이 더해진것이고 3번째 결과는 9가 반올림된것이다.

CEILING, FLOOR
CEILING은 소수점 첫번째 값을 기준으로 올림을 하고 FLOOR는 소수점 첫번째 값을 기준으로 내림을 한다.
형식: CEILING(숫자) ... FLOOR도 동일
CEILING과 FLOOR는 소수 첫번째값이 5 이상이든 이하든간에 무조건 올림, 내림을 수행한다.

----------------------------------------------------------
문자함수

LEFT, RIGHT
LEFT는 지정해준 자릿수만큼 왼쪽에서부터 문자열을 반환한다.
당연히 RIGHT는 반대이다.
형식 : LEFT(문자, 자릿수)

LTRIM, RTRIM
LTRIM은 문자열의 왼쪽 공백을 제거한다. 역시 RTRIM은 반대일 경우 사용된다.
형식: LTRIM(문자)

LEN
LEN함수는 문자열에서 문자의 갯수를 추출한다.
형식: LEN(문자)
Len함수는 문자 뒤쪽의 공백은 문자로 계산하지 않는다.

UPPER, LOWER
UPPER는 소문자를 대문자로, LOWER는 대문자를 소문자로 바꾼다.
형식: UPPER(문자)

REVERSE
REVERSE는 문자열을 반대로 표시한다.
형식: REVERSE(문자열)

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로 끝나게 된다.

---------------------------------------------------------------------
NULL 함수
ISNULL
ISNULL은 NULL값을 대체값으로 바꾼다.
형식: ISNULL(NULL값, 대체값)

NULLIF
NULLIF함수는 두개의 표현식을 비교하여 같으면 NULL을 반환한다.
형식: NULLIF(표현식1, 표현식2)

COALESCE
COALESCE함수는 NULL이 아닌 첫번째 표현식이 반환된다.
형식: COALESCE(표현식)

GETANSINULL
GETANSINULL은 데이터베이스의 기본 NULL 상태를 표시해준다.
형식: GETANSINULL(데이터베이스 이름)


------------------------------------------------------
AVG, MAX, MIN, SUM, COUNT

MAX
최대값을 구한다.

MIN
최소값을 구한다.

SUM
값갑의 합을 구한다.

AVG
평균을 구한다.

COUNT
값의 갯수를 구한다. 

[출처] mssql 함수모음|작성자 필립박

728x90
순위 함수의 부재로 인해 MSSQL2000에서는 실행될 수 없었지만 MSSQL2005에서 가능해진 기능을 소개하고자 합니다.

Transact-SQL은 다음과 같은 순위 함수를 제공합니다.

 
RANK NTILE
DENSE_RANK ROW_NUMBER

순위함수의 내용은 http://msdn2.microsoft.com/ko-kr/library/ms189798.aspx 링크의 온라인북을 참고하셔도 됩니다.

MSSQL2000에서는 오라클에서 쓰는 ROW_NUMBER() 같은 함수를 쓸 수 없어 유저들의 불만이 많았습니다. 다음과 같이 동적으로 카운트를 하는 서브쿼리를 통하여 비슷하게 구현할 수 있었습니다. 다음의 예제를 통해서 MSSQL2000에서 여러 단계를 거쳐야 했던 방법을 MSSQL2005에서 한 문장으로 해결되는 예제를 소개합니다.

 
create table TEST1 (user_id char(3),num int)

insert TEST1 values('aaa',3)
insert TEST1 values('aaa',2)
insert TEST1 values('bbb',15)
insert TEST1 values('bbb',2)
insert TEST1 values('aaa',6)
insert TEST1 values('aaa',4)
insert TEST1 values('aaa',3)
리스트 1 기초 데이터 입력

구분되는 일련번호가 없는 데이터를 다음과 같은 결과물을 얻으려면 어떻게 해야 되는지에 대한 문제입니다. 데이터는 user_id로 구분이 되어 있고 구분이 없는 num 값으로 구성되어 있습니다.

 
user_id   num1        num2        num3        num4        num5       
------- ----------- ----------- ----------- ----------- ----------
aaa         3             2              6              4               3
bbb        15            2             NULL          NULL           NULL
리스트 2 원하는 결과

MSSQL 2000을 이용할 경우

리스트 2 의 결과 처럼 user_id 별로 num1~num5 컬럼으로 나누어 데이터를 가져오는 것입니다. 각 로우를 구분하기 위해 indentity()함수를 이용해서 임시테이블에 id 라는 일련번호 컬럼을 만듭니다.

 
--------------------------------------------------------
-- 일련번호생성하여 #temp_1 만들기


select identity(int,1,1) id,* into #temp_1 from TEST1
리스트 3 일련번호 생성하기 위해 identity()함수를 이용한 임시테이블 만들기

각 로우가 구분되는 id라는 컬럼이 만들어 졌으므로, user_id 별로 일련번호를 Group by및 case 문을 사용하여 원하는 결과를 쿼리합니다.

 
select user_id,
 
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select (select count(*)
 
from #temp_1
where user_id = a. user_id
and id <= a.id) id,a.user_id,num
from #temp_1 a) t1
group by
user_id
리스트 4 MSSQL2000의 임시테이블을 이용한 쿼리

 
/* user_id     num1    num2    num3    num4     num5
------- ----------- ----------- ----------- ----------- ----------
aaa             3           2          6           4           3
bbb           15          2        NULL       NULL       NULL

(2개행적용됨)
*/
리스트 5 MSSQL2000의 임시테이블을 이용한 쿼리 결과

MSSQL 2005 ROW_NUMBER( ) OVER 절 이용

ROW_NUMBER ( )  구문

 
ROW_NUMBER ( ) OVER
(
[ <partition_by_clause> ] <order_by_clause>
)
리스트 6 ROW_NUMBER ( )  구문

인수

<partition_by_clause>
 
from 절이 생성한 결과 집합을 ROW_NUMBER 함수가 적용되는 파티션으로 나눕니다.

<order_by_clause>
 
파티션에서 ROW_NUMBER 값이 행에 할당되는 순서를 결정합니다. 자세한 내용은 ORDER BY 절(Transact-SQL)를 참조하십시오. 순위 함수에 <order_by_clause>가 사용된 경우 정수는 열을 나타낼 수 없습니다.

MSSQL2000의 쿼리에서는 원하는 결과를 위해서 각 로우를 구분하는 id열을 가진 임시테이블을 만들고 또한, 구분되어진 id 열을 user_id에 따라 서브쿼리를 하고, count 하여야 원하는 결과를 구할수 있었습니다. MSSQL 2005에서 등장한 순위 함수를 이용하면, 다음과 같은 한 문장으로 원하는 결과를 구할수 있습니다.

 
select user_id,
 
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select row_number()
 
over (partition by user_id order by user_id) id,* 
from TEST1) t1
group by
user_id
리스트 7 MSSQL2005의 row_number() 함수를 이용한 쿼리

 
/*
user_id                                                
------- ----------- ----------- ----------- ----------- --------
aaa     3           2           6           4           3
bbb    15         2         NULL      NULL      NULL
경고: 집계또는다른SET 연산에의해NULL 값이제거되었습니다.

(2개행적용됨)
*/
리스트 8 MSSQL2005의 row_number() 함수를 이용한 쿼리 결과

리스트 9 MSSQL2005의  row_number() 함수를 이용한  쿼리 결과
리스트 9 MSSQL2000과 MSSQL2005의 row_number() 함수를 이용한 쿼리 결과 실행계획

임시테이블을 생성하는 로직을 포함하지 않더라도, MSSQL2005의 순위 함수를 사용하는 것이 성능상 유리합니다.

 
SET STATISTICS IO ON
SET STATISTICS TIME ON

GO
select identity(int,1,1) id,* into #temp_1 from TEST1

SQL Server 구문분석및컴파일시간:
 
CPU 시간= 13ms, 경과시간= 13ms.
 
테이블'TEST1'. 검색수1, 논리적읽기수1, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

SQL Server 실행시간:
 
CPU 시간= 0ms, 경과시간= 13ms.
 

(7개행적용됨)

select user_id,
 
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select (select count (*)
from #temp_1
where user_id = a.user_id
and id <= a.id) id,a. user_id ,num
from #temp_1 a) t1
group by user_id

user_id                                                
------- ----------- ----------- ----------- ----------- ----------
aaa     3           2           6           4           3
bbb    15         2         NULL       NULL       NULL

경고: 집계또는다른SET 연산에의해null 값이제거되었습니다.

(2개행적용됨)

테이블'#temp_1'. 검색수8, 논리적읽기수16, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기 수0.

SQL Server 실행시간:
 
CPU 시간= 0ms, 경과시간= 1ms.

select user_id,
 
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select row_number( )
over (partition by user_id order by user_id) id,*  from TEST1) t1
group by user_id

user_id                                                
------- ----------- ----------- ----------- ----------- ----------
aaa     3           2           6           4           3
bbb     15          2           null        null        null

경고: 집계또는다른SET 연산에의해null 값이제거되었습니다.

(2개행적용됨)

테이블'TEST1'. 검색수1, 논리적읽기수1, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

SQL Server 실행시간:
 
CPU 시간= 0ms, 경과시간= 1ms.
리스트 10 실행통계

이상과 같이 실행통계의 성능을 통하여 MSSQL 2005를 이용하면 성능상 더 좋고 간단한 방법을 알아보았습니다. 다른 랭크 함수 (RANK,NTITLE,DENSE_RANK) 들의 이용과 응용은 여러분의 몫입니다. 
728x90
--**********************************************************************
--      함수(날짜열)
--**********************************************************************
함수(날짜열)
DATE구분 구분약자 DATE구분 구분약자
year yy week wk
quarter qq hour hh
month mm minute mi
day of year dy second ss
day dd millisecond ms
항목 내용
GETDATE() 시스템에서 제공하는 날짜와 시간 값
산술연산 -- date + NUMBER
-- date - NUMBER


--날짜는 계산할 수 있다.
--getdate()
select getdate()'현재날짜 & 시간'
--getdate() -1, +1
select getdate() - 1'어제 이 시간',
   getdate() + 1'내일 이 시간'
--오늘부터 100일 전의 날짜를 검색!
select getdate() - 100
--회원테이블의 생일과 1200일째 되는 날을 검색! (Alias는 회원명, 생일, 12000일째)
select mem_name "회원명", mem_bir "생일",
   getdate() + 1200 "1200일째"
 from member

--**********************************************************************
--   DATEADD 함수(날짜열)
--**********************************************************************
--DATEADD (Part,n,date)/Part부분에 n만큼 더한 date
--      (DATE구분별로 모두 적용)  

select getdate() '현재날짜 & 시간',
   dateadd(year, 1, getdate()) '1년뒤'
--
select getdate() '현재날짜 & 시간',
   dateadd(mm,-1, getdate()) '한달전'
--
select getdate()'현재날짜 & 시간',
   dateadd(dd, -50, getdate()) '50일전'
--
select getdate()'현재날짜 & 시간',
   dateadd(hour, 7, getdate()) '7시간 뒤'
--
select dateadd(yy, 1, '19990228') "1년후",
   dateadd(yy, 2, '19990228') "2년후",
   dateadd(yy, 3, '19990228') "3년후"
--
select dateadd(mm, 12, '19000228') "1년후",
   dateadd(mm, 24, '19000228') "2년후",
   dateadd(mm, 36, '19000228') "3년후"
--오늘부터 6개월 전의 날짜를 검색!
select dateadd(mm, -6, getdate())"6개월전의 날짜"
--회원테이블에서 구길동회원의 생일과 12000일째 되는 날을 검색!
--(DATEADD사용) (Alias는 회원명, 생일, 12000일째)
--1.
select mem_name"회원명", mem_bir"생일", dateadd(dd,1200,mem_bir)"1200일째" from member
   where mem_name like '구길동%'
--2.
select mem_name"회원명", mem_bir"생일", dateadd(dd,1200,mem_bir)"1200일째" from member
   where mem_name like '구길동'
--3.
select mem_name"회원명", mem_bir"생일", dateadd(dd,1200,mem_bir)"1200일째" from member
   where mem_name='구길동'

--********************************************************************
--      YEAR,MONTH,DAY,DATEDIFF함수(날짜열)
--********************************************************************
--year, month, day 해당일의 년월일 값을 돌려준다.
select year(getdate())'year',
   month(getdate())'month',
   day(getdate())'day'
--datediff(part,date1,date2)/두 날짜 사이의 DATE구분 사이 값을 계산함.
--   (part, date1, date2)
select datediff(dd,'1999/02/15','2000/02/15')'day',
   datediff(mm,'1999/02/15','2000/02/15')'month',
   datediff(yy,'1999/02/15','2000/02/15')'year'
--
select datediff(dd,'1999/11/06','2000/02/15')'day',
   datediff(mm,'1999/11/06','2000/02/15')'month',
   datediff(yy,'1999/11/06','2000/02/15')'year'
--회원테이블의 생일과 오늘의 일수 차이를 검색! (Alias는 회원명, 생일, 오늘, 태어난지?)
select mem_name"회원명", mem_bir"생일",
   getdate()"오늘",
   datediff(dd,mem_bir,getdate())"태어난지?"
 from member
--본인의 생년월일과 오늘의 일수 차이를 비교 검색!
select datediff(dd,'1972/08/14',getdate()) "차이는"

--*******************************************************************
--      Datename, Datepart함수(날짜열)
--*******************************************************************
--datename(part,date)/date에서 datepart부분의 ASCII값(구분문자)
--datepart(part,date)/date에서 datepart부분의 정수값(구분숫자)
select convert(char,getdate()), '<==현재날짜'
select datename(yy,getdate()),datepart(yy,getdate()),'<==년도'
select datename(qq,getdate()),datepart(qq,getdate()),'<==분기'
select datename(mm,getdate()),datepart(mm,getdate()),'<==월'
select datename(dy,getdate()),datepart(dy,getdate()),'<==일수'
select datename(dd,getdate()),datepart(dd,getdate()),'<==해당일'
select datename(wk,getdate()),datepart(wk,getdate()),'<==주수'
select datename(dw,getdate()),datepart(dw,getdate()),'<==요일'
select datename(hh,getdate()),datepart(hh,getdate()),'<==시간'
--회원테이블에서 구길동회원의 생일의 DATENAME 과 DATEPART를 검색!
--(Alias 는 회원명, 생일, 기타 上 同)
select mem_name"회원명", mem_bir"생일",
   datename(dw,mem_bir)'기타上同',
   datepart(dw,mem_bir)'기타上同'
 from member
   where mem_name='구길동'
/* 월 (2002년 2월)만 입력받아 해당월의 시작일과 종료일을 검색!
Alias는 해당월, 시작일, 종료일) */
--******************** 1. 2002-02-01의 1달 후 -1....
select '200년02월' 해당월, '200-02-01' 시작일,
   dateadd(mm,1,'2000-02-01')-1 "종료일"
--******************** 2. 2002-03-01의 1일 전....
select '200년02월' 해당월, '200-02-01' "시작일",
   dateadd(dd,-1,'2000-03-01') "종료일"

--*******************************************************************
--      함수(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

--
select convert(char, getdate(),0)
select convert(char, getdate(),1)
select convert(char, getdate(),2)
select convert(char, getdate(),3)
select convert(char, getdate(),4)
select convert(char, getdate(),5)
select convert(char, getdate(),6)
select convert(char, getdate(),7)
select convert(char, getdate(),8)
select convert(char, getdate(),9)
select convert(char, getdate(),10)
select convert(char, getdate(),11)
select convert(char, getdate(),12)
select convert(char, getdate(),13)
select convert(char, getdate(),14)
--다음 아래는 ERR...
select convert(char, getdate(),15)
select convert(char, getdate(),16)
select convert(char, getdate(),17)
select convert(char, getdate(),18)
select convert(char, getdate(),19)
--여기까지 ERR...
select convert(char, getdate(),20)
select convert(char, getdate(),21)
select convert(char, getdate(),22)
select convert(char, getdate(),23)
select convert(char, getdate(),24)
select convert(char, getdate(),25)
--여기서 또 아래는 ERR...
select convert(char, getdate(),26)
select convert(char, getdate(),27)
select convert(char, getdate(),28)
select convert(char, getdate(),29)
select convert(char, getdate(),30)
select convert(char, getdate(),31)
select convert(char, getdate(),32)
select convert(char, getdate(),33)
select convert(char, getdate(),34)
-- .
-- .
-- .
select convert(char, getdate(),99)
--99까지 ERR
--100단위로 위와 똑같은 숫자안에 포함되어 쓰인다.
--120과 121번의 경우는 우리나라에서 많이 쓴다. 그냥 21번을 쓰기도 한다.
select convert(char, getdate(),120)
select convert(char, getdate(),121)
/* 위에서 일반숫자형을 문자로 변환할 때 변환되어지려는 값과 변환하려는 값의
자릿숫자값이 다르면 에러가 났었지만, 날짜형은 그 자릿수만큼 잘라서 출력한다! */
--다음은 그 예이다.
--1. 날짜형 변환의 예)
select convert(char(10), getdate(),121)
--2. 숫자형 변환의 예)
select convert(char(7),300567) result1,
convert(char(4),300567) result2
--3. 문자형를 자르기 예)
select convert(char(10), 'abcdefghijklmnopqrstuvwxyz')

--122~에서부턴 형식이 맞지 않는다 ERR
select convert(char, getdate(),122)
select convert(char, getdate(),123)
select convert(char, getdate(),124)
select convert(char, getdate(),125)
   :
   :

--상품테이블에서 상품코드와 상품명을 연결하여 30자리로 치환하여 검색!
--Alias는 상품코드, 상품명, 치환상품명)
select prod_id 상품코드, prod_name 상품명,
   convert(char(30), prod_id + prod_name) 치환상품명
  from prod

--***********************************************
--      conversion
--***********************************************
--예제1) // datetime, smalldatetime, decimalzero, decimalpoint, numericzero, numericpoint

select convert(char(8), getdate(),112) result
--
select convert(datetime, '2001-01-01') DATETIME
--
select smalldatetime = convert(smalldatetime,'2001-01-01')
--
select decimalzero = convert(decimal(15),12345678912345)
--
select decimalpoint = convert(decimal(17,2),123456789012345.11)
--
select numericzero = convert(numeric,123456789012345)
select numericpoint = convert(numeric(17,2),123456789012345.11)

--예제2) // float, int, smallint, tinyint, money

select convert(float,123456789012345) float
--
select convert(float,10/3.0) float
--
select convert(int,1234567890) int
--
select convert(smallint,12345) smallint
--
select convert(tinyint, 123) tinyint
--
select convert(money,123456789012345) money

--***********************************************************************
--함수(Conversion) : 숫자로 치환
--***********************************************************************
--   숫자로 치환 // 모양만 수치인 문자를 NUMBER로 치환
--   /모양만 수치라면 연산에는 영향이 없다.
select convert(numeric,'123456.9') result1,
   cast('123456.9' as numeric) result2
--
select convert(numeric(10,2),'123456.9') result1,
   cast('123456.9' as numeric(10,2)) result2
--
select convert(float,'123456.9') result1,
   cast('123456.9' as float) result2
--
select convert(decimal,'123456.9') result1,
   cast('123456.9' as decimal) result2
--*****************************************************
/* 회원테이블에서 이쁜이 회원의 회원ID(b001) 2~4문자열을 숫자형으로 치환한 후 10을 더하여
 새로운 회원ID(b011)로 조합(Alias는 회원ID, 조합회원ID) */

    회원ID     조합회원ID
   -------   ------------
     b001          b011
   (1 row(s) affected)
--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%'


<INPUT style="BORDER-TOP-WIDTH: 1px; PADDING-LEFT: 33px; FONT-WEIGHT: bold; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 11pt; BORDER-LEFT-COLOR: white; BACKGROUND: #d4d4d4; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: white; WIDTH: 770px; BORDER-TOP-COLOR: white; PADDING-TOP: 7px; HEIGHT: 30px; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: white" value="MSSQL 함수 (기타함수)">

--******************************************************************
--      함수(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 '[바-빟]%'
--
update member set mem_mileage = null
   where mem_name like '[바-빟]%'
--
select mem_name, mem_mileage, isnull(mem_mileage,0) + 100
   from member

--NULLIF(c,d) / c와 d를 비교하여 같으면 NULL을 다르면 c값을 돌려준다.
--반환값 : NULL
select nullif(123,123)
--반환값 : NULL
select nullif(1234,'1234')
--반환값 : 123
select nullif(123,1234)
--반환값 : a
select nullif('a','b')

--**************************************************
--예제)
--**************************************************
--거래처테이블에서 거래처명, 담당자 조회!
select buyer_name 거래처,buyer_charger 담당자
   from buyer
--거래처 담당자 성씨가 '김'이면 null로 갱신! 하기전에 먼저 확인!
select buyer_name, buyer_charger
   from buyer
   where buyer_charger like '김%'
--거래처 담당자 성씨가 '김'이면 null로 갱신!
update buyer set buyer_charger=null
   where buyer_charger like '김%'
--거래처 담당자 성씨가 '성'이면 SPACE로 갱신! 하기전에 먼저 확인!
select buyer_name, buyer_charger
   from buyer
   where buyer_charger like '성%'
--거래처 담당자 성씨가 '성'이면 SPACE로 갱신!
update buyer set buyer_charger=''
   where buyer_charger like '성%'
--***********************************************************************
--      함수 (NULL 관련)
--***********************************************************************
--is null, is not null /null값인지 아닌지 비교
--isnull(c,d)/c가 null값이면 d값으로 치환!
--nullif(c,d)/c와 d를 비교하여 같으면 null을, 다르면 c값을 돌려준다.
--***********************************************************************
--해당 컬럼이 null값 비교 조회
--1. null이 존재하는 상태로 조회
select buyer_name 거래처, buyer_charger 담당자
   from buyer
--2. null을 이용 null값 비교
select buyer_name 거래처, buyer_charger 담당자
   from buyer
   where buyer_charger = null      --▷' = null ' 대신 ' is null '을 사용해야 제대로 된 구문 !

--***********************************************************************
--      함수(GROUP)
--***********************************************************************
--AVG(columm)/조회범위 내에 해당 컬럼들의 평균값
--                     /DISTINCT : 중복된 값은 제외
--                     /ALL : Default로써 모든 값을 포함(all을 쓰지 않아도 Default값으로 적용)
--                     /Column명 : NULL값은 제외
--                     /* : NULL값도 포함(COUNT함수만 사용)
--                     isnull을 사용하여 NUll값은 '0'등으로 나오게 한다.
--
select avg(distinct prod_cost),avg(all prod_cost),
   avg(prod_cost) 매입가평균
   from prod
--상품테이블의 상품분류별 매입가격 평균 값
/* (집게함수 앞에 있는 것은 group by에 포함되어있어야만 한다.
      그러나, group by에 있는 것이 집게함수나 그 앞에 포함되지 않아도 무관!) */
select prod_lgu, avg(prod_cost)'상품분류별 매입가격 평균'
   from prod
   group by prod_lgu
--상품테이블의 총 판매가격 평균값을 구하시요?
--(Alias는 상품총판매가격평균)
select prod_lgu, avg(prod_sale)'상품총판매가격평균'
   from prod
   group by prod_lgu
--상품테이블의 상품분류별 판매가격 평균값을 구하시요? (Alias는 상품분류, 상품분류별판매가평균)
select prod_name'상품분류', avg(prod_sale)'상품분류별판매가평균'
   from prod
   group by prod_name

--**************************************************************************
--      함수(GROUP)
--**************************************************************************
--COUNT(col) / 조회 범위내 해당컬럼들의 자료수
--COUNT(*) / 선택된 자료의 수
--      NULL값까지 포함해서 갯수를 헤아린다.
--
select count(distinct prod_cost), count(all prod_cost),
   count(prod_cost),count(*)
  from prod
--상품테이블의 자료수
select count(*) result1, count(prod_lgu) result2
  from prod
--상품테이블의 상품분류별 자료수
select prod_lgu, count(*)'상품분류별 자료의 수'
  from prod
group by prod_lgu
--1.거래처테이블의 담당자를 컬럼으로 하여 count집게
--  (Alias는 "자료수(Distinct)", 자료수, 자료수(*))
--방식1
select count(distinct buyer_charger) "자료수(Distinct)",
   count(buyer_charger) "자료수",
   count(*) "자료수(*)"
  from buyer
--방식2
select count(distinct buyer_charger) "자료수(Distinct)",
   count(all buyer_charger) "자료수",
   count(*) "자료수(*)"
  from buyer
--2. 회원테이블의 취미종류를 count집계
--   (Alias는 취미종류)
select distinct mem_like "취미종류" from member
--3. 회원테이블의 취미별 COUNT집계 (Alias는 취미,자료수,자료수(*))
select mem_like 취미, count(mem_like) "자료수", count(*) "자료수(*)"
   from member group by mem_like
--4. 회원테이블의 직업종류수를 COUNT집계(Alias는 직업종류수)
select count(distinct mem_job) "직업종류수"
   from member
--5. 회원테이블의 직업종류를 집계
select distinct mem_job "직업종류" from member
--6. 회원테이블의 직업별 카운트 집계
select mem_job "직업", count(mem_job) "자료수", count(*) "자료수(*)"
   from member group by mem_job
--7. 장바구니 테이블의 회원별 카운트 집계
select cart_member, count(*), count(cart_member), count(distinct cart_member)
   from cart
  group by cart_member

--**************************************************************************
--      함수(GROUP)
--**************************************************************************
--MAX(col)     / 조회범위 내 해당컬럼들 중 최대값
--MIN(col)     /조회범위 내 해당컬럼들 중 최소값
-- 어차피 중복을 배제하나 않하나 최대값과 최소값은 같으므로 distinct를 쓰나 마나이다!
select max(distinct prod_cost), max(prod_cost),
   min(distinct prod_cost), min(prod_cost)
  from prod
--상품중 최고판매가겨과 최저판매가격
select max(prod_sale) 최고판매가,
   min(prod_sale) 최저판매가
  from prod
--상품중 거래처별 최고매입가격과 최저매입가격
select prod_buyer 거래처,
   max(prod_cost) 최고매입가,
   min(prod_cost) 최저매입가
  from prod
  group by prod_buyer
--문제)
--1. 장바구니 테이블의 회원별 최대구매수량을 검색
--   (Alias는 회워ID, 최대수량, 최소수량)
select cart_member 회원ID,
   max(cart_qty) 최대수량,
   min(cart_qty) 최소수량
  from cart
group by cart_member
--2. 오늘이 2002년도 5월 15일이라 가정하고 장바구니 테이블에 발생될 추가주문번호를 검색?
--   (Alais는 최고치주문번호, 추가주문번호)
-- 우선, cart의 내용을 확인한다.
select * from cart
-- 그다음은, 2002년도 5월 15일을 출력하기 위해...
select * from cart where cart_no like '20020515%'
--2002년도 5월 15일의 최고치주문번호를 검색
select max(cart_no) from cart where cart_no like '20020515%'
--*************************** 중 요 *********************************
--2002년도 5월 15일의 최고치주문번호와 추가주문번호를 모두 검색!
--정수형의 범위가 정해져 있기 때문에 convert함수를 썼다.
select max(cart_no) 최고치주문번호, convert(decimal(13),max(cart_no)) + 1 추가주문번호
  from cart where cart_no like '20020515%'

--**************************************************************************
--      함수(GROUP)
--**************************************************************************
--SUM(column) / 조회범위 내 해당컬럼들의 합계
--상품테이블의 매입가의 총합계 값
select sum(distinct prod_cost), sum(prod_cost)
   from prod
--상품테이블의 판매가의 총합계 값
select sum(prod_sale)'상품 판매가 총합계'
   from prod
--상품테이블의 상품분류별 판매가 합계값
select prod_lgu, sum(prod_sale)'상품 분류별 판매가 합계'
   from prod
   group by prod_lgu
--상품입고테이블의 상품별 입고수량의 합계값
select buy_prod 상품, sum(buy_qty)'입고수량합계'
   from buyprod
   group by buy_prod
--문제)
--1. 장바구니테이블의 상품분류별 판매수량의 합계값
--   (Alias는 상품, 판매수량합계)
-- 장바구니테이블 모두 출력
select * from cart
--판매수량합계
select left(cart_prod,4) 상품, sum(cart_qty) 판매수량합계
   from cart
  group by left(cart_prod,4)
--2. 회원테이블의 회원전체의 마일리지 평균, 마일리지 합계, 최고마일리지, 최소마일리지, 인원수를 검색
--   (Alias는 마일리지평균, 마일리지합계, 최고마일리지, 최소마일리지, 인원수)
select avg(mem_mileage) 마일리지평균, sum(mem_mileage) 마일리지합계,
   max(mem_mileage) 최고마일리지, min(mem_mileage) 최소마일리지,
   count(*) 인원수 from member

--**************************************************************************
--      함수(소 GROUP 분리)
--**************************************************************************
--소 GROUP / 집계함수를 제외한 select절에 기술된 column명들은 모두 group by절에 기술!
--       / group by절에 기술된 column명들은 select절에 기술되지 않아도 무방
--       / 하지만 결과를 파악하기 위해서는 select절에 기술해주는 것이 타당
--       / group by절을 기술하면 group by 절에 기술된 column값으로 1개의 table이 소group으로 나눠진다.
             결과는 column값으로 sort되어서 출력된다.
--상품테이블에서 거래처, 상품분류별로 최고판매가, 최소판매가, 자료수를 검색
select * from prod
--
select prod_buyer 거래처, prod_lgu 상품분류,
   max(prod_sale) 최고판매가,
   min(prod_sale) 최소판매가,
   count(prod_sale) 자료수
   from prod
  group by prod_buyer, prod_lgu
--
--1. 장바구니테이블에서 회원, 상품분류별로 구매수량평균, 구매수량합계, 자료수를 검색?
--   (Alias는 회원ID,상품분류,구매수량평균,구매수량합계,자료수)
--   (회원ID, 상품분류 순으로 sort하시요)
select * from cart
--
select cart_member 회원ID, left(cart_prod,4) 상품분류,
   avg(cart_qty) 구매수량평균,
   sum(cart_qty) 구매수량합계, count(cart_qty) 자료수
  from cart
   group by cart_member, left(cart_prod,4)
   order by cart_member, left(cart_prod,4)
--
--2. 회원테이블에서 지역(주소1의 2자리),생일년도별로 마일리지평균,마일리지합계, 최고마일리지,최소마일리지,자료수를 검색?
--   (Alias는 지역,생일연도,마일리지평균,마일리지합계,최고마일리지,최소마일리지,자료수)
select * from member
--
select left(mem_add1,2) 지역, year(mem_bir) 생일연도,
   avg(mem_mileage) 평균, sum(mem_mileage) 합계,
   max(mem_mileage) 최대, min(mem_mileage) 최소,
   count(*) 자료수
  from member
 group by left(mem_add1,2), year(mem_bir)

--*************************************************************************
--      함수(system)
--*************************************************************************
--ISDATE(c) / 타당한 날짜 포맷인지 확인 : 날짜면 1, 아니면 0
--ISNUMERIC(n) / 타당한 숫자포맷인지 확인 : 숫자면 1, 아니면 0
--CASE WHEN / 연속적인 조건문(자주활용되는 함수)
--                     CASE WHEN ~ THEN ~ ELSE ~ END
--
select isdate('20000101') result1,
   isdate('12345678') result2,
   isdate('abc') result3
--
select isnumeric(1234.5678) result1,
   isnumeric('1234.5678') result2,
   isnumeric('ABCDEFG') result3
--
select case when'나'='나' then'맞다'
   else'아니다' end result
--
select case'나'when'철호'then'아니다'
   when'너' then'아니다'
   when'나' then'맞다'
   else'모르겠다' end result
--
select prod_name 상품, prod_lgu 분류,
   상품분류=
   case when prod_lgu = 'p101' then '컴퓨터제품'
      when prod_lgu = 'p102' then '전자제품'
      when prod_lgu = 'p201' then '여성케주얼'
      when prod_lgu = 'p202' then '남성케주얼'
      when prod_lgu = 'p301' then '피혁잡화'
      when prod_lgu = 'p302' then '화장품'
      when prod_lgu = 'p401' then '음반/CD'
      when prod_lgu = 'p402' then '도서'
      when prod_lgu = 'p403' then '문구류'
    else '미등록분류'
   end
  from prod

--10만원 초과 상품판매가 가격대를 검색
select * from prod
--
select prod_name 상품, prod_price 판매가,
    case
      when(100000-prod_price)>0 then'10만원미만'
      when(200000-prod_price)>0 then'10만원대'
      when(300000-prod_price)>0 then'20만원대'
      when(400000-prod_price)>0 then'30만원대'
      when(500000-prod_price)>0 then'40만원대'
      when(600000-prod_price)>0 then'50만원대'
      when(700000-prod_price)>0 then'60만원대'
      when(800000-prod_price)>0 then'70만원대'
      when(900000-prod_price)>0 then'80만원대'
      when(1000000-prod_price)>0 then'90만원대'
    else'100만원이상'
   end'가격대'
  from prod
 where prod_price>100000
--문제)
--회원정보테이블의 주민등록 뒷자리(7자리 중 첫째자리)에서 성별 구분을 검색
--  (Alias는 회원명, 주민등록번호(주민1-주민),성별)
select * from member
--
select (mem_regno2)
  from member
--첫번 째 방법
select mem_name 회원명, mem_regno1+'-'+mem_regno2 주민등록번호,
  case when left(mem_regno2,1)=1 then'남자'
    when left(mem_regno2,1)=2 then'여자'
    else'알수없는성별'
  end'성별'
 from member
--또 다른 방법
select mem_name 회원명, mem_regno1+'-'+mem_regno2 주민등록번호,
  case left(mem_regno2,1) when '1' then '남자'
    when '2' then '여자'
   else'알수없는성별'
  end'성별'
 from member

출처 : Tong - ahnz님의 데이터베이스통

+ Recent posts