posted by 생존본능 2011.05.18 12:07

--distinct
중복제거

select distinct job
from emp


--exec sp_help
시스템 내장변수
테이블정보.
exec sp_help emp

비트 연산
2진수변환


--in
Select ename, sal, deptno, job
from emp
where deptno in(10,20)

10보다 크고 20보다 적다.

--like
select ename, sal
from emp
where ename like '%e%'

문자열에서 시작하는 위치

where ename like '_a%' 두번째가 a인사람
where ename like '[as]%' a나 s로 시작하는 사람.
where ename like '[^s]%' s로 시작하지 않는 사람.

 

select reverse('abcdef') as reverse; // 역방향정렬
select replace('abcdef','bc','kkk'); // bc를 kkk로 대체
select STUFF('abcdef',2,3,'kkk'); // 2~3번까지를 kkk로 대체
select round(123.456,1); // 소수점이하 1자리까지 출력
select round(123.456,-1); // 1번자리까지 출력
select power(2,3); // 제곱
select ceiling(123.456);//124 가로안에 있는 숫자보다 큰 정수 출력.
select ceiling(-123.456); //-123가로안에 있는 숫자보다 큰 정수 출력.
select floor(123.456);// 123 // 보다 작은 최대정수
select floor(-123.456);//-124 // 보다 작은 최대정수
select sqrt(9);//제곱근함수(루트)
select SQUARE(9);//81
select abs(100);//절대값
select abs(-100);//절대값


select reverse('abcdef') as reverse;
select replace('abcdef','bc','kkk') as replace;
select STUFF('abcdef',2,3,'kkk') as STUFF;
select round(123.456,1) as round;
select round(123.456,-1) as round;
select power(2,3) as power;
select ceiling(123.456) as ceiling;
select ceiling(-123.456) as ceiling;
select floor(123.456) as floor;
select floor(-123.456) as floor;
select sqrt(9) as sqrt;
select SQUARE(9) as SQUARE;
select abs(100) as abs;
select abs(-100) as abs;


//소스
--select * from emp

/*select empno, ename
from emp
go*/
/*
use master

select *
from test408.dbo.emp
*/

/*select 7+5
select 7*4
select 'tom' + 'bill'*/

select ename as [사원이름], deptno as [부서이름]
from emp

select distinct job
from emp

exec sp_help emp

Select empno, sal, sal*12 As [연봉]
From emp

Select ename, sal, comm, sal+isnull(comm,0)
From emp

Select ename, sal, deptno, job
from emp
where job = 'salesman'

Select ename, sal, deptno, job
from emp
where sal >= 1500 and sal < 3000

Select ename, sal, deptno, job
from emp
where deptno in(10,20)
--where sal between 1500 and 3000

select ename, sal
from emp
where ename like '[s]%'

posted by 생존본능 2011.05.18 12:06

--pivot
select YEAR, [1] as '1분기', [2] as '2분기', [3] as '3분기', [4] as '4분기'
From (select YEAR, quater, amount from PivotT) as t1
pivot( sum(amount)
  for quater in ([1], [2], [3], [4]) ) as pvt;

PIVOT (집계함수(값이있는 열의 이름)
  FOR 피벗하고자하는열 in (목록))


--ROW_NUMBER()
SELECT Year
,  Part
,  quater
,  amount
,  ROW_NUMBER() OVER(ORDER BY 타겟 DESC) AS 'Row Nmber'
FROM PivotT
go

모든 개체의 넘버를 빠지는 번호없이 순서를 정하는 함수.


--RANK()
SELECT Year
,  Part
,  quater
,  amount
,  RANK() OVER(ORDER BY 타겟 DESC) AS 'rank'
FROM PivotT
go

개체의 타겟을 기준으로 순위를 매김.


--DENSE_RANK()
DENSE_RANK() OVER (ORDER BY amount DESC) as 'densh'

중복된 다음의 넘버가 건너뛰는 번호없이 모두 표시됨.


--NTILE(넘버)
NTILE(5) OVER (ORDER BY amount DESC) as 'tile'

0부터 넘버에 표시되는 숫자까지의 수를 균등하게 적용하여 표시됨.


--partition by
RANK() OVER(partition by 타겟 ORDER BY amount DESC) AS 'rank'

표시된 타겟을 같은수끼리 영역으로 묶어 영역별 순위를 매김.

 

 

 

 

 

/*
INSERT PivotT (Year,Part,quater,amount)
values (2001,'g',3,2.7);
*/

SELECT *
FROM PivotT
go

SELECT Year
,  Part
,  quater
,  amount
,  ROW_NUMBER() OVER(ORDER BY amount DESC) AS 'Row Nmber'
,  RANK() OVER(partition by Year ORDER BY amount DESC) AS 'rank'
,  DENSE_RANK() OVER (ORDER BY amount DESC) as 'densh'
,  NTILE(5) OVER (ORDER BY amount DESC) as 'tile'
FROM PivotT
go

posted by 생존본능 2011.05.18 12:05

 

--WHERE 타겟 is not null
null인값은 표시하지 않음.

--group by
같은 데이터를 한데 묶음.

group by Title
Title이 같은 값을 한곳에 모음.

--having
묶인 그룹내에 검색추가.

SELECT 
  Title
,  COUNT(Title) as cnt
FROM Person.Contact
group by Title
HAVING Title = 'Ms.'
or Title = 'Mr.'


--with rollup
전체리스트의 합계라인추가.

SELECT
  CountryRegionName, StateProvinceName, city, COUNT(1) as cnt
FROM
  HumanResources.vEmployee
group by CountryRegionName, StateProvinceName, City
with rollup
order by CountryRegionName, StateProvinceName;

 

--GROUPING 그룹처리될때 원본인지 모여있는 값인지 표시.
SELECT
  CountryRegionName, StateProvinceName, city, COUNT(1) as cnt
,  GROUPING(CountryRegionName) as c1
,  GROUPING(StateProvinceName) as c2
,  GROUPING(City)    as c3
FROM
  HumanResources.vEmployee
group by CountryRegionName, StateProvinceName, City
with rollup
order by CountryRegionName, StateProvinceName;
위와 같을때는
c1에는 '0'(원본) 또는 '1'(모인값)로 표시됨.

 

when(GROUPING(CountryRegionName) = 1)then '총계'
when(GROUPING(StateProvinceName) = 1)then '국별합계'


티스토리 툴바