MSSQL에서 사용할 수 있는 TSQL문의 깊은곳
1. SELECT절의 ANY, SOME, ALL
예) SELECT Name, Height FROM userTbl
WHERE height>= ANY(SELECT height FROM userTbl WHERE mobile='019')
--> 서브쿼리의 반환값이 여러개일 경우 각각의 조건을 비교하여 결과를 얻을 수 있다.
--> ANY는 서브쿼리에서 반환되는 조건중 하나만 맞으면 되고
--> ALL일 경우에는 서브쿼리에서 반환되는 모든 조건을 만족 시켜야 한다
--> SOME일 경우에는 ANY일 경우의 >=이 아닌 =과 같은 결과를 얻는다.
2. TOP구문
SELECT 절에서 결과의 갯수를 지정하여 볼 수 있다.
--> 2005 이전에는 TOP n으로 적었지만
--> 2005 이후에는 TOP (n)으로 적는다. 호환성을 위해 TOP n도 먹는다
SELECT TOP(SELECT COUNT(*)/100 FROM Sales.CreaditCard
CreditCardId
FROM Sales.CreditCard
WHERE CardType='Visa'
ORDER BY ExpYear, ExpMonth
--> 이렇게 할 경우에는 전체 갯수를 100으로 나눈 갯수만큼만 보여주라는 의미이며
TOP(0.1) PERCENT 구문은 전체중 상위 0.1% 갯수만을 출력하라는 의미임
--> TOP(0.1) PERCENT WITH TIES일 경우에 해당 마지막 건수와 랭킹상으로 동일한건이 몇건 더 있다면 초과해서 해당 건들까지 함께 보여지도록 하는 의미로, 순위 5등까지 상을 주려고 하는데 5등과 동순위가 2명 더 있을 경우에 해당 2명도 상을 주어야 하는 경우에 "WITH TIE"를 사용함
--> TOP와 유사하게 TABLESAMPLE을 사용할 수 있는데 이때는 전체 건수중 해당 퍼센트의 샘플링된 결과를 임의로 추출해 준다(건수가 많을 때만 정상적으로 값이 나옴)
3. COMPUTE, COMPUTE BY, WITH ROLLUP
집계함수를 통해 전체 또는 중간 집계 정보를 얻을 수 있다.
--> COMPUTE 는 SELECT된 전체에 대한 집계 정보를 얻고
--> COUPUTE BY는 지정된 필드로 그룹핑된 집계 정보를 얻을 수 있다.
예) SELECT *, price*amount FROM byTbl
ORDER BY groupName
COMPUTE SUM(price*amount) By GroupName
COMPUTE AVE(price*amount) By GroupName
COMPUTE SUM(price*amount)
COMPUTE AVG(price*amount)
--> GroupName으로 구분된 중간 집계 정보가 나오고 마지막에 전체에 대한 합계와 평균 정보를 보여준다
COMPUTE를 사용하면 결과 셋이 일관성이 떨어진다. 이럴 경우에 "WITH ROLLUP"을 사용하여 결과 셋의 일관성을 유지하면서 중간 집계 및 전체 집계 정보를 얻을 수 있다.
--> SELECT num, groupName, SUM(price * amount) As '비용'
FROM buyTbl
GROUP BY groupName, num
WITH ROLLUP
--> 결과
--> 중간에 num이 NULL인 것이 중간집계 정보를 나타낸다. 일관성 있는 결과로 프로그래밍 처리가 수월하다.
--> 프로그래밍 할때 해당 중간집계 ROW를 인식하기 위하여 행을 추가할 수 있는데 GROUPING(groupName)을 SELECT절에 추가해 줌으로 인해 해당 필드가 0이면 기존 데이터 ROW이고 1이면 집계를 위해 새로 추가된 ROW임을 알 수 있도록 한다.
--> 다차원 집계를 알고자 한다면 CUBE를 사용할 수 있다.
예) SELECT prodName, color, SUM(amount) AS [수량합계]
FROM cubeTbl
GROUP BY prodName, color
WITH CUBE
--> 결과
4. IDENTITY 컬럼의 마지막 값을 아는 방법
SELECT IDENT_CURRENT('테이블이름') --> 지정된 테이블의 IDENTITY 컬럼의 마지막 값을 얻음
또는
SELECT @@IDENTITY --> 현재 세션에서 마지막으로 생성된 IDENTITY컬럼 마지막 값을 얻음
1. SELECT절의 ANY, SOME, ALL
예) SELECT Name, Height FROM userTbl
WHERE height>= ANY(SELECT height FROM userTbl WHERE mobile='019')
--> 서브쿼리의 반환값이 여러개일 경우 각각의 조건을 비교하여 결과를 얻을 수 있다.
--> ANY는 서브쿼리에서 반환되는 조건중 하나만 맞으면 되고
--> ALL일 경우에는 서브쿼리에서 반환되는 모든 조건을 만족 시켜야 한다
--> SOME일 경우에는 ANY일 경우의 >=이 아닌 =과 같은 결과를 얻는다.
2. TOP구문
SELECT 절에서 결과의 갯수를 지정하여 볼 수 있다.
--> 2005 이전에는 TOP n으로 적었지만
--> 2005 이후에는 TOP (n)으로 적는다. 호환성을 위해 TOP n도 먹는다
SELECT TOP(SELECT COUNT(*)/100 FROM Sales.CreaditCard
CreditCardId
FROM Sales.CreditCard
WHERE CardType='Visa'
ORDER BY ExpYear, ExpMonth
--> 이렇게 할 경우에는 전체 갯수를 100으로 나눈 갯수만큼만 보여주라는 의미이며
TOP(0.1) PERCENT 구문은 전체중 상위 0.1% 갯수만을 출력하라는 의미임
--> TOP(0.1) PERCENT WITH TIES일 경우에 해당 마지막 건수와 랭킹상으로 동일한건이 몇건 더 있다면 초과해서 해당 건들까지 함께 보여지도록 하는 의미로, 순위 5등까지 상을 주려고 하는데 5등과 동순위가 2명 더 있을 경우에 해당 2명도 상을 주어야 하는 경우에 "WITH TIE"를 사용함
--> TOP와 유사하게 TABLESAMPLE을 사용할 수 있는데 이때는 전체 건수중 해당 퍼센트의 샘플링된 결과를 임의로 추출해 준다(건수가 많을 때만 정상적으로 값이 나옴)
3. COMPUTE, COMPUTE BY, WITH ROLLUP
집계함수를 통해 전체 또는 중간 집계 정보를 얻을 수 있다.
--> COMPUTE 는 SELECT된 전체에 대한 집계 정보를 얻고
--> COUPUTE BY는 지정된 필드로 그룹핑된 집계 정보를 얻을 수 있다.
예) SELECT *, price*amount FROM byTbl
ORDER BY groupName
COMPUTE SUM(price*amount) By GroupName
COMPUTE AVE(price*amount) By GroupName
COMPUTE SUM(price*amount)
COMPUTE AVG(price*amount)
--> GroupName으로 구분된 중간 집계 정보가 나오고 마지막에 전체에 대한 합계와 평균 정보를 보여준다
COMPUTE를 사용하면 결과 셋이 일관성이 떨어진다. 이럴 경우에 "WITH ROLLUP"을 사용하여 결과 셋의 일관성을 유지하면서 중간 집계 및 전체 집계 정보를 얻을 수 있다.
--> SELECT num, groupName, SUM(price * amount) As '비용'
FROM buyTbl
GROUP BY groupName, num
WITH ROLLUP
--> 결과
num | groupName | 비용 | |
1 | 1 | NULL | 90 |
2 | 10 | NULL | 90 |
3 | 12 | NULL | 90 |
4 | NULL | NULL | 270 |
5 | 7 | 서적 | 110 |
6 | 8 | 서적 | 44 |
7 | 11 | 서적 | 22 |
8 | NULL | 서적 | 176 |
9 | 5 | 의류 | 225 |
--> 프로그래밍 할때 해당 중간집계 ROW를 인식하기 위하여 행을 추가할 수 있는데 GROUPING(groupName)을 SELECT절에 추가해 줌으로 인해 해당 필드가 0이면 기존 데이터 ROW이고 1이면 집계를 위해 새로 추가된 ROW임을 알 수 있도록 한다.
--> 다차원 집계를 알고자 한다면 CUBE를 사용할 수 있다.
예) SELECT prodName, color, SUM(amount) AS [수량합계]
FROM cubeTbl
GROUP BY prodName, color
WITH CUBE
--> 결과
prodName | color | 수량합계 | |
1 | 모니터 | 검정 | 33 |
2 | 모니터 | 파랑 | 44 |
3 | 모니터 | NULL | 77 |
4 | 컴퓨터 | 검정 | 11 |
5 | 컴퓨터 | 파랑 | 22 |
6 | 컴퓨터 | NULL | 33 |
7 | NULL | NULL | 110 |
8 | NULL | 검정 | 44 |
9 | NULL | 파랑 | 66 |
4. IDENTITY 컬럼의 마지막 값을 아는 방법
SELECT IDENT_CURRENT('테이블이름') --> 지정된 테이블의 IDENTITY 컬럼의 마지막 값을 얻음
또는
SELECT @@IDENTITY --> 현재 세션에서 마지막으로 생성된 IDENTITY컬럼 마지막 값을 얻음
'프로그래밍 > MSSQL' 카테고리의 다른 글
MSSQL 2005 - 파일그룹 생성 SQL 예제 (4) | 2011.02.07 |
---|---|
MSSQL CREATE DATABASE 쿼리 SAMPLE (1) | 2011.02.07 |
[MSSQL 2005]샘플DB AdventureWorksDB를 지웠을 경우 복구 방법 (1) | 2011.02.07 |
WITH CTE_TABLE을 이용한 쿼리문의 단순화 (1) | 2011.01.31 |
[MSSQL]테이블 소유자 일괄 변경 (0) | 2010.08.20 |