컴퓨토피아

Q162361 : Understanding and Resolving SQL Server Blocking Problems

Q169960 : Analyzing and Avoiding Deadlocks in SQL Server

Q43199 : Concurrency and Consistency and SQL Server Alternatives

SQL 7.0의 undetected deadlock에 대해서는 Q239753을 참조하세요

K001430 : Transaction Isolation Level을 이해하기

K000733 : Blocking과 Dead-Lock에 대하여

KR224453 : SQL Server 7.0 블로킹 문제 파악 및 해결방법

K001420 : Deadlock 발생을 감소시키는 방법

Cross-Tab 쿼리 생성시 대개 SP에서 처리(Temp테이블 이용)하는것과 동일한
결과 내는 방법입니다.
(Pubs DB이용)
방법1>
SELECT DatePart(yy, Ord_date)년도, SUM( Qty) AS Total,
  SUM(CASE DATEPART(qq,  Ord_date) WHEN 1 THEN  Qty  ELSE 0 END) AS [1분기],
  SUM(CASE DATEPART(qq,  Ord_date) WHEN 2 THEN  Qty  ELSE 0 END) AS [2분기],
  SUM(CASE DATEPART(qq,  Ord_date) WHEN 3 THEN  Qty  ELSE 0 END) AS [3분기],
  SUM(CASE DATEPART(qq,  Ord_date) WHEN 4 THEN  Qty   ELSE 0 END) AS [4분기]
FROM Sales 
GROUP BY DatePart(yy, Ord_date )
Order by 1 

방법2>
SELECT DatePart(yy, Ord_date)년도,  SUM( Qty) AS Total,
  SUM(Qty * (1-ABS(SIGN(DatePart(qq, Ord_date)-1)))) AS [1분기],
  SUM(Qty * (1-ABS(SIGN(DatePart(qq, Ord_date)-2)))) AS [2분기],
  SUM(Qty * (1-ABS(SIGN(DatePart(qq, Ord_date)-3)))) AS [3분기],
  SUM(Qty * (1-ABS(SIGN(DatePart(qq, Ord_date)-4)))) AS [4분기]
From Sales
Group by DatePart(yy, Ord_date)
Order by 1

--------------------------------------------------------
결과 >

년도         Total         1분기         2분기        3분기         4분기
------------------------------------------------------------
1992 80 0 80 0 0
1993 250 60 165 0 25
1994 163 0 0 163 0


* 방법1은 간단한 CASE~WHEN문으로 보시면 아실것 같고,
  방법2의 경우는
(1-ABS(SIGN(DatePart(qq, Ord_date)-1))) 의 경우
각 분기외의 부분은 0으로 처리되어 (Qty * 0), 각분기별로 컬럼이 생성되고
이 컬럼들을 SUM시키게 됩니다.
- ABS와 SIGN 함수는 시퀄내장 함수이므로, 추가적인 사항은
  SQL도움말을 참조하시면 될듯 합니다. 

가끔은 한 테이블에서 검색을 할 때 무작위로 뽑아내고 싶은 경우가 있습니다. 예를 들어 추첨을 하는 경우 필요할 수 있는 요구사항입니다. 이에 대한 아주 간단한 방법을 소개해 드립니다.

1. newid() 함수

NEWID() 함수는 수행이 될 때마다 전혀 중복되지 않는 임의의 값을 만들어 리턴해줍니다. 만일 다음과 같이 newid() 함수의 결과를 SELECT 해보면

SELECT newid()

결과는 다음과 같습니다.

E78CAC2D-85DD-415B-9EEF-2FCBB93A05D3

한번 두 수행해 보면 결과는 다음과 같습니다.

C71014B6-F286-4E4C-B438-05CE509B9BDE

2. newid() 함수를 이용한 무작위 추출

위 내용을 이용하면 무작위로 테이블에서 행을 얻어 낼 수 있습니다. 만일 다음과 같이 Titles 테이블에서 5개의 title_id를 얻는 쿼리문이 있다고 가정하겠습니다.

USE Pubs GO SELECT TOP 5 title_id FROM titles

위 SELECT 문이 수행이 되면 매번 동일한 결과를 얻게 됩니다. Titles 테이블의 값이 변경되지 않는한 똑같은 경과가 나올 수 밖에 없습니다.

하지만 다음과 같이 ORDER BY 문을 사용하게 되면 결과는 매번 달라집니다.

USE Pubs GO SELECT TOP 5 title_id FROM titles ORDER BY newid()

위와 같이 하면 쿼리가 수행되는 중에 newid() 함수에 의해 각 행에 임의의 값이 만들어지고 그 값을 기준으로 정렬해서 상위 5개의 행만을 얻게 되므로 결과가 매번 달라지게 됩니다. 우선 처음 수행을 시켜보면 결과는 다음과 같습니다.

title_id -------- MC2222 BU7832 BU1032 PC8888 MC3026 (5개 행 적용됨)

한번 더 수행해보면 결과는 다음과 같습니다.

title_id -------- BU7832 BU2075 MC3026 PS3333 TC3218 (5개 행 적용됨)

결과가 계속 달라짐을 알 수 있습니다. 확인해 보세요~

3. 정리

참 편리하죠? 만일 위 기능을 추첨에 사용한다고 한다면 한번 추첨된 값은 대상에서 제외하는 처리 루틴을 포함해야 합니다. SELECT 된 결과는 다른 테이블에 기록을 하고 그 테이블에 존재하는 대상을 제외하고 SELECT 되도록 하면 쉽게 해결이 됩니다.