본문 바로가기

프로그래밍/MSSQL

MSSQL을 좀더 알기 - TSQL부문

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 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 
  --> 중간에 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
  --> 결과
   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컬럼 마지막 값을 얻음