우리는 우리에게 주어진 공간이 한정되고
우리에게 주어진 시간 또한 유한하다는 것을 깨닫기 위해
여행을 떠나는 게 아닐까요. 그리하여 우리 자신과
우리를 둘러싼 배경에 애정을 갖게 되고, 나아가
불필요한 욕망이나 중요하지 않은 모든 가치들을
과감히 버리고 생에 매진 할 수 있는 용기를
얻는 것, 그것이 우리가 여행을 하는
목적이 아닐까요?
- 황시내의《황금물고기》 중에서 -
* 살다보면 용기를 잃고 헤매일 때가 많습니다.
너무 바쁜 나머지 정리할 시간도 없고 주위에 애정을
가질 여유도 없는 것 같습니다. 그럴 때 필요한 것이
여행인 것 같습니다. 다시금 생에 매진할 수 있는
용기도 얻고 생각의 정리도 하기 위해 가까운
곳으로라도 당장 여행을 떠나고 싶습니다.
------------------------------------------------------------
--EX)Employee 테이블 조회
------------------------------------------------------------
SELECT EmployeeID, EmployeeName
FROM Employee
------------------------------------------------------------
--EX)WHERE절 사용
------------------------------------------------------------
SELECT *
FROM Employee
WHERE EmployeeUD = 'ahsoo'
------------------------------------------------------------
--EX)AND 조건의 사용
------------------------------------------------------------
SELECT *
FROM Employee
WHERE BirthYMD <= '19800101'
AND JoinYMD >= '20050601'
------------------------------------------------------------
--EX)OR 조건의 사용(UNION/UNION ALL로도 커버 가능)
------------------------------------------------------------
SELECT *
FROM Employee
WHERE BirthYMD <= '19800101'
OR JoinYMD >= '20050601'
------------------------------------------------------------
--EX)1월1일이나 7월1일에 입사한 직원 찾아내기(UNION뿐 아니라 IN으로도 커버)
------------------------------------------------------------
SELECT *
FROM Employee
WHERE JoinYMD = '20050701'
OR JoinYMD = '20050101'
------------------------------------------------------------
--EX)1월1일이나 7월1일에 입사했고 EmployeeType이 0001인 경우
------------------------------------------------------------
SELECT *
FROM Employee
WHERE JoinYMD = '20050701'
OR JoinYMD = '20050101'
AND EmployeeType = '0001'
-- 총12건의 데이터가 나오고 EmployeeType이 0002인 데이터도 존재
-- 1월1일 입사했고, EmployeeType이 0001이거나 입사일이 7월1일인 데이터가 조회됨
------------------------------------------------------------
--EX)괄호를 사용 – 정확한 데이터가 조회됨
------------------------------------------------------------
SELECT *
FROM Employee
WHERE (JoinYMD = '20050701'
OR JoinYMD = '20050101')
AND EmployeeType = '0001'
------------------------------------------------------------
--EX)IN의 사용
------------------------------------------------------------
SELECT *
FROM Employee
WHERE JoinYMD IN ('20050701', '20050101')
AND EmployeeType = '0001'
------------------------------------------------------------
--EX)LIKE의 사용
------------------------------------------------------------
SELECT *
FROM Employee
WHERE EmployeeID LIKE 'a%'
go
SELECT *
FROM Employee
WHERE EmployeeID LIKE 'h%77'
go
SELECT *
FROM Employee
WHERE EmployeeID LIKE '___[0-9][0-9]'
go
SELECT *
FROM Employee
WHERE EmployeeID LIKE '[as]%'
go
SELECT *
FROM Employee
WHERE EmployeeID LIKE '[^as]%'
go
------------------------------------------------------------
--EX)NULL값에 대한 조회
------------------------------------------------------------
SELECT *
FROM Employee
WHERE RetireYMD = NULL
go
SELECT *
FROM Employee
WHERE RetireYMD IS NULL
go
SELECT *
FROM Employee
WHERE RetireYMD IS NOT NULL
go
------------------------------------------------------------
--EX)BETWEEN의 사용
------------------------------------------------------------
SELECT *
FROM Employee
WHERE JoinYMD BETWEEN '20050601' AND '20050701'
-- BETWEEN연산자는 >=와 <=를 섞어 쓴것과 동일
------------------------------------------------------------
--EX)별칭의 여러가지 유형
------------------------------------------------------------
SELECT T1.EmployeeID as EmpID,
T1.EmployeeType EmpType,
EmpName = T1.EmployeeNAme
FROM Employee T1
------------------------------------------------------------
--EX)별칭 사용시 SQL의 작성이 보다 쉬워진다.
------------------------------------------------------------
SELECT SalesmanSeq, Amount
FROM Sales
INNER JOIN ShopSalesman
ON SalesmanSeq = SalesmanSeq
WHERE SaleYMD = '20060201'
go
--'열 이름 'SalesmanSeq'이(가) 불확실합니다.'와 같은 에러 발생
--중복되는 컬럼명에 테이블명을 명시해준다.
SELECT ShopSalesman.SalesmanSeq, Sales.Amount
FROM Sales
INNER JOIN ShopSalesman
ON Sales.SalesmanSeq = ShopSalesman.SalesmanSeq
WHERE Sales.SaleYMD = '20060201'
go
-- 작성이 번거로움, 별칭사용
SELECT T2.SalesmanSeq, T1.Amount
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T1.SalesmanSeq = T2.SalesmanSeq
WHERE T1.SaleYMD = '20060201'
go
------------------------------------------------------------
--EX)SELECT문을 사용한 INSERT문(Test직원을 이용해 Test1직원을 만든다.)
------------------------------------------------------------
INSERT INTO Employee
(EmployeeID, EmployeeName, EmployeeType,
BirthYMD, JoinYMD, RetireYMD,
PhoneNo, EmailAddress, MobileNo,
Address, EmployeeState)
SELECT 'Test1', '테스트1', EmployeeType,
BirthYMD, JoinYMD, RetireYMD,
PhoneNo, EmailAddress, MobileNo,
Address, EmployeeState
FROM Employee
WHERE EmployeeID = 'Test'
------------------------------------------------------------
--EX)UPATE문의 사용
------------------------------------------------------------
UPDATE Employee
SET JoinYMD = '20060101'
WHERE EmployeeID = 'Test'
------------------------------------------------------------
--EX)여러 개의 컬럼을 한 번에 UPDATE할 수 있다.
------------------------------------------------------------
UPDATE Employee
SET JoinYMD = '20060201',
RetireYMD = '20061231'
WHERE EmployeeID = 'Test'
------------------------------------------------------------
--EX)UPDATE문장에 별칭과 FROM절 사용
------------------------------------------------------------
UPDATE T1
SET T1.JoinYMD = '20061231',
T1.RetireYMD = '20060201'
FROM Employee T1
WHERE T1.EmployeeID = 'Test1'
------------------------------------------------------------
--EX)두 컬럼간의 값을 변경하기(이전의 값들을 참조하므로 가능)
------------------------------------------------------------
UPDATE T1
SET T1.RetireYMD = T1.JoinYMD,
T1.JoinYMD = T1.RetireYMD
FROM Employee T1
WHERE T1.EmployeeID = 'Test1'
------------------------------------------------------------
--EX)DELETE문의 사용
------------------------------------------------------------
DELETE Employee
WHERE EmployeeID = 'Test1'
------------------------------------------------------------
--EX)DELETE문장에 별칭과 FROM절 사용
------------------------------------------------------------
DELETE T1
FROM Employee T1
WHERE EmployeeID = 'Test'
------------------------------------------------------------
--EX)기본적인 ORDER BY
------------------------------------------------------------
SELECT *
FROM Sales
WHERE SaleYMD LIKE '200501%'
ORDER BY SaleYMD DESC
------------------------------------------------------------
--EX)DESC, ASC지정한 ORDER BY
------------------------------------------------------------
SELECT *
FROM Sales
WHERE SaleYMD LIKE '200501%'
ORDER BY ModelID ASC, SaleYMD DESC, SalesTime DESC
------------------------------------------------------------
--EX)고정형 데이터와 가변형 데이터의 차이
------------------------------------------------------------
SELECT '1' + cast('234' as varchar(10)) + '5'
SELECT '1' + cast('234' as char(10)) + '5'
INSERT INTO #TBL(float_val, numeric_val)
VALUES (12.13142132, 12.13142132)
INSERT INTO #TBL(float_val, numeric_val)
VALUES (22.13142136, 22.13142136)
SELECT *
FROM #TBL
--데이터를 조회하면, 원래 입력한 데이터와 틀리다는 것을 알 수 있다.
--근사 숫자로 자료형을 만들 경우 실제 원하는 결과와 틀린 결과가 나올 수 있다는 것을 유념해 두자.
------------------------------------------------------------
--EX)특정 일자의 데이터를 조회하는 방법
------------------------------------------------------------
--한건의 데이터도 조회되지 않는다.
SELECT *
FROM Sales
WHERE RegiDT = '2005-03-01'
--3월 2일 데이터가 포함될 수 있다.
SELECT *
FROM Sales
WHERE RegiDT BETWEEN '2005-03-01 00:00:00' AND '2005-03-02 00:00:00'
--제대로 된 구간 조회
SELECT *
FROM Sales
WHERE RegiDT >= '2005-03-01 00:00:00'
AND RegiDT < '2005-03-02 00:00:00'
------------------------------------------------------------
--EX)날짜 및 시간 함수
------------------------------------------------------------
--GETDATE() : 현재 날짜 가져오기
SELECT GETDATE()
go
--DATEADD() : 현재 날짜에서 한달을 더하고 빼기
SELECT DATEADD(m, 1, GETDATE()), DATEADD(m, -1, GETDATE())
go
--DATEDIFF() : 날짜간의 차이
SELECT DATEDIFF(yy, '20060401', '20060520') [년도차이],
DATEDIFF(mm, '20060401', '20060520') [월차이],
DATEDIFF(dd, '20060401', '20060520') [일수차이]
go
--DATEPART() : 해당일자의 특정 부분 값 알아내기
SELECT DATEPART(yy, GETDATE()) [년],
DATEPART(mm, GETDATE()) [월],
DATEPART(dd, GETDATE()) [일자],
DATEPART(dw, GETDATE()) [요일], --1은 일요일, 7은 토요일
DATEPART(hh, GETDATE()) [시],
DATEPART(mm, '2006-05-21 09:10:15') [분],
DATEPART(ss, '2006-05-21 09:10:15') [초]
go
------------------------------------------------------------
--EX)DISTINCT 사용
------------------------------------------------------------
--판매일자 중복제거하기
SELECT DISTINCT T1.SaleYMD
FROM Sales T1
WHERE SaleYMD BETWEEN '20060501' AND '20060531'
ORDER BY T1.SaleYMD
go
--판매된 모델 중복 제거하기
SELECT DISTINCT T1.ModelID
FROM Sales T1
WHERE SaleYMD BETWEEN '20060501' AND '20060531'
ORDER BY T1.ModelID
go
--판매일자 모델별 중복 제거하기
SELECT DISTINCT T1.SaleYMD, T1.ModelID
FROM Sales T1
WHERE SaleYMD BETWEEN '20060501' AND '20060531'
ORDER BY T1.SaleYMD, T1.ModelID
go
------------------------------------------------------------
--EX)GROUP BY 기본 구문
------------------------------------------------------------
--직원타입별로 직원 카운트 하기
SELECT T1.EmployeeType, COUNT(*)
FROM Employee T1
GROUP BY T1.EmployeeType
--직원 타입, 직원의 입사년도 별로 카운트 하기
SELECT T1.EmployeeType,
LEFT(T1.JoinYMD, 4) JoinYY,
COUNT(*)
FROM Employee T1
GROUP BY T1.EmployeeType, LEFT(T1.JoinYMD, 4)
ORDER BY T1.EmployeeType, LEFT(T1.JoinYMD, 4)
------------------------------------------------------------
--EX)GROUP BY와 집계함수의 사용
------------------------------------------------------------
--일자별, 모델별 판매 카운트 구하기
SELECT T1.SaleYMD, T1.ModelID, COUNT(T1.Qty)
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY T1.SaleYMD, T1.ModelID
ORDER BY T1.SaleYMD, T1.ModelID
go
--일별 가장 높은 금액, 가장 낮은 금액 구하기
SELECT T1.SaleYMD, MAX(T1.Amount), MIN(T1.Amount)
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY T1.SaleYMD
ORDER BY T1.SaleYMD
go
--일자별로 판매 수량 구하기
SELECT T1.SaleYMD, SUM(T1.Qty)
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY T1.SaleYMD
ORDER BY T1.SaleYMD
go
--모델별로 판매수량 구하기
SELECT T1.ModelID, SUM(T1.Qty)
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY T1.ModelID
ORDER BY T1.ModelID
go
--일별 평균 판매 금액 구하기
SELECT T1.SaleYMD, CONVERT(numeric(18,2), AVG(T1.Amount))
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY T1.SaleYMD
ORDER BY T1.SaleYMD
------------------------------------------------------------
--EX)GROUP BY되는 컬럼의 변경
------------------------------------------------------------
--요일별 판매 수량 구하기
SELECT DATEPART(dw, T1.SaleYMD), DATENAME(dw, T1.SaleYMD), SUM(T1.Qty)
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY DATEPART(dw, T1.SaleYMD), DATENAME(dw, T1.SaleYMD)
ORDER BY DATEPART(dw, T1.SaleYMD)
------------------------------------------------------------
--EX)HAVING 사용하기
------------------------------------------------------------
--특정 기간내 판매금액이 1억보다 작은 모델
SELECT T1.ModelID, SUM(T1.Amount)
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY T1.ModelID
HAVING SUM(T1.Amount) <= 100000000
go
--특정 기간내 판매금액이 1억보다 큰 모델
SELECT T1.ModelID, SUM(T1.Amount)
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY T1.ModelID
HAVING SUM(T1.Amount) >= 100000000
go
--특정 기간내 일별로 집계된 판매금애에서 2006년 5월 3일 데이터만 찾아내기
SELECT T1.SaleYMD, SUM(T1.Amount)
FROM Sales T1
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060531'
GROUP BY T1.SaleYMD
HAVING T1.SaleYMD = '20060531'
go
--WHERE절로 조건을 옮기는 것과 동일
SELECT T1.SaleYMD, SUM(T1.Amount)
FROM Sales T1
WHERE T1.SaleYMD = '20060531'
GROUP BY T1.SaleYMD
EX)HAVING절을 사용해서 중복된 데이터 찾아내기
--한 상점에 두 명 이상의 판매원이 등록된 경우
SELECT T1.ShopID, COUNT(*)
FROM ShopSalesman T1
WHERE T1.SalesEndYMD = '99991231'
GROUP BY T1.ShopID
HAVING COUNT(*)>=2
------------------------------------------------------------
--응용EX)GROUP BY 1.판매 테이블에 대해서 각 년월별로 판매금액을 집계
------------------------------------------------------------
년 + 해당 년도의 총 판매 금액
SELECT LEFT(T1.SaleYMD, 4) SaleYY,
SUM(T1.Amount) TTL_AMT
FROM Sales T1
GROUP BY LEFT(T1.SaleYMD, 4)
YYYYMMDD날짜 형식에서 년도를 가져오는 다른 방법
SELECT DATEPART(yy, T1.SaleYMD) SaleYY,
SUM(T1.Amount) TTL_AMT
FROM Sales T1
GROUP BY DATEPART(yy, T1.SaleYMD)
------------------------------------------------------------
--응용EX)GROUP BY 2.2006년 5월 판매 데이터에 대해서 각 모델별 판매 수량을 집계
------------------------------------------------------------
모델ID + 해당모델의 총 판매 수량
SELECT T1.ModelID, SUM(T1.Qty) TTL_Qty
FROM Sales T1
WHERE T1.SaleYMD LIKE '200605%'
GROUP BY T1.ModelID
ORDER BY T1.ModelID
------------------------------------------------------------
--응용EX)GROUP BY 3.2006년 판매 데이터에 대해서 각 월별, 판매수량과 판매 금액
------------------------------------------------------------
년월 + 모델ID + 해당월 모델의 판매 수량 + 해당월 모델의 판매 금액
SELECT LEFT(T1.SaleYMD, 6) SaleYM, T1.ModelID,
SUM(T1.Qty) TTL_Qty, SUM(T1.Amount) TTL_Amt
FROM Sales T1
WHERE T1.SaleYMD LIKE '2006%'
GROUP BY LEFT(T1.SaleYMD, 6), T1.ModelID
ORDER BY LEFT(T1.SaleYMD, 6), T1.ModelID
------------------------------------------------------------
--응용EX)GROUP BY 4.2006년 5월 판매 데이터중 판매 수량이 200 이상인 데이터만 추출
------------------------------------------------------------
모델ID + 판매수량(200이상인 경우만)
SELECT T1.ModelID, SUM(T1.Qty) TTL_Qty
FROM Sales T1
WHERE T1.SaleYMD LIKE '200605%'
GROUP BY ModelID
HAVING SUM(T1.Qty) >= 200
ORDER BY SUM(T1.Qty) ASC --ORDER BY에 집계된 내용이 올 수 있다.
------------------------------------------------------------
--응용EX)GROUP BY 5.2006년 4월에서 각 요일별 시간대별 판매 수량 구하기
------------------------------------------------------------
요일 + 시 + 판매 수량
SELECT DATENAME(dw, T1.SaleYMD) SaleDW,
LEFT(SalesTime, 2) SalesTime,
SUM(T1.Qty)
FROM Sales T1
WHERE T1.SaleYMD LIKE '200604%'
GROUP BY DATEPART(dw, T1.SaleYMD), DATENAME(dw, T1.SaleYMD), LEFT(SalesTime, 2)
ORDER BY DATEPART(dw, T1.SaleYMD), LEFT(SalesTime, 2)
-- DATENAME과 DATEPART에 dw를 사용하면 일자(YYYYMMDD)에 대해 요일을 찾을 수 있게 된다.
-- GROUP BY 에서 DATEPART를 사용한 이유는 DATEPART는 요일에 대해 1부터 7로 숫자로 표현해주고,
-- DATENAME은 요일에 대해 월~일같은 문자로 표기해 준다. 이때 정렬을 문자가 아닌 숫자로 하는 것이
-- 우리가 일반적으로 알고 있는 요일대로 정렬이 되기 때문에 실제 SELECT절에서는 사용하지 않지만
-- GROUP BY에 DATEPART를 한번 다 사용하게 된다.
------------------------------------------------------------
--EX)CASE 기본 구문1의 사용1 -> 2006년 3월 데이터의 판매일자에 대한 요일을 영문 3문자로 표기.
------------------------------------------------------------
SELECT T1.SaleYMD,
CASE WHEN DATEPART(dw, T1.SaleYMD) = 1 THEN 'Sun'
WHEN DATEPART(dw, T1.SaleYMD) = 2 THEN 'Mon'
WHEN DATEPART(dw, T1.SaleYMD) = 3 THEN 'Tue'
WHEN DATEPART(dw, T1.SaleYMD) = 4 THEN 'Wed'
WHEN DATEPART(dw, T1.SaleYMD) = 5 THEN 'Thu'
WHEN DATEPART(dw, T1.SaleYMD) = 6 THEN 'Fri'
WHEN DATEPART(dw, T1.SaleYMD) = 7 THEN 'Sat'
END DayOfWeek,
T1.ModelID, T1.Qty
FROM Sales T1
WHERE T1.SaleYMD LIKE '200603%'
CASE의 기본 구문2 : CASE를 사용하는 또 다른 구문이 있다.
SELECT CASE <값1> WHEN <값2> THEN <결과값1>
WHEN <값3> THEN <결과값2>
…
ELSE <결과값3>
END
------------------------------------------------------------
--EX)CASE 기본 구문1의 사용2 -> 2006년 6월 데이터에 대해 판매 금액이 판매 금액이 백만원보다 낮으면 Low로
--백만원을 넘고 이백만원 사이이면 Mid로 이백만원 이상일 경우는 High로 판매에 대해 가격 등급을 구하기.
------------------------------------------------------------
SELECT T1.SaleYMD,
T1.ModelID,
T1.Qty,
T1.Amount,
CASE WHEN T1.Amount < 1000000 THEN 'Low'
WHEN T1.Amount >= 1000000 AND T1.Amount < 2000000 THEN 'Mid'
WHEN T1.Amount >= 2000000 THEN 'High'
END PriceGrade
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
--각격등급, 모델별 정렬을 해보도록 하자. -> ORDER BY에도 CASE문을 그대로 사용할 수 있다.
SELECT T1.SaleYMD,
T1.ModelID,
T1.Qty,
T1.Amount,
CASE WHEN T1.Amount < 1000000 THEN 'Low'
WHEN T1.Amount >= 1000000 AND T1.Amount < 2000000 THEN 'Mid'
WHEN T1.Amount >= 2000000 THEN 'High'
END PriceGrade
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
ORDER BY
CASE WHEN T1.Amount < 1000000 THEN 0
WHEN T1.Amount >= 1000000 AND T1.Amount < 2000000 THEN 1
WHEN T1.Amount >= 2000000 THEN 2
END DESC, --High인 가격부터 나오도록 처리 -> Low, Mid, High를 그대로 사용하면 정렬은 알파벳순으로 된다.(H->L->M)
T1.ModelID
--CASE문에 적은 조건들중에 가장 위에 조건부터 처리 된다. 즉, 위의 예제에서
--CASE의 첫번째 조건인 Amount가 백만 미만인 경우가 아닌 경우에만 두번째 조건이 수행된다. 그러므로 두 번째 조건에서
--백만 이상이란 조건은 필요없다. 마찬가지로 첫 번째 조건과 두 번째 조건을 모두 만족시키지 않은 Amount는 당연히 이백만 이상인 금액이므로
--3번째 조건을 사용하지 않고 ELSE문으로 처리해도 된다.
SELECT T1.SaleYMD,
T1.ModelID,
T1.Qty,
T1.Amount,
CASE WHEN T1.Amount < 1000000 THEN 'Low'
WHEN T1.Amount < 2000000 THEN 'Mid'
ELSE 'High'
END PriceGrade
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
ORDER BY
CASE WHEN T1.Amount < 1000000 THEN 0
WHEN T1.Amount < 2000000 THEN 1
ELSE 2
END DESC, --High인 가격부터 나오도록 처리 -> Low, Mid, High를 그대로 사용하면 정렬은 알파벳순으로 된다.(H->L->M)
T1.ModelID
------------------------------------------------------------
--EX)CASE 기본 구문2의 사용 2-2006년 3월 데이터의 판매일자에 대한 요일을 영문 3문자로 표기
------------------------------------------------------------
SELECT T1.SaleYMD,
CASE DATEPART(dw, T1.SaleYMD)
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat'
END DayOfWeek,
T1.ModelID, T1.Qty
FROM Sales T1
WHERE T1.SaleYMD LIKE '200603%'
------------------------------------------------------------
--EX)CASE를 이용한 GROUP BY이해하기->이전에 사용한 각격대별로 GROUP BY를 지어서 가격대별 판매수량을 구한다.
--Low 등급 : 백만원 미만 가격
--Mid 등급 : 백만원 이상 이백만원 미만 가격
--High 등급 : 이백만원 이상 가격
------------------------------------------------------------
SELECT CASE WHEN T1.Amount < 1000000 THEN 'Low'
WHEN T1.Amount < 2000000 THEN 'Mid'
ELSE 'High'
END PriceGrade,
SUM(T1.Qty) QtyByPriceGrade
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY
CASE WHEN T1.Amount < 1000000 THEN 'Low'
WHEN T1.Amount < 2000000 THEN 'Mid'
ELSE 'High'
END
------------------------------------------------------------
--EX)CASE의 조건절에서 여러 조건 사용하기
--월요일부터 금요일까지의 가격등급은 이전과 동일하나 토요일, 일요일일 경우에는 다른 가격등급을 적용한다.
--평일의 각격 등급
-- Low 등급 : 백만원 미만 가격
-- Mid 등급 : 백만원 이상 이백만원 미만 가격
-- High 등급 : 이백만원 이상 가격
--토,일요일의 각격 등급
-- Low 등급 : 120만원 미만 가격
-- Mid 등급 : 120만원 이상 210만원 미만 가격
-- High 등급 : 210만원 이상 가격
------------------------------------------------------------
SELECT CASE WHEN DATEPART(dw, T1.SaleYMD) IN (1, 7) AND T1.Amount < 1200000 THEN 'Low'
WHEN DATEPART(dw, T1.SaleYMD) IN (1, 7) AND T1.Amount < 2100000 THEN 'Mid'
WHEN DATEPART(dw, T1.SaleYMD) IN (1, 7) AND T1.Amount >= 2100000 THEN 'High'
WHEN DATEPART(dw, T1.SaleYMD) NOT IN (1, 7) AND T1.Amount < 1000000 THEN 'Low'
WHEN DATEPART(dw, T1.SaleYMD) NOT IN (1, 7) AND T1.Amount < 2000000 THEN 'Mid'
WHEN DATEPART(dw, T1.SaleYMD) NOT IN (1, 7) AND T1.Amount >= 2000000 THEN 'High'
END PriceGrade,
SUM(T1.Qty) QtyByPriceGrade
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY
CASE WHEN DATEPART(dw, T1.SaleYMD) IN (1, 7) AND T1.Amount < 1200000 THEN 'Low'
WHEN DATEPART(dw, T1.SaleYMD) IN (1, 7) AND T1.Amount < 2100000 THEN 'Mid'
WHEN DATEPART(dw, T1.SaleYMD) IN (1, 7) AND T1.Amount >= 2100000 THEN 'High'
WHEN DATEPART(dw, T1.SaleYMD) NOT IN (1, 7) AND T1.Amount < 1000000 THEN 'Low'
WHEN DATEPART(dw, T1.SaleYMD) NOT IN (1, 7) AND T1.Amount < 2000000 THEN 'Mid'
WHEN DATEPART(dw, T1.SaleYMD) NOT IN (1, 7) AND T1.Amount >= 2000000 THEN 'High'
END
------------------------------------------------------------
--EX)중첩된 CASE문 사용하기 - 위와 동일한 SQL을 중첩된 CASE문으로 해결.
------------------------------------------------------------
SELECT CASE WHEN DATEPART(dw, T1.SaleYMD) IN (1, 7) THEN
CASE WHEN T1.Amount < 1200000 THEN 'Low'
WHEN T1.Amount < 2100000 THEN 'Mid'
WHEN T1.Amount >= 2100000 THEN 'High'
END
WHEN DATEPART(dw, T1.SaleYMD) NOT IN (1, 7) THEN
CASE WHEN T1.Amount < 1000000 THEN 'Low'
WHEN T1.Amount < 2000000 THEN 'Mid'
WHEN T1.Amount >= 2000000 THEN 'High'
END
END PriceGrade,
SUM(T1.Qty) QtyByPriceGrade
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY
CASE WHEN DATEPART(dw, T1.SaleYMD) IN (1, 7) THEN
CASE WHEN T1.Amount < 1200000 THEN 'Low'
WHEN T1.Amount < 2100000 THEN 'Mid'
WHEN T1.Amount >= 2100000 THEN 'High'
END
WHEN DATEPART(dw, T1.SaleYMD) NOT IN (1, 7) THEN
CASE WHEN T1.Amount < 1000000 THEN 'Low'
WHEN T1.Amount < 2000000 THEN 'Mid'
WHEN T1.Amount >= 2000000 THEN 'High'
END
END
------------------------------------------------------------
--응용EX)CASE 예제1.2006년3월 1일부터 10일까지의 판매에 대해서 1일부터 10일까지의 모델별 판매 수량 보여주기.
--여기서 1일부터 10일은 컬럼이 되어야 한다.
--결과의 키를 찾아내는 훈련이 필요하다. -> 모델별 집계이므로 모델ID가 키가 된다.
--GROUP BY ModelID를 하면 ModelID가 키가된 결과집합을 만들어 내게 된다.
------------------------------------------------------------
모델ID + 1일판매수량 + 2일판매수량 + ... + 10일판매수량
SELECT T1.ModelID,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '01' THEN T1.Qty ELSE 0 END) QTY_1,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '02' THEN T1.Qty ELSE 0 END) QTY_2,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '03' THEN T1.Qty ELSE 0 END) QTY_3,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '04' THEN T1.Qty ELSE 0 END) QTY_4,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '05' THEN T1.Qty ELSE 0 END) QTY_5,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '06' THEN T1.Qty ELSE 0 END) QTY_6,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '07' THEN T1.Qty ELSE 0 END) QTY_7,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '08' THEN T1.Qty ELSE 0 END) QTY_8,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '09' THEN T1.Qty ELSE 0 END) QTY_9,
SUM(CASE WHEN RIGHT(T1.SaleYMD,2) = '10' THEN T1.Qty ELSE 0 END) QTY_10
FROM Sales T1
WHERE T1.SaleYMD >= '20060301'
AND T1.SaleYMD <= '20060310'
GROUP BY T1.ModelID
ORDER BY T1.ModelID
------------------------------------------------------------
--응용EX)CASE 예제2.2006년 2월 판매에 대해 모델별 요일별 판매수량 보여주기.
--요일은 컬럼이 되어야 한다.
------------------------------------------------------------
모델ID + 월요일 + 화요일 + 수요일 + ... + 일요일
SELECT T1.ModelID,
SUM(CASE WHEN DATEPART(dw, T1.SaleYMD) = 2 THEN T1.Qty ELSE 0 END) QTY_Mon,
SUM(CASE WHEN DATEPART(dw, T1.SaleYMD) = 3 THEN T1.Qty ELSE 0 END) QTY_Tue,
SUM(CASE WHEN DATEPART(dw, T1.SaleYMD) = 4 THEN T1.Qty ELSE 0 END) QTY_Wed,
SUM(CASE WHEN DATEPART(dw, T1.SaleYMD) = 5 THEN T1.Qty ELSE 0 END) QTY_Thu,
SUM(CASE WHEN DATEPART(dw, T1.SaleYMD) = 6 THEN T1.Qty ELSE 0 END) QTY_Fri,
SUM(CASE WHEN DATEPART(dw, T1.SaleYMD) = 7 THEN T1.Qty ELSE 0 END) QTY_Sat,
SUM(CASE WHEN DATEPART(dw, T1.SaleYMD) = 1 THEN T1.Qty ELSE 0 END) QTY_Sun
FROM Sales T1
WHERE T1.SaleYMD LIKE '200602%'
GROUP BY T1.ModelID
------------------------------------------------------------
--응용EX)CASE 예제3.2006년 3월 판매에 대해서 모델별로 월별 판매 수량을 구해서 모델별 판매 수량 등급을 구한다.
--판매수량 등급 : 300개 미만 판매 : LowSale
-- : 300개 이상 450개 미만 판매 : MidSale
-- : 450개 이상 판매 : HighSale
------------------------------------------------------------
모델ID + 판매수량 + 모델등급
SELECT T1.ModelID,
SUM(Qty) TTL_Qty,
CASE WHEN SUM(Qty) < 300 THEN 'LowSale'
WHEN SUM(Qty) < 450 THEN 'MidSale'
WHEN SUM(Qty) >= 450 THEN 'HighSale'
END ModelSaleGrade
FROM Sales T1
WHERE T1.SaleYMD LIKE '200603%'
GROUP BY T1.ModelID
--위의 결과를 HighSale, MidSale, LowSale순으로 정렬을 한다.
SELECT T1.ModelID,
SUM(Qty) TTL_Qty,
CASE WHEN SUM(Qty) < 300 THEN 'LowSale'
WHEN SUM(Qty) < 450 THEN 'MidSale'
WHEN SUM(Qty) >= 450 THEN 'HighSale'
END ModelSaleGrade
FROM Sales T1
WHERE T1.SaleYMD LIKE '200603%'
GROUP BY T1.ModelID
ORDER BY
CASE WHEN SUM(Qty) < 300 THEN 0
WHEN SUM(Qty) < 450 THEN 1
WHEN SUM(Qty) >= 450 THEN 2
END DESC
------------------------------------------------------------
--EX)INNER JOIN사용하기 – 판매를 실시한 사원의 이름도 조회
------------------------------------------------------------
SELECT T3.EmployeeName SalesmanName, T2.SalesmanID, T1.SaleYMD, T1.SalesTime, T1.ModelID, T1.Qty
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T2.SalesmanSeq = T1.SalesmanSeq
INNER JOIN Employee T3
ON T3.EmployeeID = T2.SalesmanID
WHERE T1.SaleYMD BETWEEN '20060501' AND '20060530'
ORDER BY T3.EmployeeName
------------------------------------------------------------
--응용EX)INNER JOIN 예제1.모델과 제품명 같이 보여주기 : 모델정보 + 제품명
------------------------------------------------------------
->모델테이블 : Model
->제품테이블 : Product
SELECT T1.ModelID, T1.ModelName, T1.ProductCode, T2.ProductName
FROM Model T1
INNER JOIN Product T2
ON T1.ProductCode = T2.ProductCode
------------------------------------------------------------
--응용EX)INNER JOIN 예제2.제품명별 모델종류 카운트 보여주기 : 제품명 + 해당제품에 속한 모델의 카운트
------------------------------------------------------------
SELECT T1.ProductCode, T1.ProductName, COUNT(*)
FROM Product T1
INNER JOIN Model T2
ON T1.ProductCode = T2.ProductCode
GROUP BY T1.ProductCode, T1.ProductName
------------------------------------------------------------
--응용EX)INNER JOIN 예제3.모델별 인센티브와 제품명 보여주기 : 제품명+모델+인센티브시작일+인센티브종료일+인센티브 금액
------------------------------------------------------------
SELECT T3.ProductName, T1.ModelID, T2.StartYMD, T2.EndYMD, T2.IncentiveAmt
FROM Model T1
INNER JOIN ModelIncentive T2
ON T1.ModelID = T2.ModelID
INNER JOIN Product T3
ON T3.ProductCode = T1.ProductCode
------------------------------------------------------------
--응용EX)INNER JOIN 예제4.2006년 5월 판매에 대해 판매원ID, 판매원명과 판매일자, 수량정보 보여주기
------------------------------------------------------------
판매원ID, 판매원명, 판매일자, 수량
SELECT T2.SalesmanID, T1.EmployeeName SalesmanName, T3.SaleYMD, T3.Qty
FROM Employee T1
INNER JOIN ShopSalesman T2
ON T1.EmployeeID = T2.SalesmanID
INNER JOIN Sales T3
ON T3.SalesmanSeq = T2.SalesmanSeq
AND T3.SaleYMD LIKE '200605%'
WHERE T1.EmployeeType = '0002' --Salesman
--동일한 결과를 얻는 조인
SELECT T2.SalesmanID, T3.EmployeeName SalesmanName, T1.SaleYMD, T1.Qty
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T2.SalesmanSeq = T1.SalesmanSeq
INNER JOIN Employee T3
ON T3.EmployeeID = T2.SalesmanID
AND T3.EmployeeType = '0002'
WHERE T1.SaleYMD LIKE '200605%'
------------------------------------------------------------
--응용EX)INNER JOIN 예제5.2006년 5월 판매에 대해 판매원 ID별로 판매수량 집계하기
------------------------------------------------------------
판매원ID, 판매원명, 판매수량
SELECT T2.SalesmanID, T1.EmployeeName SalesmanName, SUM(T3.Qty) TTL_Qty
FROM Employee T1
INNER JOIN ShopSalesman T2
ON T1.EmployeeID = T2.SalesmanID
INNER JOIN Sales T3
ON T3.SalesmanSeq = T2.SalesmanSeq
AND T3.SaleYMD LIKE '200605%'
WHERE T1.EmployeeType = '0002' --Salesman
GROUP BY T2.SalesmanID, T1.EmployeeName
ORDER BY SUM(T3.Qty) DESC --GROUP BY 를 사용했을 경우에 ORDER BY에 집계함수가 올 수 있다.
------------------------------------------------------------
--응용EX)INNER JOIN 예제6.2006년 5월 판매에 대해 상장ID, 상장명별로 판매수량, 판매 금액 집계하기
------------------------------------------------------------
SELECT T1.ShopID, MIN(T1.ShopName) ShopName,
SUM(T3.Qty) TTL_Qty, SUM(T3.Amount) TTL_Amount
FROM SalesShop T1
INNER JOIN ShopSalesman T2
ON T1.ShopID = T2.ShopID
INNER JOIN Sales T3
ON T3.SalesmanSeq = T2.SalesmanSeq
AND T3.SaleYMD LIKE '200605%'
GROUP BY T1.ShopID
ORDER BY SUM(T3.Amount) DESC
------------------------------------------------------------
--응용EX)INNER JOIN 예제7.2006년 5월 판매 정보와 판매가 발생된 상점의 상점명, 판매한 판매원명, 상점의 상점매니점명 보여주기
------------------------------------------------------------
: 판매일+모델ID+수량+금액+상점명+판매원명+상점관리자명
SELECT T1.SaleYMD, T1.ModelID, T1.Qty, T1.Amount,
T4.ShopName,
T3.EmployeeName SalesmanName,
T6.EmployeeName ManagerName
FROM Sales T1
INNER JOIN ShopSalesMan T2
ON T1.SalesmanSeq = T2.SalesmanSeq
INNER JOIN Employee T3
ON T3.EmployeeID = T2.SalesmanID
INNER JOIN SalesShop T4
ON T4.ShopID = T2.ShopID
INNER JOIN ShopManager T5
ON T5.ShopID = T4.ShopID
INNER JOIN Employee T6
ON T6.EmployeeID = T5.ManagerID
WHERE T1.SaleYMD LIKE '200605%'
------------------------------------------------------------
--EX)LEFT OUTER JOIN사용하기
------------------------------------------------------------
--2006년 3월 1일 데이터를 조회 -- 171건
SELECT *
FROM Sales T1
WHERE T1.SaleYMD = '20060301'
ORDER BY T1.ModelID
--2006년 3월 1일 데이터에 대해 판매가 되지 않은 모델도 모두 보고 싶은 경우 --177건
SELECT *
FROM Model T1
LEFT OUTER JOIN Sales T2
ON T1.ModelID = T2.ModelID
AND T2.SaleYMD = '20060301'
ORDER BY T1.ModelID
------------------------------------------------------------
--LEFT OUTER JOIN 이해하기
------------------------------------------------------------
SELECT *
FROM Model T1
LEFT OUTER JOIN Sales T2
ON T1.ModelID = T2.ModelID
------------------------------------------------------------
--RIGHT OUTER JOIN 이해하기
------------------------------------------------------------
SELECT *
FROM Model T1
RIGHT OUTER JOIN Sales T2
ON T1.ModelID = T2.ModelID
------------------------------------------------------------
--FULL OUTER JOIN 이해하기
------------------------------------------------------------
SELECT *
FROM Model T1
FULL OUTER JOIN Sales T2
ON T1.ModelID = T2.ModelID
------------------------------------------------------------
--응용EX)OUTER JOIN 예제1.2006년 5월 1일에 판매된 모든 판매를 조회, 판매되지 않은 모델도 판매에 대한 정보를 NULL로 조회
------------------------------------------------------------
SELECT *
FROM Model T1
LEFT OUTER JOIN Sales T2
ON T1.ModelID = T2.ModelID
AND T2.SaleYMD = '20060501'
ORDER BY T1.ModelID
------------------------------------------------------------
--응용EX)OUTER JOIN 예제2.2006년 5월 1일에 판매된 정보를 모델별로 판매수량을 집계한다. 판매되지 않은 모델의 수량은 0으로 조회
------------------------------------------------------------
SELECT T1.ModelID,
ISNULL(SUM(T2.Qty),0) SaleQty
FROM Model T1
LEFT OUTER JOIN Sales T2
ON T1.ModelID = T2.ModelID
AND T2.SaleYMD = '20060501'
GROUP BY T1.ModelID
ORDER BY T1.ModelID
------------------------------------------------------------
--응용EX)OUTER JOIN 예제3.2006년 5월 1일에 판매된 정보를 제품별로 판매수량을 집계한다. 판매되지 않은 제품의 수량은 0으로 조회
------------------------------------------------------------
--제품과 모델은 마스터 테이블이므로 조인하면 항상 모든 데이터가 나온다.
--필요없이 LEFT OUTER JOIN을 할 필요는 없다.
SELECT T1.ProductCode, ISNULL(SUM(T3.Qty),0)
FROM Product T1
INNER JOIN Model T2
ON T2.ProductCode = T1.ProductCode
LEFT OUTER JOIN Sales T3
ON T3.ModelID = T2.ModelID
AND T3.SaleYMD = '20060501'
GROUP BY T1.ProductCode
------------------------------------------------------------
--응용EX)OUTER JOIN 예제4. 2006년 5월 1일 판매중에 판매가 없는 상점의 판매원 ID찾아내기
------------------------------------------------------------
SELECT T1.EmployeeID, T1.EmployeeName
FROM Employee T1
INNER JOIN ShopSalesman T2
ON T1.EmployeeID = T2.SalesmanID
--조회하는 판매일자에 활성화되어 있던 상점의 Salesman만 대상으로 한다.
AND T2.SalesStartYMD <= '20060501' AND T2.SalesEndYMD >= '20060501'
LEFT OUTER JOIN Sales T3
ON T3.SalesmanSeq = T2.SalesmanSeq
AND T3.SaleYMD = '20060501'
WHERE T3.SalesmanSeq IS NULL
--이 문장은 NOT EXISTS로도 변경이 가능하다.
------------------------------------------------------------
--EX)파생된 테이블 사용하기
------------------------------------------------------------
--1:N관계를 1:1로 만들어 조인하기
--2006년 6월 판매에 대해 각 상점별로 판매수량, 판매금액을 구하기
SELECT T1.ShopID, T1.ShopName, SUM(T3.Qty) Qty, SUM(T3.Amt) Amt
FROM SalesShop T1
INNER JOIN ShopSalesman T2
ON T1.ShopID = T2.ShopID
INNER JOIN (
--Sales를 ShopsalesmanSeq별로 GROUP BY를 수행한 집합을 만들어 낸다.
SELECT A.SalesmanSeq, SUM(Qty) Qty, SUM(Amount) Amt
FROM Sales A
WHERE A.SaleYMD LIKE '200606%'
GROUP BY A.SalesmanSeq
) T3
ON T3.SalesmanSeq = T2.SalesmanSeq
GROUP BY T1.ShopID, T1.ShopName
--파생된 테이블을 사용하면 단계적으로 SQL을 분석하기 쉬워진다.
--조인횟수가 줄어들게 되는 경우가 있다.
------------------------------------------------------------
--EX)WHERE절의 조건은 파생된 테이블 내에서부터 주도록 한다.
------------------------------------------------------------
SELECT *
FROM Model T1
INNER JOIN Product T2
ON T1.ProductCode = T2.ProductCode
INNER JOIN (
SELECT T1.SaleYMD, T1.ModelID, SUM(T1.Qty) Qty
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY T1.SaleYMD, T1.ModelID
) T3
ON T3.ModelID = T1.ModelID
--조회조건을 안에 주니, Sales테이블에서 클러스터드 인덱스를 스캔해 4381건을 얻어낸후 GROUP BY를 수행했다.
Rows EXEC StmtTxt
348 1 SELECT * FROM Model T1 INNER JOIN Product T2 ON T1.ProductCode = T2.ProductCode INNER JOIN ( SELECT T1.SaleYMD, T1.ModelID, SUM(T1.Qty) Qty FROM Sales T1 WHERE T1.SaleYMD LIKE '200606%' GROUP BY T1.SaleYMD, T1.ModelID ) T3 ON T3.ModelID = T1.ModelID 4 1 0 NULL NULL NULL NULL 180.87758 NULL NULL NULL 2.5557406 NULL NULL SELECT 0 NULL
348 1 |--Hash Match(Inner Join, HASH:([T1].[ModelID])=([T1].[ModelID]), RESIDUAL:([T1].[ModelID]=[T1].[ModelID])) 4 3 1 Hash Match Inner Join HASH:([T1].[ModelID])=([T1].[ModelID]), RESIDUAL:([T1].[ModelID]=[T1].[ModelID]) NULL 180.87758 0.0 2.2488363E-2 176 2.5557225 [T1].[SaleYMD], [T1].[ModelID], [Expr1001], [T1].[ModelID], [T1].[ModelName], [T1].[ProductCode], [T2].[ProductCode], [T2].[ProductName] NULL PLAN_ROW 0 1.0
19 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([T1].[ProductCode])) 4 4 3 Nested Loops Inner Join OUTER REFERENCES:([T1].[ProductCode]) NULL 5.7000003 0.0 7.9420002E-5 207 4.5676775E-2 [T1].[ModelID], [T1].[ModelName], [T1].[ProductCode], [T2].[ProductCode], [T2].[ProductName] NULL PLAN_ROW 0 1.0
19 1 | |--Clustered Index Scan(OBJECT:([ILLHWAN_SQL].[dbo].[Model].[PK_Model] AS [T1])) 4 5 4 Clustered Index Scan Clustered Index Scan OBJECT:([ILLHWAN_SQL].[dbo].[Model].[PK_Model] AS [T1]) [T1].[ModelID], [T1].[ModelName], [T1].[ProductCode] 19.0 3.7578501E-2 0.0000994 128 3.7677899E-2 [T1].[ModelID], [T1].[ModelName], [T1].[ProductCode] NULL PLAN_ROW 0 1.0
19 19 | |--Clustered Index Seek(OBJECT:([ILLHWAN_SQL].[dbo].[Product].[PK_Product] AS [T2]), SEEK:([T2].[ProductCode]=[T1].[ProductCode]) ORDERED FORWARD) 4 6 4 Clustered Index Seek Clustered Index Seek OBJECT:([ILLHWAN_SQL].[dbo].[Product].[PK_Product] AS [T2]), SEEK:([T2].[ProductCode]=[T1].[ProductCode]) ORDERED FORWARD [T2].[ProductCode], [T2].[ProductName] 1.0 6.3284999E-3 7.9603E-5 88 7.9194568E-3 [T2].[ProductCode], [T2].[ProductName] NULL PLAN_ROW 0 19.0
348 1 |--Hash Match(Aggregate, HASH:([T1].[SaleYMD], [T1].[ModelID]), RESIDUAL:([T1].[SaleYMD]=[T1].[SaleYMD] AND [T1].[ModelID]=[T1].[ModelID]) DEFINE:([Expr1001]=SUM([T1].[Qty]))) 4 7 3 Hash Match Aggregate HASH:([T1].[SaleYMD], [T1].[ModelID]), RESIDUAL:([T1].[SaleYMD]=[T1].[SaleYMD] AND [T1].[ModelID]=[T1].[ModelID]) [Expr1001]=SUM([T1].[Qty]) 602.92523 0.0 0.08101359 43 2.4875546 [T1].[SaleYMD], [T1].[ModelID], [Expr1001] NULL PLAN_ROW 0 1.0
4381 1 |--Clustered Index Scan(OBJECT:([ILLHWAN_SQL].[dbo].[Sales].[PK_Sales] AS [T1]), WHERE:(like([T1].[SaleYMD], '200606%', NULL))) 4 8 7 Clustered Index Scan Clustered Index Scan OBJECT:([ILLHWAN_SQL].[dbo].[Sales].[PK_Sales] AS [T1]), WHERE:(like([T1].[SaleYMD], '200606%', NULL)) [T1].[Qty], [T1].[SaleYMD], [T1].[ModelID] 5108.5137 2.2168376 0.1054255 96 2.3222632 [T1].[Qty], [T1].[SaleYMD], [T1].[ModelID] NULL PLAN_ROW 0 1.0
SELECT *
FROM Model T1
INNER JOIN Product T2
ON T1.ProductCode = T2.ProductCode
INNER JOIN (
SELECT T1.SaleYMD, T1.ModelID, SUM(T1.Qty) Qty
FROM Sales T1
GROUP BY T1.SaleYMD, T1.ModelID
) T3
ON T3.ModelID = T1.ModelID
WHERE T3.SaleYMD LIKE '200606%'
--조회조건을 밖에 주니, Sales테이블에서 클러스터드 인덱스를 스캔해 95770건을 얻어낸후 GROUP BY를 수행하고, 그 중에서 SaleYMD가 200606인 데이터를 얻어냈다.
Rows EXEC StmtTxt
348 1 SELECT * FROM Model T1 INNER JOIN Product T2 ON T1.ProductCode = T2.ProductCode INNER JOIN ( SELECT T1.SaleYMD, T1.ModelID, SUM(T1.Qty) Qty FROM Sales T1 GROUP BY T1.SaleYMD, T1.ModelID ) T3 ON T3.ModelID = T1.ModelID WHERE T3.SaleYMD LIKE '200606%' 5 1 0 NULL NULL NULL NULL 180.87758 NULL NULL NULL 3.6041574 NULL NULL SELECT 0 NULL
348 1 |--Hash Match(Inner Join, HASH:([T1].[ModelID])=([T1].[ModelID]), RESIDUAL:([T1].[ModelID]=[T1].[ModelID])) 5 3 1 Hash Match Inner Join HASH:([T1].[ModelID])=([T1].[ModelID]), RESIDUAL:([T1].[ModelID]=[T1].[ModelID]) NULL 180.87758 0.0 2.2488363E-2 176 3.6041393 [T1].[SaleYMD], [T1].[ModelID], [Expr1001], [T1].[ModelID], [T1].[ModelName], [T1].[ProductCode], [T2].[ProductCode], [T2].[ProductName] NULL PLAN_ROW 0 1.0
19 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([T1].[ProductCode])) 5 4 3 Nested Loops Inner Join OUTER REFERENCES:([T1].[ProductCode]) NULL 5.7000003 0.0 7.9420002E-5 207 4.5676775E-2 [T1].[ModelID], [T1].[ModelName], [T1].[ProductCode], [T2].[ProductCode], [T2].[ProductName] NULL PLAN_ROW 0 1.0
19 1 | |--Clustered Index Scan(OBJECT:([ILLHWAN_SQL].[dbo].[Model].[PK_Model] AS [T1])) 5 5 4 Clustered Index Scan Clustered Index Scan OBJECT:([ILLHWAN_SQL].[dbo].[Model].[PK_Model] AS [T1]) [T1].[ModelID], [T1].[ModelName], [T1].[ProductCode] 19.0 3.7578501E-2 0.0000994 128 3.7677899E-2 [T1].[ModelID], [T1].[ModelName], [T1].[ProductCode] NULL PLAN_ROW 0 1.0
19 19 | |--Clustered Index Seek(OBJECT:([ILLHWAN_SQL].[dbo].[Product].[PK_Product] AS [T2]), SEEK:([T2].[ProductCode]=[T1].[ProductCode]) ORDERED FORWARD) 5 6 4 Clustered Index Seek Clustered Index Seek OBJECT:([ILLHWAN_SQL].[dbo].[Product].[PK_Product] AS [T2]), SEEK:([T2].[ProductCode]=[T1].[ProductCode]) ORDERED FORWARD [T2].[ProductCode], [T2].[ProductName] 1.0 6.3284999E-3 7.9603E-5 88 7.9194568E-3 [T2].[ProductCode], [T2].[ProductName] NULL PLAN_ROW 0 19.0
348 1 |--Filter(WHERE:(like([T1].[SaleYMD], '200606%', NULL))) 5 7 3 Filter Filter WHERE:(like([T1].[SaleYMD], '200606%', NULL)) NULL 602.92523 0.0 9.9467468E-3 43 3.5359712 [T1].[SaleYMD], [T1].[ModelID], [Expr1001] NULL PLAN_ROW 0 1.0
8491 1 |--Hash Match(Aggregate, HASH:([T1].[SaleYMD], [T1].[ModelID]), RESIDUAL:([T1].[SaleYMD]=[T1].[SaleYMD] AND [T1].[ModelID]=[T1].[ModelID]) DEFINE:([Expr1001]=SUM([T1].[Qty]))) 5 8 7 Hash Match Aggregate HASH:([T1].[SaleYMD], [T1].[ModelID]), RESIDUAL:([T1].[SaleYMD]=[T1].[SaleYMD] AND [T1].[ModelID]=[T1].[ModelID]) [Expr1001]=SUM([T1].[Qty]) 11303.121 0.0 1.2037612 43 3.5260243 [T1].[SaleYMD], [T1].[ModelID], [Expr1001] NULL PLAN_ROW 0 1.0
95770 1 |--Clustered Index Scan(OBJECT:([ILLHWAN_SQL].[dbo].[Sales].[PK_Sales] AS [T1])) 5 9 8 Clustered Index Scan Clustered Index Scan OBJECT:([ILLHWAN_SQL].[dbo].[Sales].[PK_Sales] AS [T1]) [T1].[Qty], [T1].[SaleYMD], [T1].[ModelID] 95770.0 2.2168376 0.1054255 96 2.3222632 [T1].[Qty], [T1].[SaleYMD], [T1].[ModelID] NULL PLAN_ROW 0 1.0
------------------------------------------------------------
--EX)SELECT절의 서브쿼리
------------------------------------------------------------
--2006-05-01의 판매를 조회, 각 판매의 상점명을 서브쿼리를 사용해서 가져오자.
--상관서브쿼리(외부의 값과 서브쿼리의 결과가 연관된다.)
SELECT T1.SaleYMD, T1.ModelID, T1.SalesTime, T1.Qty, T1.UnitPrice, T1.Amount,
( SELECT A.ShopName
FROM SalesShop A
INNER JOIN ShopSalesman B
ON A.ShopID = B.ShopID
WHERE B.SalesmanSeq = T1.SalesmanSeq) ShopName
FROM Sales T1
WHERE T1.SaleYMD = '20060501'
--2006년 5월1일의 판매모델별로 금액을 구하고, 모델별 판매금액 옆에 총 판매금액을 구한다.
--외부결과와 상관되지 않은 서브쿼리
SELECT T1.ModelID, SUM(T1.Amount) AmountByModel,
(SELECT SUM(A.Amount) FROM Sales A WHERE A.SaleYMD = '20060501')
FROM Sales T1
WHERE T1.SaleYMD = '20060501'
GROUP BY T1.ModelID
------------------------------------------------------------
--EX)WHERE절의 서브쿼리
------------------------------------------------------------
--2006년 6월에 판매 수량이 100개 이상인 판매원들만 조회하기
SELECT T1.EmployeeID, T1.EmployeeName
FROM Employee T1
INNER JOIN ShopSalesman T2
ON T1.EmployeeID = T2.SalesmanID
WHERE T2.SalesmanSeq IN (
SELECT T1.SalesmanSeq
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY T1.SalesmanSeq
HAVING SUM(T1.Qty) >= 100
)
--위의 문장을 파생된 테이블을 이용해서 해결할 수도 있다.
--파생된 테이블과 조인을 사용하면 판매 수량까지 얻어낼 수 있다. -> 일거양득
SELECT T1.EmployeeID, T1.EmployeeName, T3.Qty
FROM Employee T1
INNER JOIN ShopSalesman T2
ON T1.EmployeeID = T2.SalesmanID
INNER JOIN (
SELECT T1.SalesmanSeq, SUM(T1.Qty) Qty
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY T1.SalesmanSeq
HAVING SUM(T1.Qty) >= 100
) T3
ON T2.SalesmanSeq = T3.SalesmanSeq
--심하게 잘못 만든 SQL
SELECT T1.EmployeeID, T1.EmployeeName,
( SELECT SUM(A.Qty)
FROM Sales A
WHERE A.SaleYMD LIKE '200606%'
AND A.SalesmanSeq = T2.SalesmanSeq
) Qty
FROM Employee T1
INNER JOIN ShopSalesman T2
ON T1.EmployeeID = T2.SalesmanID
WHERE T2.SalesmanSeq IN (
SELECT T1.SalesmanSeq
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY T1.SalesmanSeq
HAVING SUM(T1.Qty) >= 100
)
------------------------------------------------------------
--응용EX)2006년5월의 판매에 대해서 모델별로 판매금액이 총판매금액에 대해 얼만큼 비중이 있는지 비율구하기
------------------------------------------------------------
모델+판매금액+판매비율(모델의판매금액/총판매금액*100)
SELECT T1.ModelID,
T1.AmtBYModel,
CONVERT(numeric(18,2), (T1.AmtBYModel / TTL_Amt) * 100.00) AmtRate
FROM (
SELECT T1.ModelID, SUM(T1.Amount) AmtBYModel,
(SELECT SUM(A.Amount) FROM Sales A WHERE A.SaleYMD LIKE '200605%') TTL_Amt
FROM Sales T1
WHERE T1.SaleYMD LIKE '200605%'
GROUP BY T1.ModelID
) T1
ORDER BY T1.ModelID
------------------------------------------------------------
--EX)UNION 사용하기
------------------------------------------------------------
--상점 관리자 집합
SELECT 'Manager' DataTP, T2.ShopName, T1.ManagerID
FROM ShopManager T1
INNER JOIN SalesShop T2
ON T1.ShopID = T2.ShopID
UNION
--상점 판매원 집합
SELECT 'Salesman', T2.ShopName, T1.SalesmanID
FROM ShopSalesman T1
INNER JOIN SalesShop T2
ON T1.ShopID = T2.ShopID
--위 집합은 절대 중복될 수 없다. 그러므로 UNION보다는 UNION ALL을 사용한다.
SELECT 'Manager' DataTP, T2.ShopName, T1.ManagerID
FROM ShopManager T1
INNER JOIN SalesShop T2
ON T1.ShopID = T2.ShopID
UNION ALL
--상점 판매원 집합
SELECT 'Salesman', T2.ShopName, T1.SalesmanID
FROM ShopSalesman T1
INNER JOIN SalesShop T2
ON T1.ShopID = T2.ShopID
------------------------------------------------------------
--EX)OR를 대신하는 UNION/UNION ALL
------------------------------------------------------------
--분기가 시작되는 월에 Open한 상점들
SELECT CASE WHEN T1.OpenYMD LIKE '200501%' THEN '1/4'
WHEN T1.OpenYMD LIKE '200504%' THEN '2/4'
WHEN T1.OpenYMD LIKE '200507%' THEN '3/4'
WHEN T1.OpenYMD LIKE '200510%' THEN '4/4'
END OpenQuarter,
T1.ShopID, T1.ShopName, T1.OpenYMD
FROM SalesShop T1
WHERE (T1.OpenYMD LIKE '200501%'
OR T1.OpenYMD LIKE '200504%'
OR T1.OpenYMD LIKE '200507%'
OR T1.OpenYMD LIKE '200510%')
--위와 같은 OR를 UNION ALL로 변경한다.(위의 결과도 중복되지 않으므로 UNION을 사용하지 않는다.)
--결과의 정렬 순서를 주목하자, 만약에 1/4분기에 대한 집합을 제일 아래서 처리한다면 1/4분기가 제일 아래로 내려간 결과가 반환될 것이다.
SELECT '1/4' OpenQuarter, T1.ShopID, T1.ShopName, T1.OpenYMD
FROM SalesShop T1
WHERE T1.OpenYMD LIKE '200501%'
UNION ALL
SELECT '2/4' OpenQuarter, T1.ShopID, T1.ShopName, T1.OpenYMD
FROM SalesShop T1
WHERE T1.OpenYMD LIKE '200504%'
UNION ALL
SELECT '3/4' OpenQuarter, T1.ShopID, T1.ShopName, T1.OpenYMD
FROM SalesShop T1
WHERE T1.OpenYMD LIKE '200507%'
UNION ALL
SELECT '4/4' OpenQuarter, T1.ShopID, T1.ShopName, T1.OpenYMD
FROM SalesShop T1
WHERE T1.OpenYMD LIKE '200510%'
------------------------------------------------------------
--EX)IN를 대신하는 UNION/UNION ALL
------------------------------------------------------------
--5월동안에 3개 모델을 판매한 판매원의 ID
SELECT DISTINCT T2.SalesmanID
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T2.SalesmanSeq = T1.SalesmanSeq
WHERE T1.SaleYMD LIKE '200605%'
AND T1.ModelID IN ('PDA_M2', 'PDA_M3', 'PDA_M7')
--중복을 제거하는 DISTINCT가 있다. 그러므로 UNION을 사용한다.
SELECT T2.SalesmanID
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T2.SalesmanSeq = T1.SalesmanSeq
WHERE T1.SaleYMD LIKE '200605%'
AND T1.ModelID = 'PDA_M2'
UNION
SELECT T2.SalesmanID
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T2.SalesmanSeq = T1.SalesmanSeq
WHERE T1.SaleYMD LIKE '200605%'
AND T1.ModelID = 'PDA_M3'
UNION
SELECT T2.SalesmanID
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T2.SalesmanSeq = T1.SalesmanSeq
WHERE T1.SaleYMD LIKE '200605%'
AND T1.ModelID = 'PDA_M7'
------------------------------------------------------------
--EX)총계 같이 보여주기
------------------------------------------------------------
--2006년 6월 1일 판매에 대해 판매원이름 모델ID, 판매수량, 금액 리스트를 보여주고,
--제일 마지막에 2006년 6월 1일의 총 판매수량과 판매금액을 같이 보여주시오.
SELECT T3.EmployeeName, T1.ModelID, T1.Qty, T1.Amount
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T1.SalesmanSeq = T2.SalesmanSeq
INNER JOIN Employee T3
ON T3.EmployeeID = T2.SalesmanID
WHERE SaleYMD = '20060601'
UNION ALL
SELECT 'TTL', 'TTL', SUM(T1.Qty), SUM(T1.Amount)
FROM Sales T1
WHERE SaleYMD = '20060601'
------------------------------------------------------------
--EX)계층별 중간계도 포함하기
------------------------------------------------------------
--1.2006년6월 판매에 대해 상장이름, 모델ID 별로 판매수량, 판매금액을 집계
--2.1번 결과에 추가적으로 2006년 6월 판매에 대해 각 상장별로 판매수량, 판매금액을 집계해서 결과에 추가(모델별 집계를 제외한다.)
--3.1번과 2번의 결과에 2006년 6월 판매에 대해 총 판매수량, 판매금액을 집계해서 결과에 추가
SELECT T3.ShopID, T3.ShopName, T1.ModelID,
SUM(T1.Qty) ShopSaleQty, SUM(T1.Amount) ShopSaleAmount
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T1.SalesmanSeq = T2.SalesmanSeq
INNER JOIN SalesShop T3
ON T3.ShopID = T2.ShopID
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY T3.ShopID, T3.ShopName, T1.ModelID
UNION ALL
SELECT T3.ShopID, T3.ShopName, 'TTL',
SUM(T1.Qty) ShopSaleQty, SUM(T1.Amount) ShopSaleAmount
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T1.SalesmanSeq = T2.SalesmanSeq
INNER JOIN SalesShop T3
ON T3.ShopID = T2.ShopID
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY T3.ShopID, T3.ShopName
UNION ALL
SELECT NULL, 'TTL', 'TTL',
SUM(T1.Qty) TTL_Qty, SUM(T1.Amount) TTL_Amount
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
--위의 결과에 정렬 순서를 적용시키려면 어떻게 해야 할까?
--먼저 상장별로 데이터가 나오도록 한다. 상장별로 나오면서 상장, 모델별 집계된 데이터가 나오고, 상장별 집계된 데이터가 나온다.
--제일 마지막에 총 집계된 데이터가 나오도록 한다.
--원하는 정렬 결과를 얻기 위해 인라인뷰(파생된 테이블)을 사용한다.
SELECT T1.ShopID, T1.ShopName, T1.ModelID,
T1.ShopSaleQty, T1.ShopSaleAmount
FROM (
SELECT T3.ShopID, T3.ShopName, T1.ModelID,
SUM(T1.Qty) ShopSaleQty, SUM(T1.Amount) ShopSaleAmount
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T1.SalesmanSeq = T2.SalesmanSeq
INNER JOIN SalesShop T3
ON T3.ShopID = T2.ShopID
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY T3.ShopID, T3.ShopName, T1.ModelID
UNION ALL
SELECT T3.ShopID, T3.ShopName, 'TTL',
SUM(T1.Qty) ShopSaleQty, SUM(T1.Amount) ShopSaleAmount
FROM Sales T1
INNER JOIN ShopSalesman T2
ON T1.SalesmanSeq = T2.SalesmanSeq
INNER JOIN SalesShop T3
ON T3.ShopID = T2.ShopID
WHERE T1.SaleYMD LIKE '200606%'
GROUP BY T3.ShopID, T3.ShopName
UNION ALL
SELECT NULL, 'TTL', 'TTL',
SUM(T1.Qty) TTL_Qty, SUM(T1.Amount) TTL_Amount
FROM Sales T1
WHERE T1.SaleYMD LIKE '200606%'
) T1
ORDER BY CASE WHEN T1.ShopName <> 'TTL' THEN 1 --상장명이 TTL이 아닌 경우 먼저 나오도록 한다.(총계가 가장 늦게 나오게 하기 위해
ELSE 2 END,
T1.ShopName,
CASE WHEN T1.ModelID <> 'TTL' THEN 1 --모델ID가 TTL이 아닌 경우 먼저 나오도록 한다.(상장별 중간계가 늦게 나오게 하기 위해)
ELSE 2 END
------------------------------------------------------------
--응용EX)UNION 예제1. 2006년4월 판매에 대해서 제품,모델별 판매수량을 집계하고, 제품별 중간계를 추가한다.
------------------------------------------------------------
SELECT T2.ProductCode, T3.ProductName,
T1.ModelID, SUM(T1.Qty) ModelSaleQty
FROM Sales T1
INNER JOIN Model T2
ON T2.ModelID = T1.ModelID
INNER JOIN Product T3
ON T3.ProductCode = T2.ProductCode
WHERE T1.SaleYMD LIKE '200604%'
GROUP BY T2.ProductCode, T3.ProductName, T1.ModelID
UNION ALL
SELECT T2.ProductCode, T3.ProductName,
'TTL', SUM(T1.Qty) ProductSaleQty
FROM Sales T1
INNER JOIN Model T2
ON T2.ModelID = T1.ModelID
INNER JOIN Product T3
ON T3.ProductCode = T2.ProductCode
WHERE T1.SaleYMD LIKE '200604%'
GROUP BY T2.ProductCode, T3.ProductName
------------------------------------------------------------
--응용EX)UNION 예제2. 예제1의 결과에 대해 제품별, 모델별 정렬을 수행하는데,
--제품별중간계가 각 제품의 모데별 집계 결과보다 먼저 나오도록 구성
------------------------------------------------------------
SELECT T1.ProductCode, T1.ProductName,
T1.ModelID, T1.ModelSaleQty
FROM (
SELECT T2.ProductCode, T3.ProductName,
T1.ModelID, SUM(T1.Qty) ModelSaleQty
FROM Sales T1
INNER JOIN Model T2
ON T2.ModelID = T1.ModelID
INNER JOIN Product T3
ON T3.ProductCode = T2.ProductCode
WHERE T1.SaleYMD LIKE '200604%'
GROUP BY T2.ProductCode, T3.ProductName, T1.ModelID
UNION ALL
SELECT T2.ProductCode, T3.ProductName,
'TTL', SUM(T1.Qty) ProductSaleQty
FROM Sales T1
INNER JOIN Model T2
ON T2.ModelID = T1.ModelID
INNER JOIN Product T3
ON T3.ProductCode = T2.ProductCode
WHERE T1.SaleYMD LIKE '200604%'
GROUP BY T2.ProductCode, T3.ProductName
) T1
ORDER BY T1.ProductCode,
CASE WHEN T1.ModelID = 'TTL' THEN 1 ELSE 2 END
------------------------------------------------------------
--EX)CROSS JOIN 사용하기
------------------------------------------------------------
--지역별로 가능한 모든 판매장 형태 데이터 만들기
SELECT T2.RegionCode, T2.RegionName, T1.Code ShopType, T1.CodeName ShopTypeName
FROM MasterCode T1
CROSS JOIN Region T2
WHERE T1.CodeType = 'ShopType'
ORDER BY T2.RegionCode, T1.Code
--SalesShop에 등록된 강남지역의 매장이 백화점매장만 있다면, 대리점, 특판매장등의 판매등은 발생되지 않는다.
--하지만 발생되지 않은 경우도 데이터 결과에 포함해야 한다면 이와 같이 CROSS JOIN을 사용해서 가능한 모든 결과를 얻어낼 필요가 있다.
------------------------------------------------------------
--EX)CROSS JOIN을 사용해서 판매 데이터를 3배로 불리기
------------------------------------------------------------
--UNION ALL을 이용해 3건의 데이터를 만들고 이를 인라인 뷰에 집어넣어서 Sales테이블과 CROSS JOIN을 수행한다.
SELECT *
FROM Sales T1
CROSS JOIN
( SELECT 1 DATA_NO
UNION ALL
SELECT 2 DATA_NO
UNION ALL
SELECT 3 DATA_NO
) T2
------------------------------------------------------------
--EX)판매금액과 판매수량을 각각 로우로 분리하기
------------------------------------------------------------
--2006년 6월 1일 판매 데이터에 대해 모델별로 판매 수량, 금액을 집계해서 보여주는데,
--한 로우는 수량만, 한 로우는 금액이 나오도록 처리
--수량을 나타내는 로우일 경우에는 DATA_TP란 컬럼에 QTY로, 판매금액일 경우네는 AMT로 표시해준다.
--즉, 한 모델은 두개의 로우에 나오게 된다.
SELECT T1.ModelID,
T2.DATA_TP,
CASE WHEN T2.DATA_TP = 'QTY' THEN T1.Qty
WHEN T2.DATA_TP = 'AMT' THEN T1.Amount
END Value
FROM (
SELECT T1.ModelID, SUM(T1.Qty) Qty, SUM(T1.Amount) Amount
FROM Sales T1
WHERE T1.SaleYMD = '20060601'
GROUP BY T1.ModelID
) T1 CROSS JOIN
( SELECT 'QTY' DATA_TP
UNION ALL
SELECT 'AMT' DATA_TP
) T2
ORDER BY T1.ModelID, T2.DATA_TP
좋은 선생과 함께라면 더 빨리 배울 수 있다. 그러므로 노련한 라이더에게 그들이 편한 날에 그들과 함께 탈 수 있는지 정하라. 그들이 어떻게 코너를 돌고, 언덕을 오르는지 잘 살펴보자. 충고를 구하는 것을 두려워하지 마라. 대부분의 최고 선수들은 그들의 정보를 나누어주는 것을 좋아한다.그룹으로 주행을 하고, 선두 그룹과 5마일 정도 같이 타도록 노력해라. 다음주에는 10마일을 목표로 하라. 싱글 트랙(Single Track)에서는 노련한 선수를따라가며 그들이 일렬로 내려가는 것을 흉내내라. 그들이 언제 변속하고 브레이크를 잡고, 장애물을 어떻게 피하고 얼마나 험한 지형에서 그들이 자전거를탈 수 있는지 눈여겨 보아라.조언해 줄 수 있는 사람을 찾아라. 그를 따라 노력하면 당신은 빨리 배울 것이다. 그렇지 않으면 넘어질 것이다. 잘 타는 사람들은 당신이 해보려고 생각지도 못했던 동작을 쉽게 할 것이다. 그들을 따라 하면 가능성의 한계를 넓힐수 있다.
2. 수박이 되라
당신은 경치 좋은 곳에서 자전거를 타는 것을 멈추고 음식을 먹고, 그 후 즉시 당신이 얼마나 힘차고 빠르게 자전거를 다시 타게 되는가를 눈 여겨 본적이 있습니까?
음식은 연료다. 그러므로 배를 채워야 한다. 길에서 탈 때는 쌓여 있는 에너지바나 바나나 껍질을 벗기는데 능력을 완벽히 발휘해야 한다. 중간에 쉬며 식사 하는 것은 산행 바이킹의 훌륭한 전통이다. 음료수도 중요하다. 적당한 수분 섭취는 당신이 탈 수 있는 거리를 연장시켜 준다. 그것은 의식적인 노력을 줄여 주고 쉽게 회복시켜 준다.
적어도 물이나 스포츠 음료통 큰 것 없이는 집을 나서지 마라. 음료수를 챙기는 것으로 다른 부가적인 훈련 없이도 지구력을 30 % 향상시킬 수 있다. 수박이 되라. 건포도가 되지 말고.
3. 엔진강화훈련
약간의 무게가 가벼운 자전거를 타고 산을 오르는데 큰 차이를 가져올 수 있다. 컴퓨터 연구를 통해, 1 마일 올라갈 때 자전거와 타는 사람의 무게를 6파운드까지 줄이면 22초 또는 300피트까지 더 잘 타고 더 잘 오르는 효과를 얻을 수 있다고 했다.
자전거를 가볍게 하는 것은 돈이 많이 들고 복잡하지만 몸무게를 약간 줄이는것은 쉽다. 간단히 당신의 음식물에서 지방을 배제하고 칼로리를 줄여라. "Pizza조각에서 치즈의 일부를 떼어 내고 먹어라"
지방 칼로리를 피하는 반면 쌀밥으로부터 더 많은 탄수화물을 얻는 것이 좋다.
자전거 탄 후에 마시는 맥주는 미네랄워터로 바꿔라.
4. 휴식과 게으름
휴식은 훈련만큼이나 중요하다. 힘든 노력 후에 휴식을 할 때 회복과 향상이이루어진다. 당신이 일요일의 힘든 라이딩으로 아직도 피곤하다면 당신은 아직도 회복과 향상이 이루어지고 있는 상태이다.
" 극도로 피로하게 운동을 한 후 근육의 글리코겐을 재 보충하려면 48시간 정도 걸린다. 원기를 회복하기 전에 자신을 다그쳐서 그 과정을 짧게 하지 마라게으름이 때로는 미덕이다".
5. 천천히 타라
대부분의 자전거 타기는 향상을 자극하는 때때로의 심한 노력을 위해 충분히휴식시켜 줄 수 있을 정도인, 최대 심장 박동의 60-80%의 비교적 낮은 상태로해야만 한다. 국가 대표팀이 심장 박동 120 이하에서 훈련 시간의 60-90%를 보낸다고 지적한다.
대부분의 비전문적인 사이클리스트는 항상 똑같이 자전거를 타려는 경향이 있다고 한다.
평상시에는 힘들게, 컨디션이 좋지 않은 날은 너무 쉽게 낮은 속력을 유지하는 것은 지방을 분해시키고 산소량을 증가시키고, 계획된 더 힘든 과정에서의회복 속력을 빠르게 해준다.
대부분의 주행에서 경치를 즐기고 친구들과 이야기를 나눌 수 없다면 속력을 줄여라. 헬멧 끈 위의 마른 땀자국은 지나 치게 하고 있다는 확실한 증거이다.
6. 심장 박동 모니터를 사용하라
90년대의 가장 중요한 훈련 장비가 무엇이냐고 물을 때 전문가들의 견해는 심장 박동 모니터라고 다들 일치한다. 그러나 대부분의 라이더들은 힘든 과정에 서만 그것을 사용한다. 그러나 그것은 쉬운 과정에서도 사용해야 한다.심장 박동 모니터의 심장 경고 음을 당신의 최고 심장 박동의 75-80%정도로 맞춰라 경보 음이 울리면 속력을 낮춰라. 당신의 심장은 스포츠카 엔진이라 생각하고 모니터는 통솔하는 것이라고 생각해라.
7. 오프로드를 타라
싸이클리스트와 산악자전거 타는 사람들이 두 마리의 다른 불독처럼 지내곤 해도, 그것은 더 이상 사실이 아니다. 거의 모든 진지한 싸이클리스트들은 Fat 타이어와 Skinny 타이어 둘 다 가지고 있다. 오프 로드에서의 주행은 자전거조작 능력을 향상시켜 주는 좋은 방법이다. 그것은 언덕 주행시 파워를 증가시켜 주고 내리막 길로에서 휴식을 준다.
산악 지형에서 정규적으로 자전거를 타는 사람은 약간의 스트레스를 받게 되고 긴장감을 갖게 된다. 반대로 길에서 타는 것은 스트레스를 적게 받은 상태에서 지구력을 키워 주며 Aerobic Fitness를 향상시 켜 주고 울퉁불퉁한 지형에 의해 사각형 형태가 되어 버린 페달 스트로크(부드러운 원형의 페달링이 아닌 찍어누르기식 페달링을 말함)를 유연하게 해준다. "대부분의 우수 산악자전거 선수들은 그들 훈련의 60%를 길에서 한다" 경기 때 외에는 절대 비포장에서 타지 않는 선수가 의외로 많다.
8. 다양성
싸이클링은 싸이클링에 의해 향상된다. 그러나 날씨나 당신의 계획이 라이딩을 방해 할 때, 그대로 포기하지 마라. 대신 다른 스포츠를 고려해 보라. 예를 들면, 달리기는 비오는 날이나 어두워질 때 더 쉽게 할 수 있다. 인라인 스케이팅 (흔히 롤러 블레이드라고 부름)은 단조로움을 줄여 준다. "스케이팅과싸이클링은 유사한 자세를 가지고 있다. 계속 자전거만 탄다면 회사 야유회에서 야구를 할 때 1루로 역주하는 등의 단거리 질주는 당신을 1주일 동안 아프게 할 것이다. 1 주일에 두 차례 짧게 달 려라. 그러면 번트를 쳐낸 후에도 절뚝거리지 않을 것이다."
9. 느낌대로 타라
시간을 분류하는 것은 - 조심스럽게 하루, 1주일, 한 달의 훈련을 계획하는것- 가장 중요한 조절 속도이다. 그러나 특별한 계획을 수행하기 위한 계속적인 압박은 하루를 서둘러 지나가게 할 수 있다. 화요일에는 역주하고, 수요일에는 지구력을 기르고 우리들의 대부분은 그런 압박으로부터 빠져나가기 위해 자전거를 타는 것이 지, 그것에 의해 소요되기 위해서가 아니다. 또한 이따금은 훈련 계획을 무시하고 당신의 느낌대로 해라. 길 표지판을 보면서 역주하라 그러다가 피곤하면 천천히 타라.
무식하게 스케줄대로만 억지로 타려하지 말라는 뜻임
10. 언덕과 친숙하라
힘든 훈련을 위해 언덕이 많은 곳을 선택해라, 그러면 실력 향상은 쉽다. 가파른 곳을 오르는 것은 정신적인 노력을 거의 하지 않고도 당신의 심장 박동을 최상의 상태로 올려 준다. 평지에서의 (시간 훈련) 보다 언덕에서 최고의 90%를 얻어 내는 것이 훨씬 쉽다. 우수한 라이더는 이것을 안다.
몇몇 유명선수가 살인적인 언덕의 꼭대기에 살고 있으며, 경기가 없을 때 언덕 마을로 돌아가는 이유가 이것이다. 진지함이 간단히 우리를 강하게 만든다
