조건식 CASE. CASE 조건식 CASE 식은 SQL 언어의 조건문입니다.

두 가지 방법이 사용됩니다.

CASE 표현식

디코드 기능

SQL 문에서 조건부 처리(IF-THEN-ELSE 논리)를 구현하는 데 사용되는 두 가지 방법은 CASE 표현식과 DECODE 함수입니다.

참고: CASE 표현식은 ANSI SQL을 따릅니다. DECODE 함수는 Oracle 구문에 따라 다릅니다.

CASE 표현식

IF-THEN-ELSE 문이 작동하도록 하여 조건부 쿼리를 단순화합니다.

CASE 표현식을 사용하면 프로시저를 호출하지 않고도 SQL 문에서 IF-THEN-ELSE 논리를 사용할 수 있습니다.

간단하게 조건식 CASE Oracle Server는 expr이 comparison_expr과 같고 return_expr을 반환하는 첫 번째 WHEN ... THEN 쌍을 검색합니다. WHEN ... THEN 쌍 중 어느 것도 이 조건을 만족하지 않고 else 절이 존재하면 Oracle은 else_expr을 반환합니다. 그렇지 않으면 Oracle은 null을 반환합니다. 모든 return_expr 및 else_expr에 대해 NULL을 지정할 수는 없습니다.

Expr 및 comparison_expr은 CHAR, VARCHAR2, NCHAR 또는 NVARCHAR2가 될 수 있는 동일한 데이터 유형이어야 합니다. 모든 반환 값(return_expr)은 동일한 데이터 유형이어야 합니다.

이 구문에서 Oracle은 입력 표현식(e)을 각 비교 표현식 e1, e2, ..., en과 비교합니다.

입력 표현식이 비교 표현식과 같으면 CASE 표현식은 해당 결과 표현식(r)을 반환합니다.

입력 식 e가 비교 식과 일치하지 않는 경우 CASE 식은 ELSE 절이 있으면 ELSE 절의 식을 반환하고, 그렇지 않으면 null 값을 반환합니다.

Oracle은 간단한 CASE 표현식에 대해 단락 평가를 사용합니다. 이는 Oracle이 각 비교 표현식(e1, e2, .. en) 중 하나를 입력 표현식(e)과 비교하기 전에만 평가한다는 것을 의미합니다. Oracle은 표현식(e)과 비교하기 전에 모든 비교 표현식을 평가하지 않습니다. 결과적으로 Oracle은 이전 표현식이 입력 표현식(e)과 같을 경우 비교 표현식을 평가하지 않습니다.

간단한 CASE 표현식 예제

데모에서는 제품 테이블을 사용합니다.

다음 쿼리는 CASE 표현식을 사용하여 각 제품 범주(예: CPU 5%, 비디오 카드 10% 및 기타 제품 범주 8%)에 대한 할인을 계산합니다.

선택하다

CASE category_id

언제 1

THEN ROUND (list_price * 0.05,2) - CPU

언제 2

THEN ROUND (List_price * 0.1,2) - 비디오 카드

ELSE ROUND (list_price * 0.08,2) - 기타 카테고리

할인 종료

에서

주문

ROUND() 함수를 사용하여 할인을 소수점 이하 두 자리로 반올림했습니다.

검색된 CASE 표현식

Oracle 검색 CASE 표현식은 부울 표현식 목록을 평가하여 결과를 결정합니다.

검색된 CASE 문에는 다음 구문이 있습니다.

사례

언제 e1THEN r1

, COUNT (DISTINCT DepartmentID) [고유 부서 수], COUNT (DISTINCT PositionID) [고유 직위 수], COUNT (BonusPercent) [% 보너스가 있는 직원 수], MAX (BonusPercent) [최대 보너스 백분율], MIN ( BonusPercent) [최소 상여금 비율], SUM(급여 / 100 * BonusPercent) [모든 상여금의 합계], AVG(급여 / 100 * BonusPercent) [평균 상여금], AVG(급여) [평균 급여] FROM Employees

명확성을 위해 여기에서 예외를 만들고 [...] 구문을 사용하여 열 별칭을 정의했습니다.

각 반환 값이 어떻게 생성되었는지 살펴보고 먼저 SELECT 문의 기본 구문 구성을 상기해 보겠습니다.

첫째, 왜냐하면 쿼리에서 WHERE 조건을 지정하지 않은 경우 쿼리에서 얻은 자세한 데이터에 대해 합계가 계산됩니다.

SELECT * FROM 직원

저것들. 직원 테이블의 모든 행에 대해

명확성을 위해 집계 함수에 사용되는 필드와 표현식만 선택합니다.

SELECT DepartmentID, PositionID, BonusPercent, 급여 / 100 * BonusPercent, 급여 FROM 직원

부서 ID	위치ID	보너스퍼센트	급여 / 100 * 보너스퍼센트	샐러리
1	2	50	2500	5000
3	3	15	225	1500
2	1	없는	없는	2500
3	4	30	600	2000
3	3	없는	없는	1500
없는	없는	없는	없는	2000

이것은 집계된 쿼리의 총계가 계산될 초기 데이터(상세 라인)입니다.

이제 각 집계 값을 살펴보겠습니다.

세다 (*)- 부터 쿼리의 WHERE 절에 필터링 조건을 지정하지 않은 경우 COUNT(*)는 테이블의 총 레코드 수, 즉 이것은 쿼리에서 반환된 행 수입니다.

SELECT * FROM 직원

COUNT(DISTINCT DepartmentID)- 우리에게 값 3을 반환했습니다. 이 숫자는 NULL 값을 제외하고 DepartmentID 열에 지정된 고유 부서 값의 수에 해당합니다. DepartmentID 열의 값을 살펴보고 동일한 값을 한 가지 색상으로 색칠해 보겠습니다(자유롭게 모든 방법이 훈련에 좋습니다).

우리는 NULL을 버리고 3개의 고유 값(1, 2, 3)을 얻습니다. 저것들. 확장된 형식에서 COUNT(DISTINCT DepartmentID)가 수신한 값은 다음 선택 항목으로 나타낼 수 있습니다.

SELECT DISTINCT DepartmentID - 2. DepartmentID가 NULL이 아닌 곳에서 직원의 고유한 값만 취합니다. - 1. NULL 값을 버립니다.

COUNT(DIISTINCT PositionID)- COUNT(DISTINCT DepartmentID)에 대해 말한 것과 동일하지만 PositionID 필드만. PositionID 열의 값을 보고 색상을 후회하지 않습니다.

COUNT(보너스백분율)- BonusPercent 값이 있는 행 수를 반환합니다. BonusPercent가 NULL이 아닌 레코드 수를 계산합니다. 여기가 더 쉬울 것입니다. 고유 값을 계산할 필요가 없으며 NULL 값이 있는 레코드를 버리면 됩니다. BonusPercent 열의 값을 가져 와서 모든 NULL 값을 지웁니다.

3개의 값이 남아 있습니다. 저것들. 확장된 형태로 샘플은 다음과 같이 나타낼 수 있습니다.

SELECT BonusPercent - 2. BonusPercent가 NULL이 아닌 곳에서 직원의 모든 값을 가져옵니다. - 1. NULL 값을 버립니다.

때문에 DISTINCT라는 단어를 사용하지 않았기 때문에 NULL과 동일한 BonusPercent를 제외하고 반복된 BonusPercent가 있는 경우 이를 계산합니다. 예를 들어 DISTINCT가 있는 경우와 없는 경우의 결과를 비교해 보겠습니다. 명확성을 위해 DepartmentID 필드 값을 사용하겠습니다.

SELECT COUNT(*), - 6 COUNT(DISTINCT DepartmentID), - 3 COUNT(DepartmentID) - 5 FROM 직원

MAX(보너스백분율)- 다시 NULL 값을 제외하고 최대 BonusPercent 값을 반환합니다.
BonusPercent 열의 값을 가져 와서 그 중 최대 값을 찾고 NULL 값에주의를 기울이지 않습니다.

SELECT TOP 1 BonusPercent FROM 직원 WHERE BonusPercent IS NOT NULL ORDER BY BonusPercent DESC - 내림차순으로 정렬

MIN(보너스백분율)- 다시 NULL 값을 제외하고 최소 BonusPercent 값을 반환합니다. MAX의 경우와 마찬가지로 NULL을 무시하고 최소값만 찾습니다.

저것들. 다음 값을 얻습니다.

SELECT TOP 1 BonusPercent FROM 직원 WHERE BonusPercent IS NOT NULL ORDER BY BonusPercent - 오름차순으로 정렬

MIN(BonusPercent) 및 MAX(BonusPercent)의 시각적 표현:

SUM (급여 / 100 * BonusPercent)- NULL이 아닌 모든 값의 합계를 반환합니다. 표현식의 값을 구문 분석합니다(Salary / 100 * BonusPercent):

저것들. 다음 값이 추가됩니다.

SELECT 급여 / 100 * BonusPercent FROM 직원 WHERE 급여 / 100 * BonusPercent IS NOT NULL

AVG (급여 / 100 * BonusPercent)- 값의 평균을 반환합니다. NULL 표현식은 무시됩니다. 이것은 두 번째 표현식과 일치합니다.

SELECT AVG (급여 / 100 * BonusPercent), - 1108.333333333333 SUM (급여 / 100 * BonusPercent) / COUNT (급여 / 100 * 6 6 직원 6 F 6 6 COUNT), - 1108.333333333333 SUM00 (급여 /Percent)

저것들. 다시 말하지만, 수량을 계산할 때 NULL 값은 고려되지 않습니다.

554.166666666667을 제공하는 세 번째 표현식에서와 같이 모든 직원의 평균을 계산해야 하는 경우 NULL 값을 0으로 예비 변환합니다.

SELECT AVG(ISNULL(급여 / 100 * BonusPercent, 0)), - 554.166666666667 SUM(급여 / 100 * BonusPercent) / COUNT(*) - 554.1666666666667 FROM 직원

평균(급여)-사실, 모든 것이 이전의 경우와 동일합니다. 급여가 NULL이면 계산되지 않습니다. 모든 직원을 각각 고려하기 위해 AVG 값의 예비 NULL 변환을 수행합니다(ISNULL(Salary, 0))

결과를 요약하자면 다음과 같습니다.

COUNT(*) - "SELECT ... WHERE ..." 연산자가 수신한 총 행 수를 계산하는 역할을 합니다.
위의 다른 모든 집계 함수에서 합계를 계산할 때 NULL 값은 고려되지 않습니다
모든 행을 고려해야 하는 경우 AVG 함수와 더 관련이 있는 경우 먼저 NULL 값을 처리해야 합니다(예: 위에 표시된 "AVG(ISNULL(Salary, 0))").

따라서 WHERE 절에서 집계 함수로 추가 조건을 지정할 때 조건을 만족하는 행에 대한 합계만 계산됩니다. 저것들. 집계 값 계산은 SELECT 구성을 사용하여 얻은 전체 집합에 대해 수행됩니다. 예를 들어 IT 부서의 컨텍스트에서만 동일한 작업을 수행해 보겠습니다.

SELECT COUNT (*) [총 직원 수], COUNT (DISTINCT DepartmentID) [고유 부서 수], COUNT (DISTINCT PositionID) [고유 직위 수], COUNT (BonusPercent) [% 상여금이 있는 직원 수] , MAX (BonusPercent) [최대 보너스 백분율], MIN (BonusPercent) [최소 보너스 백분율], SUM (급여 / 100 * BonusPercent) [모든 보너스의 합계], AVG (급여 / 100 * BonusPercent) [평균 보너스 크기], AVG ( 급여) [평균 급여] FROM 직원 WHERE DepartmentID = 3 - IT 부서만 고려

집계 함수의 작업을 더 잘 이해하려면 얻은 각 값을 독립적으로 분석하는 것이 좋습니다. 요청에 의해 수신된 세부 데이터에 따라 여기에서 각각 계산을 수행합니다.

SELECT DepartmentID, PositionID, BonusPercent, Salary / 100 * BonusPercent, Salary FROM Employees WHERE DepartmentID = 3 - IT 부서만 포함

부서 ID	위치ID	보너스퍼센트	급여 / 100 * 보너스퍼센트	샐러리
3	3	15	225	1500
3	4	30	600	2000
3	3	없는	없는	1500

계속 진행합니다. 집계 함수가 NULL을 반환하는 경우(예: 모든 직원의 급여 값이 지정되지 않은 경우) 또는 단일 레코드가 선택 항목에 포함되지 않고 보고서에 이러한 경우에 대해 0을 표시해야 하는 경우 ISNULL 함수는 집계 표현식을 래핑할 수 있습니다.

SELECT SUM(Salary), AVG(Salary), - ISNULL을 사용하여 합계 처리 ISNULL(SUM(Salary), 0), ISNULL(AVG(Salary), 0) FROM Employees WHERE DepartmentID = 10 - 존재하지 않는 부서가 특별히 쿼리가 레코드를 반환하지 않도록 여기에 표시됨

(열 이름 없음)	(열 이름 없음)	(열 이름 없음)	(열 이름 없음)
없는	없는	0	0

각 집계 함수의 목적과 계산 방법을 이해하는 것이 매우 중요하다고 생각합니다. SQL에서 합계를 계산하는 주요 도구입니다.

이 경우 각 집계 함수가 어떻게 독립적으로 작동하는지 조사했습니다. SELECT 명령으로 얻은 전체 레코드 집합의 값에 적용되었습니다. 다음으로 GROUP BY 절을 사용하여 그룹 합계를 계산하는 데 이러한 동일한 함수가 어떻게 사용되는지 살펴보겠습니다.

GROUP BY - 데이터 그룹화

그 전에 우리는 대략 다음과 같이 특정 부서에 대한 총계를 이미 계산했습니다.

SELECT COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(급여) SalaryAmount FROM Employees WHERE DepartmentID = 3 - IT 부서 전용 데이터

이제 각 부서의 컨텍스트에서 동일한 숫자를 구하라는 요청을 받았다고 상상해 보십시오. 물론 우리는 소매를 걷어붙이고 각 부서에 대해 동일한 요청을 수행할 수 있습니다. 따라서 완료되자마자 4개의 요청을 작성합니다.

SELECT "관리" 정보, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(급여) 급여 FROM 직원 WHERE DepartmentID = 1 - 관리에 대한 데이터 SELECT "회계" 정보, COUNT(DISTINCT PositionID) PositionCount, COUNT( * ) EmplCount, SUM(Salary) SalaryAmount FROM Employees WHERE DepartmentID = 2 - 회계 데이터 SELECT "IT" 정보, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Salary) SalaryAmount FROM Employees WHERE DepartmentID = 3 - 데이터 IT 부서 SELECT "Other" Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Salary) SalaryAmount FROM Employees WHERE DepartmentID IS NULL - 프리랜서에 대한 데이터를 잊지 마세요.

결과적으로 4개의 데이터 세트를 얻습니다.

상수로 지정된 필드("관리", "회계", ...)를 사용할 수 있습니다.

일반적으로 우리는 우리에게 요청한 모든 숫자를 추출하고 모든 것을 Excel로 결합하여 감독에게 제공합니다.

감독은 보고서를 좋아했으며 "평균 급여에 대한 정보가 포함된 다른 열을 추가합니다."라고 말합니다. 그리고 항상 그렇듯이 매우 시급하게 처리해야 합니다.

흠, 어떡하지?! 또한 우리 부서가 3이 아니라 15라고 상상해 봅시다.

이것이 바로 GROUP BY 절이 이러한 경우에 해당하는 것입니다.

SELECT DepartmentID, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Salary) SalaryAmount, AVG(Salary) SalaryAvg - 플러스 우리는 이사의 소원을 이행합니다. FROM Employees GROUP BY DepartmentID

부서 ID	위치 수	EmplCount	급여액	급여평균
없는	0	1	2000	2000
1	1	1	5000	5000
2	1	1	2500	2500
3	2	3	5000	1666.66666666667

우리는 모두 동일한 데이터를 얻었지만 이제 하나의 요청만 사용합니다!

지금은 부서가 숫자 형식으로 표시된다는 사실에주의를 기울이지 말고 모든 것을 아름답게 표시하는 방법을 배웁니다.

GROUP BY 절에서 여러 필드 "GROUP BY field1, field2, ..., fieldN"을 지정할 수 있습니다. 이 경우 그룹화는 "field1, field2, .." 필드의 값을 형성하는 그룹별로 발생합니다. ., 필드N".

예를 들어 부서 및 직책별로 데이터를 그룹화해 보겠습니다.

SELECT DepartmentID, PositionID, COUNT(*) EmplCount, SUM(급여) 급여 FROM 직원 GROUP BY DepartmentID, PositionID

그런 다음 각 조합을 실행하고 집계 함수를 계산합니다.

SELECT COUNT(*) EmplCount, SUM(급여) SalaryAmount FROM 직원 WHERE DepartmentID가 NULL이고 PositionID가 NULL SELECT COUNT(*) EmplCount, SUM(급여) 직원 FROM WHERE DepartmentID = 1 AND PositionID = 2 - ... SELECT COUNT (*) EmplCount, SUM(급여) DepartmentID = 3 AND PositionID = 4인 직원의 급여액

그런 다음 이 모든 결과가 함께 결합되어 하나의 집합으로 제공됩니다.

주요 항목에서 그룹화(GROUP BY)의 경우 SELECT 블록의 열 목록에서 다음을 확인할 수 있습니다.

GROUP BY 절에 나열된 열만 사용할 수 있습니다.
GROUP BY 블록의 필드와 함께 표현식을 사용할 수 있습니다.
상수를 사용할 수 있습니다. 그들은 그룹화 결과에 영향을 미치지 않습니다
다른 모든 필드(GROUP BY 블록에 나열되지 않음)는 집계 함수(COUNT, SUM, MIN, MAX, ...)에만 사용할 수 있습니다.
SELECT 열 목록의 GROUP BY 절에서 모든 열을 나열할 필요는 없습니다.

그리고 말한 모든 것을 보여줍니다.

SELECT "문자열 상수" Const1, - 문자열 1 형태의 상수 Const2, - 숫자 형태의 상수 - 그룹 CONCAT("부서 번호", DepartmentID)에 참여하는 필드를 사용한 표현식 ConstAndGroupField, CONCAT("부서 No.", DepartmentID , ", Position No.", PositionID) ConstAndGroupFields, DepartmentID, - 그룹화에 참여하는 필드 목록의 필드 - PositionID, - 그룹화에 참여하는 필드, 여기에 복제할 필요가 없습니다. COUNT( *) EmplCount, - 각 그룹의 줄 수 - 나머지 필드는 집계 함수에만 사용할 수 있습니다. COUNT, SUM, MIN, MAX,… SUM (Salary) SalaryAmount, MIN (ID) MinID FROM Employees GROUP BY DepartmentID , PositionID - DepartmentID, PositionID 필드별 그룹화

그룹화는 필드뿐만 아니라 표현식으로도 수행할 수 있다는 점도 주목할 가치가 있습니다. 예를 들어 데이터를 직원별로, 생년월일별로 그룹화해 보겠습니다.

SELECT CONCAT("생년 -", YEAR(생일)) YearOfBirthday, COUNT(*) EmplCount FROM 직원 GROUP BY YEAR(생일)

좀 더 복잡한 표현이 있는 예를 살펴보겠습니다. 예를 들어, 출생 연도별로 직원의 등급을 구해 보겠습니다.

SELECT CASE WHEN YEAR(생일)> = 2000 THEN "from 2000" WHEN YEAR(생일)> = 1990 THEN "1999-1990" WHEN YEAR(생일)> = 1980 THEN "1989-1980 YEARth" (B) WHEN 1970 THEN "1979-1970" WHEN Birthday IS NOT NULL THEN "before 1970" ELSE "not specified" END RangeName, COUNT(*) EmplCount FROM Employees GROUP BY CASE WHEN YEAR(생일)> = 2000 THEN "2000년부터" W (생일)> = 1990 THEN "1999-1990" WHEN YEAR (생일)> = 1980 THEN "1989-1980" WHEN YEAR(생일)> = 1970 THEN "1979-1970" WHEN 생일 "be00"이 NULL이 아님 THEN ELSE "지정되지 않음" END

범위 이름	EmplCount
1979-1970	1
1989-1980	2
표시되지 않음	2
1970년 초	1

저것들. 이 경우 그룹화는 각 직원에 대해 이전에 계산된 CASE 표현식에 따라 수행됩니다.

SELECT ID, CASE WHEN YEAR(생일)> = 2000 THEN "from 2000" WHEN YEAR(생일)> = 1990 THEN "1999-1990" WHEN YEAR(생년)> = 1980 THEN "1989-1980 YEARthEN" WHEN > = 1970 THEN "1979-1970" WHEN Birthday IS NOT NULL THEN "before 1970" ELSE "not specified" END FROM Employees

물론 GROUP BY 블록의 필드와 표현식을 결합할 수 있습니다.

SELECT DepartmentID, CONCAT("생년 -", YEAR(생일)) YearOfBirthday, COUNT(*) EmplCount FROM Employees GROUP BY YEAR(생일), DepartmentID - 순서가 SELECT ORDER에서 사용하는 순서와 일치하지 않을 수 있습니다. BY DepartmentID 블록, YearOfBirthday - 마지막으로 결과에 정렬을 적용할 수 있습니다.

원래 작업으로 돌아가 보겠습니다. 우리가 이미 알고 있듯이 이사는 보고서를 매우 좋아했고 회사의 변화를 모니터링 할 수 있도록 매주 보고서를 요청했습니다. Excel에서 이름으로 부서의 숫자 값을 매번 중단하지 않도록 이미 알고 있는 지식을 사용하고 쿼리를 개선합니다.

SELECT CASE DepartmentID WHEN 1 THEN "Administration" WHEN 2 THEN "Accounting" WHEN 3 THEN "IT" ELSE "Other" END 정보, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(급여) SalaryAmount, AVG(급여) ) SalaryAvg - 플러스 우리는 이사의 희망을 충족합니다. FROM Employees GROUP BY DepartmentID ORDER BY Info - 더 많은 편의를 위해 정보 열을 기준으로 정렬을 추가합니다.

겉으로 보기에는 무섭게 보일지 모르지만 그래도 원래보다는 나아요. 단점은 새 부서와 해당 직원이 시작되면 새 부서의 직원이 "Others" 그룹에 포함되지 않도록 CASE 표현식을 추가해야 한다는 것입니다.

그러나 시간이 지남에 따라 모든 것을 아름답게 수행하는 법을 배우지 않으므로 선택이 데이터베이스의 새 데이터 모양에 의존하지 않고 동적입니다. 나는 우리가 어떤 종류의 요청을 제시하려고 하는지 보여주기 위해 조금 앞서 나갈 것입니다.

SELECT ISNULL(dep.Name, "Other") DepName, COUNT(DISTINCT emp.PositionID) PositionCount, COUNT(*) EmplCount, SUM(emp.Salary) SalaryAmount, AVG(emp.Salary) SalaryAvg - 플러스의 희망 사항 충족 Director FROM 직원 emp LEFT JOIN 부서 dep ON emp.DepartmentID = dep.ID GROUP BY emp.DepartmentID, dep.Name ORDER BY DepName

일반적으로 걱정하지 마십시오. 모두가 간단하게 시작했습니다. 지금은 GROUP BY 절의 요점만 이해하면 됩니다.

마지막으로 GROUP BY를 사용하여 요약 보고서를 작성하는 방법을 살펴보겠습니다.

예를 들어 부서 컨텍스트에서 피벗 테이블을 표시하여 직위별로 직원이 받는 총 급여를 계산해 보겠습니다.

SELECT DepartmentID, SUM (CASE WHEN PositionID = 1 THEN 급여 종료) [회계사], SUM (CASE WHEN PositionID = 2 THEN 급여 종료) [이사], SUM (CASE WHEN PositionID = 3 THEN 급여 종료) [프로그래머], SUM ( CASE WHEN PositionID = 4 THEN Salary END) [선임 프로그래머], SUM(연봉) [부서 합계] FROM 직원 GROUP BY DepartmentID

저것들. 집계 함수 내에서 모든 표현식을 자유롭게 사용할 수 있습니다.

물론 IIF를 사용하여 다시 작성할 수 있습니다.

SELECT DepartmentID, SUM(IIF(PositionID = 1, 급여, NULL)) [회계사], SUM(IIF(PositionID = 2, 급여, NULL)) [이사], SUM(IIF(PositionID = 3, 급여, NULL)) [프로그래머], SUM (IIF (PositionID = 4, 급여, NULL)) [선임 프로그래머], SUM (급여) [부서 합계] FROM 직원 GROUP BY DepartmentID

그러나 IIF의 경우 조건이 충족되지 않으면 반환되는 NULL을 명시적으로 지정해야 합니다.

비슷한 경우에 나는 NULL을 다시 쓰는 것보다 ELSE 블록 없이 CASE를 사용하는 것을 선호합니다. 그러나 이것은 확실히 취향의 문제이며 논쟁의 여지가 없습니다.

그리고 집계 함수에서 NULL 값은 고려되지 않는다는 것을 기억합시다.

통합하려면 확장된 요청으로 얻은 데이터를 독립적으로 분석하십시오.

SELECT DepartmentID, CASE WHEN PositionID = 1 THEN 급여 END [회계사], CASE WHEN PositionID = 2 THEN 급여 END [이사], CASE WHEN PositionID = 3 THEN 급여 END [프로그래머], CASE WHEN PositionID = 4 THEN 급여 END [시니어 프로그래머 ], 급여 [부서 합계] FROM 직원

부서 ID	회계사	이사	프로그래머	수석 프로그래머	부서별 합계
1	없는	5000	없는	없는	5000
3	없는	없는	1500	없는	1500
2	2500	없는	없는	없는	2500
3	없는	없는	없는	2000	2000
3	없는	없는	1500	없는	1500
없는	없는	없는	없는	없는	2000

또한 NULL 대신 0을 보고 싶다면 집계 함수에서 반환된 값을 처리할 수 있다는 점도 기억합시다. 예를 들어:

SELECT DepartmentID, ISNULL(SUM(IIF(PositionID = 1, 급여, NULL)), 0) [회계사], ISNULL(SUM(IIF(PositionID = 2, 급여, NULL)), 0) [이사], ISNULL(SUM (IIF (PositionID = 3, 급여, NULL)), 0) [프로그래머], ISNULL (SUM (IIF (PositionID = 4, 급여, NULL)), 0) [선임 프로그래머], ISNULL (SUM (급여), 0 ) [부서 합계] FROM 직원 GROUP BY DepartmentID

이제 연습을 위해 다음을 수행할 수 있습니다.

예를 들어 SELECT 블록에서 DepartmentID를 처리하는 CASE 표현식을 추가하여 식별자 대신 부서 이름을 표시합니다.
ORDER BY를 사용하여 부서 이름별로 정렬 추가

일반적으로 데이터가 이 형식으로 사용되기 때문에 데이터베이스에서 요약 데이터를 얻는 데 사용되는 주요 도구 중 하나인 집계 함수가 있는 희소의 GROUP BY 우리는 일반적으로 상세한 데이터(시트)보다는 요약 보고서를 제공해야 합니다. 물론 기본 디자인을 아는 것이 중요합니다. 무언가를 요약(집계)하기 전에 먼저 "SELECT ... WHERE ..."를 사용하여 올바르게 선택해야 합니다.

여기서 연습은 중요한 위치를 차지합니다. 따라서 SQL 언어를 이해하는 목표를 설정했다면 배우는 것이 아니라 이해하는 것입니다. 연습, 연습 및 연습, 생각할 수 있는 가장 다양한 옵션을 거치는 것입니다.

초기 단계에서 얻은 집계 데이터의 정확성에 대해 확신이 없으면 집계가 진행되는 모든 값을 포함하여 자세한 샘플을 만드십시오. 그리고 이 상세한 데이터를 사용하여 수동으로 계산의 정확성을 확인하십시오. 이럴 때 엑셀을 활용하면 많은 도움이 됩니다.

이 지경에 이르렀다고 가정해 봅시다.

귀하가 SELECT 쿼리 작성 방법을 배우기로 결정한 회계사 S. S. Sidorov라고 가정해 보겠습니다.
이 시점까지 이 튜토리얼을 이미 읽었고 위의 모든 기본 구성, 즉 당신은 할 수 있습니다:

하나의 테이블에서 WHERE 절로 세부 데이터 선택
하나의 테이블에서 집계 함수 및 그룹화를 사용하는 방법을 알고 있습니다.

직장에서 그들은 당신이 이미 모든 일을 하는 방법을 알고 있다고 생각했기 때문에 데이터베이스에 대한 액세스 권한이 부여되었고(가끔 이런 일이 발생함) 이제 당신은 디렉터를 위한 바로 그 주간 보고서를 개발하여 꺼내고 있습니다.

예, 하지만 그들은 아직 여러 테이블에서 쿼리를 작성할 수 없다는 점을 고려하지 않았습니다. 다음과 같은 작업을 수행하는 방법을 모릅니다.

SELECT emp. *, - 직원 테이블 dep.Name DepartmentName의 모든 필드 반환, - 부서 테이블 pos.Name PositionName의 이름 필드를 이 필드에 추가 - 또한 위치 테이블의 이름 필드를 추가 FROM Employees emp LEFT JOIN 부서 dep ON emp.DepartmentID = dep.ID LEFT JOIN 직책 pos ON emp.PositionID = pos.ID

당신이 이것을하는 방법을 모른다는 사실에도 불구하고 나를 믿으십시오. 당신은 잘했고 이미 많은 것을 성취했습니다.

그렇다면 현재 지식을 활용하고 더 생산적인 결과를 얻으려면 어떻게 해야 합니까?! 집단 정신의 힘을 사용합시다 - 우리는 당신을 위해 일하는 프로그래머에게갑니다. Andreev A.A., Petrov P.P. 또는 Nikolayev N.N., 그리고 그들 중 한 명에게 보기를 작성해 달라고 요청하십시오(VIEW 또는 "보기"만 있으면 더 빨리 이해할 수 있습니다). Ivanov II가 당신에게 업로드 한 주간 보고서에 대해 지금 너무 부족한 "부서 이름"과 "직위 이름".

때문에 모든 것을 올바르게 설명하면 IT 전문가가 즉시 원하는 것이 무엇인지 이해하고 특히 귀하를 위해 ViewEmployeesInfo라는 보기를 만들었습니다.

다음 명령이 표시되지 않음을 나타냅니다. IT 전문가는 다음을 수행합니다.

CREATE VIEW ViewEmployeesInfo AS SELECT emp. *, - 직원 테이블 dep.Name DepartmentName의 모든 필드 반환, - Departments pos.Name PositionName 테이블의 이름 필드를 이 필드에 추가 - 또한 위치 테이블 FROM의 이름 필드 추가 직원 emp LEFT JOIN 부서 dep ON emp.DepartmentID = dep.ID LEFT JOIN 직책 pos ON emp.PositionID = pos.ID

저것들. 이 모든 것은 무섭고 이해할 수 없지만 텍스트는 화면 밖에 남아 있으며 IT 전문가는 위의 모든 데이터(즉, 요청한 내용)를 반환하는 "ViewEmployeesInfo" 보기의 이름만 제공합니다.

이제 일반 테이블과 마찬가지로 이 보기로 작업할 수 있습니다.

SELECT * FROM ViewEmployeesInfo

때문에 이제 보고서에 필요한 모든 데이터가 하나의 "표"(단순히 보기)에 있으므로 주간 보고서를 쉽게 다시 실행할 수 있습니다.

SELECT DepartmentName, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(급여) SalaryAmount, AVG(급여) SalaryAvg FROM ViewEmployeesInfo emp GROUP BY DepartmentID, DepartmentName ORDER BY DepartmentName

이제 모든 부서 이름이 필드에 있고 요청이 동적이 되었으며 새 부서와 직원이 추가되면 변경됩니다. 이제 아무 것도 다시 할 필요가 없지만 일주일에 한 번 요청을 실행하고 그 결과를 감독에게 전달하는 것으로 충분합니다.

저것들. 이 경우에는 아무 것도 변경되지 않은 것처럼 계속해서 필요한 모든 데이터를 반환하는 하나의 테이블로 작업합니다(그러나 ViewEmployeesInfo 보기로 말하는 것이 더 정확할 것입니다). IT 전문가의 도움으로 DepartmentName 및 PositionName 마이닝의 세부 정보는 블랙박스에 남아 있습니다. 저것들. 보기는 일반 테이블과 동일하게 보입니다. Employees 테이블의 확장된 버전이라고 생각하십시오.

예를 들어, 모든 것이 실제로 내가 말한 대로(전체 샘플이 하나의 보기에서 나온 것인지) 확인하기 위해 진술을 작성해 보겠습니다.

SELECT ID, 이름, 급여 FROM ViewEmployeesInfo WHERE 급여가 NULL이 아니고 급여> 0 ORDER BY 이름

이 요청이 명확하기를 바랍니다.

경우에 따라 보기를 사용하면 기본 SELECT 쿼리를 작성하는 방법을 알고 있는 사용자의 경계를 크게 확장할 수 있습니다. 이 경우 보기는 사용자가 필요로 하는 모든 데이터가 포함된 평면 테이블입니다(OLAP을 이해하는 사람에게는 팩트 및 차원이 있는 OLAP 큐브의 근사치와 비교할 수 있음).

Wikipedia에서 클리핑. SQL은 최종 사용자를 위한 도구로 생각되었지만 결국 너무 복잡해져서 프로그래머의 도구가 되었습니다.

보시다시피, 친애하는 사용자 여러분, SQL 언어는 원래 귀하를 위한 도구로 생각되었습니다. 따라서 모든 것이 당신의 손에 달려 있으며 손을 놓지 마십시오.

HAVING - 그룹화된 데이터에 선택 조건 부과

실제로 그룹핑이 무엇인지 이해하면 HAVING에 복잡한 것이 없습니다. HAVING은 WHERE와 다소 유사하지만 세부 데이터에 WHERE 조건을 적용하면 이미 그룹화된 데이터에 HAVING 조건이 적용됩니다. 이러한 이유로 HAVING 블록의 조건에서는 그룹화에 포함된 필드가 있는 표현식이나 집계 함수에 포함된 표현식을 사용할 수 있습니다.

예를 들어 보겠습니다.

SELECT DepartmentID, SUM(급여) SalaryAmount FROM Employees GROUP BY DepartmentID HAVING SUM(급여)> 3000

부서 ID	급여액
1	5000
3	5000

저것들. 이 요청은 모든 직원의 총 급여가 3000을 초과하는 부서에 대해서만 그룹화된 데이터를 반환했습니다. "합계(급여)> 3000".

저것들. 여기에서 우선 그룹화가 발생하고 모든 부서에 대한 데이터가 계산됩니다.

SELECT DepartmentID, SUM (Salary) SalaryAmount FROM Employees GROUP BY DepartmentID - 1. 모든 부서에 대한 그룹화된 데이터 가져오기

그리고 이미 HAVING 블록에 지정된 조건이 이 데이터에 적용되었습니다.

SELECT DepartmentID, SUM(Salary) SalaryAmount FROM Employees GROUP BY DepartmentID - 1. 모든 부서에 대한 그룹화된 데이터 가져오기 HAVING SUM(Salary)> 3000 - 2.그룹화된 데이터 필터링 조건

HAVING 조건에서 AND, OR 및 NOT 연산자를 사용하여 복잡한 조건을 작성할 수도 있습니다.

SELECT DepartmentID, SUM(급여) SalaryAmount FROM Employees GROUP BY DepartmentID HAVING SUM(급여)> 3000 AND COUNT(*)<2 -- и число людей меньше 2-х

여기에서 볼 수 있듯이 집계 함수("COUNT(*)" 참조)는 HAVING 블록에서만 지정할 수 있습니다.

따라서 HAVING 조건과 일치하는 부서 번호만 표시할 수 있습니다.

SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING SUM(급여) > 3000 AND COUNT(*)<2 -- и число людей меньше 2-х

GROUP BY에 포함된 필드에 HAVING 조건을 사용하는 예:

SELECT DepartmentID, SUM (Salary) SalaryAmount FROM Employees GROUP BY DepartmentID - 1. 그룹화 HAVING DepartmentID = 3 - 2. 그룹화 결과 필터링

이것은 단지 예일 뿐입니다. 이 경우 WHERE 조건을 통해 확인하는 것이 더 논리적입니다.

SELECT DepartmentID, SUM (Salary) SalaryAmount FROM Employees WHERE DepartmentID = 3 - 1. 세부 데이터 필터링 GROUP BY DepartmentID - 2. 선택한 레코드로만 그룹화

저것들. 먼저 부서 3별로 직원을 필터링한 다음 계산을 수행합니다.

메모.실제로 두 쿼리가 다르게 보여도 DBMS 옵티마이저는 동일한 방식으로 쿼리를 실행할 수 있습니다.

HAVING 조건에 대한 이야기는 여기서 끝이라고 생각합니다.

요약하자면

두 번째 및 세 번째 부분에서 얻은 데이터를 요약하고 우리가 연구한 각 구조의 특정 위치를 고려하고 구현 순서를 표시해 보겠습니다.

건설 / 블록	실행 명령	수행된 기능
SELECT 반환 표현식	4	요청에 의해 수신된 데이터 반환
출처에서	0	우리의 경우 이것은 지금까지 테이블의 모든 행입니다.
WHERE 소스 선택 조건	1	조건과 일치하는 행만 선택됩니다.
GROUP BY 그룹화 표현식	2	지정된 그룹화 표현식으로 그룹을 생성합니다. SELECT 또는 HAVING 블록에서 사용되는 이러한 그룹에 대한 집계 값 계산
그룹화된 데이터에 대한 HAVING 필터	3	그룹화된 데이터에 적용된 필터링
결과를 정렬하는 ORDER BY 표현식	5	지정된 표현식으로 데이터 정렬

물론 2부에서 배운 DISTINCT 및 TOP 절을 그룹화된 데이터에 적용할 수도 있습니다.

이 경우 다음 제안이 최종 결과에 적용됩니다.

SELECT TOP 1 - 6. 마지막 SUM (Salary) SalaryAmount FROM Employees GROUP BY DepartmentID HAVING SUM (Salary)> 3000 ORDER BY DepartmentID - 5.결과 정렬

이러한 결과를 얻은 방법을 직접 분석하십시오.

결론

이 부분에서 설정한 주요 목표는 집계 함수와 그룹화의 본질을 밝히는 것입니다.

기본 설계를 통해 필요한 세부 데이터를 얻을 수 있었다면 이 세부 데이터에 집계 함수 및 그룹화를 적용하면 이에 대한 요약 데이터를 얻을 수 있습니다. 보시다시피 여기에서 모든 것이 중요합니다. tk. 하나는 다른 하나를 기반으로합니다. 기본 구조에 대한 지식이 없으면 예를 들어 합계를 계산하는 데 필요한 데이터를 올바르게 선택할 수 없습니다.

여기서는 초보자의 주의를 가장 중요한 구조에 집중하고 불필요한 정보로 과부하가 걸리지 않도록 하기 위해 의도적으로 기본만 보여주려고 합니다. 기본 구조(다음 부분에서 계속 이야기할 것임)에 대한 확실한 이해는 RDB에서 데이터를 가져오는 거의 모든 문제를 해결할 수 있는 기회를 제공합니다. SELECT 문의 기본 구성은 거의 모든 DBMS에서 동일한 형식으로 적용할 수 있습니다(차이점은 주로 문자열, 시간 등으로 작업하는 함수 구현과 같은 세부 사항에 있음).

결과적으로 기초에 대한 확실한 지식은 다음과 같은 SQL 언어의 다양한 확장을 스스로 쉽게 배울 수 있는 기회를 제공합니다.

GROUP BY ROLLUP(…), GROUP BY GROUPING SETS(…),…
피벗, 언피벗
등.

이 자습서의 목적을 위해 이러한 확장에 대해 이야기하지 않기로 결정했습니다. 그리고 그들의 지식 없이는 SQL 언어의 기본 구성만 알면 매우 광범위한 문제를 해결할 수 있습니다. SQL 언어의 확장은 실제로 특정 범위의 작업을 해결하는 데 사용됩니다. 특정 클래스의 문제를 보다 우아하게 해결할 수 있습니다(그러나 속도 또는 리소스 소비 측면에서 항상 더 효율적이지는 않음).

SQL에서 첫 걸음을 내딛는다면 무엇보다도 기본 구성에 대한 연구에 집중하십시오. 기지를 소유하면 다른 모든 것이 훨씬 더 쉽게 이해할 수 있을 뿐만 아니라 스스로도 이해할 수 있습니다. 먼저 SQL 언어의 기능을 깊이 이해해야 합니다. 일반적으로 데이터에 대해 수행할 수 있는 작업의 종류입니다. 초보자들에게 방대한 양의 정보를 전달하는 것도 가장 중요한(철) 구조만 보여드리는 또 다른 이유입니다.

SQL 언어를 배우고 이해하는 데 행운을 빕니다.

4부 -

1부 - habrahabr.ru/post/255361
2부 - habrahabr.ru/post/255523

이 부분에서 다룰 내용

이 부분에서 우리는 알게 될 것입니다:

쿼리에 조건식을 포함할 수 있는 CASE 표현식 사용
"SELECT ... WHERE ..." 연산자로 얻은 자세한 데이터를 기반으로 계산된 모든 종류의 합계(집계 값)를 얻을 수 있는 집계 함수 사용
집계 함수에도 불구하고 그룹 컨텍스트에서 자세한 데이터에 대한 합계를 얻을 수 있는 GROUP BY 절을 사용합니다.
그룹화된 데이터를 필터링할 수 있는 HAVING 절을 사용합니다.

CASE 표현식 - SQL 조건문

이 연산자를 사용하면 조건을 확인하고 특정 조건의 이행에 따라 하나 또는 다른 결과를 반환할 수 있습니다.

CASE 문에는 두 가지 형식이 있습니다.

표현도 의미로 사용할 수 있습니다.

첫 번째 CASE 형식의 예를 살펴보겠습니다.

SELECT ID, 이름, 급여, CASE WHEN 급여> = 3000 THEN "RFP> = 3000" WHEN 급여> = 2000 THEN "2000<= ЗП < 3000" ELSE "ЗП < 2000" END SalaryTypeWithELSE, CASE WHEN Salary>= 3000 THEN "급여> = 3000" WHEN 급여> = 2000 THEN "2000<= ЗП < 3000" END SalaryTypeWithoutELSE FROM Employees

WHEN 조건은 위에서 아래로 순차적으로 테스트됩니다. 첫 번째 만족 조건에 도달하면 추가 검사가 중단되고 이 WHEN 절에 대한 THEN 단어 뒤에 지정된 값이 반환됩니다.

WHEN 조건이 충족되지 않으면 ELSE 단어 뒤에 지정된 값이 반환됩니다(이 경우 "ELSE RETURN ..."을 의미함).

ELSE 블록이 지정되지 않고 WHEN 조건이 충족되지 않으면 NULL이 반환됩니다.

첫 번째 및 두 번째 형식 모두에서 ELSE 블록은 CASE 구조의 맨 끝에 있습니다. 모든 WHEN 조건 후에.

두 번째 CASE 형식의 예를 살펴보겠습니다.

새해에 모든 직원에게 보상하기로 결정하고 다음 계획에 따라 보너스 금액을 계산하도록 요청했다고 가정해 보겠습니다.

IT 부서 직원에게는 급여의 15%를 제공합니다.
회계 부서 직원 급여의 10%;
나머지는 급여의 5%.

이 작업을 위해 CASE 표현식이 있는 쿼리를 사용합니다.

SELECT ID, Name, Salary, DepartmentID, - 명확성을 위해 백분율을 줄로 표시합니다 CASE DepartmentID - 확인된 값 WHEN 2 THEN "10%" - 회계사에게 발행된 급여의 10% WHEN 3 THEN "15%" - 급여의 15%를 IT 직원에게 줄 ELSE "5%" - 다른 모든 사람에게 5% END NewYearBonusPercent, - 보너스 금액을 보기 위해 CASE를 사용하여 표현식을 작성해 봅시다. Salary / 100 * CASE DepartmentID WHEN 2 THEN 10 - 급여의 10% 발행 회계사 WHEN 3 THEN 15 - IT 직원 급여의 15% 발행 ELSE 5 - 그 외 모든 직원 각 5% END BonusAmount FROM

WHEN 값에 대해 DepartmentID 값을 순차적으로 검사합니다. 첫 번째 DepartmentID가 WHEN 값과 같으면 검사가 중단되고 이 WHEN 절에 대한 THEN 단어 뒤에 지정된 값이 반환됩니다.

따라서 DepartmentID가 WHEN 값과 일치하지 않으면 ELSE 블록의 값이 반환됩니다.

ELSE 블록이 없으면 DepartmentID가 WHEN 값과 일치하지 않으면 NULL이 반환됩니다.

두 번째 CASE 형식은 첫 번째 형식을 사용하여 표현하기 쉽습니다.

SELECT ID, Name, Salary, DepartmentID, CASE WHEN DepartmentID = 2 THEN "10%" - 회계사에게 지급할 급여의 10% WHEN DepartmentID = 3 THEN "15%" - IT 직원에게 지급할 급여의 15% ELSE " 5% "- 다른 모든 사람 5% END NewYearBonusPercent, - CASE를 사용하여 보너스 금액을 확인하는 표현식 작성 Salary / 100 * CASE WHEN DepartmentID = 2 THEN 10 - 10%를 회계사에게 WHEN DepartmentID = 3 THEN 15 - IT 직원에게 급여의 15%를 발급합니다. ELSE 5 - 그 외 모든 직원은 각 5% END Bonus FROM 직원

따라서 두 번째 형식은 모든 WHEN 값/표현식과 동일한 테스트 값의 동등 비교를 수행해야 하는 경우에 대한 단순화된 표기법입니다.

메모. CASE의 첫 번째 및 두 번째 형식은 SQL 언어 표준에 포함되어 있으므로 대부분의 DBMS에 적용할 수 있을 것입니다.

MS SQL 버전 2012에서는 단순화된 IIF 표기법이 등장했습니다. 2개의 값만 반환되는 경우 CASE 문을 단순화하는 데 사용할 수 있습니다. IIF 디자인은 다음과 같습니다.

IIF(조건, true_value, false_value)

저것들. 실제로 다음 CASE 구성에 대한 래퍼입니다.

CASE WHEN 조건 THEN true_value ELSE false_value END

예를 들어 보겠습니다.

SELECT ID, 이름, 급여, IIF (급여> = 2500, "급여> = 2500", "급여< 2500") DemoIIF, CASE WHEN Salary>= 2500 다음 "RFP> = 2500" 그렇지 않으면 "RFP< 2500" END DemoCASE FROM Employees

CASE, IIF 구문은 서로 중첩될 수 있습니다. 추상적인 예를 살펴보겠습니다.

ID, 이름, 급여, CASE WHEN DepartmentID IN (1,2) THEN "A" WHEN DepartmentID = 3 THEN CASE PositionID - 중첩 CASE WHEN 3 THEN "B-1" WHEN 4 THEN "B-2" END ELSE " C "END Demo1, IIF(DepartmentID IN (1,2)," A ", IIF(DepartmentID = 3, CASE PositionID WHEN 3 THEN" B-1 "WHEN 4 THEN" B-2 "END," C ")) Demo2 직원으로부터

CASE 및 IIF 구문은 결과를 반환하는 표현식이므로 SELECT 블록뿐만 아니라 WHERE 또는 ORDER BY 절과 같이 표현식을 사용할 수 있는 다른 블록에서도 사용할 수 있습니다.

예를 들어 다음과 같이 급여 지급 목록을 작성하는 작업이 있다고 가정해 보겠습니다.

우선 급여가 2500 미만인 직원이 급여를 받아야 합니다.
급여가 2,500 이상인 직원은 2 위 급여를받습니다.
이 두 그룹 내에서 전체 이름(이름 필드)으로 라인을 정렬해야 합니다.

ORDER BY 블록에 CASE 표현식을 추가하여 이 문제를 해결해 보겠습니다.

SELECT ID, 이름, 급여 FROM 직원 ORDER BY CASE WHEN Salary> = 2500 THEN 1 ELSE 0 END, - 급여가 2500 미만인 사람에게 먼저 급여를 발급합니다. 이름 - 목록을 전체 이름 순으로 추가 정렬

보시다시피 Ivanov와 Sidorov는 마지막으로 퇴근합니다.

WHERE 절에서 CASE를 사용하는 추상적인 예:

SELECT ID, 이름, 급여 FROM Employees WHERE CASE WHEN Salary> = 2500 THEN 1 ELSE 0 END = 1 - 표현식이 1인 모든 레코드

IIF 함수를 사용하여 마지막 2개의 예제를 직접 다시 실행해 볼 수 있습니다.

마지막으로 NULL 값에 대해 다시 기억해 보겠습니다.

SELECT ID, Name, Salary, DepartmentID, CASE WHEN DepartmentID = 2 THEN "10%" - 회계사에게 지급할 급여의 10% WHEN DepartmentID = 3 THEN "15%" - IT 직원에게 급여의 15% 지급 WHEN DepartmentID IS NULL THEN "-" - 우리는 프리랜서에게 보너스를 제공하지 않습니다(IS NULL 사용) ELSE "5%" - 다른 모든 사람은 각각 5%를 갖습니다 END NewYearBonusPercent1, - 그러나 NULL을 확인할 수 없습니다. NULL에 대해 말한 것을 기억하십시오 CASE DepartmentID의 두 번째 부분 - - 체크 값 WHEN 2 THEN "10%" WHEN 3 THEN "15%" WHEN NULL THEN "-" - !!! 이 경우 두 번째 CASE 형식을 사용하는 것은 적합하지 않습니다. ELSE "5%" END NewYearBonusPercent2 FROM Employees

물론 다음과 같이 다시 작성할 수 있습니다.

SELECT ID, Name, Salary, DepartmentID, CASE ISNULL (DepartmentID, -1) - NULL인 경우 -1 WHEN 2 THEN "10%" WHEN 3 THEN "15%" WHEN -1 THEN "-" - ID가 (-1)인 부서가 없고 ELSE "5%"가 없다고 확신하는 경우 END NewYearBonusPercent3 FROM Employees

일반적으로 이 경우 상상력의 비행은 제한되지 않습니다.

예를 들어 CASE 및 IIF를 사용하여 ISNULL 함수를 모델링하는 방법을 살펴보겠습니다.

SELECT ID, 이름, 성, ISNULL(성, "지정되지 않음") DemoISNULL, CASE WHEN 성 IS NULL THEN "지정되지 않음" ELSE 성 END DemoCASE, IIF(성이 NULL, "지정되지 않음", 성) DemoIIF FROM Employees

CASE 구문은 결과 집합의 값을 계산하기 위해 추가 논리를 부과할 수 있는 매우 강력한 SQL 기능입니다. 이 부분에서 CASE 구성의 소유는 여전히 우리에게 유용할 것이므로 이 부분에서는 우선 주의를 기울입니다.

집계 함수

여기서는 기본적이고 가장 일반적으로 사용되는 집계 함수만 다룹니다.

이름	설명
세다 (*)	"SELECT ... WHERE ..." 연산자가 받은 행 수를 반환합니다. WHERE가 없는 경우 테이블의 모든 레코드 수입니다.
COUNT(열/식)	지정된 열/표현식에서 NULL이 아닌 값의 수를 반환합니다.
COUNT(DISTINCT 열/식)	지정된 열/표현식에서 고유한 NULL이 아닌 값의 수를 반환합니다.
SUM(열/식)	열 / 표현식의 값에 대한 합계를 반환합니다.
AVG(열/식)	열 / 표현식의 값에 대한 평균을 반환합니다. NULL 값은 계산에 포함되지 않습니다.
MIN(열/식)	열/표현식의 값에 대한 최소값을 반환합니다.
MAX(열/식)	열/표현식의 값에 대한 최대값을 반환합니다.

집계 함수를 사용하면 SELECT 문을 사용하여 얻은 일련의 행에 대한 총 값을 계산할 수 있습니다.

예를 들어 각 기능을 살펴보겠습니다.

SELECT COUNT (*) [총 직원 수], COUNT (DISTINCT DepartmentID) [고유 부서 수], COUNT (DISTINCT PositionID) [고유 직위 수], COUNT (BonusPercent) [% 상여금이 있는 직원 수] , MAX (BonusPercent) [최대 보너스 백분율], MIN (BonusPercent) [최소 보너스 백분율], SUM (급여 / 100 * BonusPercent) [모든 보너스의 합계], AVG (급여 / 100 * BonusPercent) [평균 보너스 크기], AVG ( 급여) [평균 급여] FROM 직원

명확성을 위해 여기에서 예외를 만들고 [...] 구문을 사용하여 열 별칭을 정의했습니다.

각 반환 값이 어떻게 생성되었는지 살펴보고 먼저 SELECT 문의 기본 구문 구성을 상기해 보겠습니다.

첫째, 왜냐하면 쿼리에서 WHERE 조건을 지정하지 않은 경우 쿼리에서 얻은 자세한 데이터에 대해 합계가 계산됩니다.

SELECT * FROM 직원

저것들. 직원 테이블의 모든 행에 대해

명확성을 위해 집계 함수에 사용되는 필드와 표현식만 선택합니다.

SELECT DepartmentID, PositionID, BonusPercent, 급여 / 100 * BonusPercent, 급여 FROM 직원

부서 ID	위치ID	보너스퍼센트	급여 / 100 * 보너스퍼센트	샐러리
1	2	50	2500	5000
3	3	15	225	1500
2	1	없는	없는	2500
3	4	30	600	2000
3	3	없는	없는	1500
없는	없는	없는	없는	2000

이것은 집계된 쿼리의 총계가 계산될 초기 데이터(상세 라인)입니다.

이제 각 집계 값을 살펴보겠습니다.

세다 (*)- 부터 쿼리의 WHERE 절에 필터링 조건을 지정하지 않은 경우 COUNT(*)는 테이블의 총 레코드 수, 즉 이것은 쿼리에서 반환된 행 수입니다.

SELECT * FROM 직원

SELECT DISTINCT DepartmentID - 2. DepartmentID가 NULL이 아닌 곳에서 직원의 고유한 값만 취합니다. - 1. NULL 값을 버립니다.

COUNT(DIISTINCT PositionID)- COUNT(DISTINCT DepartmentID)에 대해 말한 것과 동일하지만 PositionID 필드만. PositionID 열의 값을 보고 색상을 후회하지 않습니다.

3개의 값이 남아 있습니다. 저것들. 확장된 형태로 샘플은 다음과 같이 나타낼 수 있습니다.

SELECT BonusPercent - 2. BonusPercent가 NULL이 아닌 곳에서 직원의 모든 값을 가져옵니다. - 1. NULL 값을 버립니다.

SELECT COUNT(*), - 6 COUNT(DISTINCT DepartmentID), - 3 COUNT(DepartmentID) - 5 FROM 직원

SELECT TOP 1 BonusPercent FROM 직원 WHERE BonusPercent IS NOT NULL ORDER BY BonusPercent DESC - 내림차순으로 정렬

MIN(보너스백분율)- 다시 NULL 값을 제외하고 최소 BonusPercent 값을 반환합니다. MAX의 경우와 마찬가지로 NULL을 무시하고 최소값만 찾습니다.

저것들. 다음 값을 얻습니다.

SELECT TOP 1 BonusPercent FROM 직원 WHERE BonusPercent IS NOT NULL ORDER BY BonusPercent - 오름차순으로 정렬

MIN(BonusPercent) 및 MAX(BonusPercent)의 시각적 표현:

SUM (급여 / 100 * BonusPercent)- NULL이 아닌 모든 값의 합계를 반환합니다. 표현식의 값을 구문 분석합니다(Salary / 100 * BonusPercent):

저것들. 다음 값이 추가됩니다.

SELECT 급여 / 100 * BonusPercent FROM 직원 WHERE 급여 / 100 * BonusPercent IS NOT NULL

AVG (급여 / 100 * BonusPercent)- 값의 평균을 반환합니다. NULL 표현식은 무시됩니다. 이것은 두 번째 표현식과 일치합니다.

SELECT AVG (급여 / 100 * BonusPercent), - 1108.333333333333 SUM (급여 / 100 * BonusPercent) / COUNT (급여 / 100 * 6 6 직원 6 F 6 6 COUNT), - 1108.333333333333 SUM00 (급여 /Percent)

저것들. 다시 말하지만, 수량을 계산할 때 NULL 값은 고려되지 않습니다.

554.166666666667을 제공하는 세 번째 표현식에서와 같이 모든 직원의 평균을 계산해야 하는 경우 NULL 값을 0으로 예비 변환합니다.

SELECT AVG(ISNULL(급여 / 100 * BonusPercent, 0)), - 554.166666666667 SUM(급여 / 100 * BonusPercent) / COUNT(*) - 554.1666666666667 FROM 직원

결과를 요약하자면 다음과 같습니다.

COUNT(*) - "SELECT ... WHERE ..." 연산자가 수신한 총 행 수를 계산하는 역할을 합니다.
위의 다른 모든 집계 함수에서 합계를 계산할 때 NULL 값은 고려되지 않습니다
모든 행을 고려해야 하는 경우 AVG 함수와 더 관련이 있는 경우 먼저 NULL 값을 처리해야 합니다(예: 위에 표시된 "AVG(ISNULL(Salary, 0))").

SELECT DepartmentID, PositionID, BonusPercent, Salary / 100 * BonusPercent, Salary FROM Employees WHERE DepartmentID = 3 - IT 부서만 포함

부서 ID	위치ID	보너스퍼센트	급여 / 100 * 보너스퍼센트	샐러리
3	3	15	225	1500
3	4	30	600	2000
3	3	없는	없는	1500

(열 이름 없음)	(열 이름 없음)	(열 이름 없음)	(열 이름 없음)
없는	없는	0	0

각 집계 함수의 목적과 계산 방법을 이해하는 것이 매우 중요하다고 생각합니다. SQL에서 합계를 계산하는 주요 도구입니다.

GROUP BY - 데이터 그룹화

그 전에 우리는 대략 다음과 같이 특정 부서에 대한 총계를 이미 계산했습니다.

SELECT COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(급여) SalaryAmount FROM Employees WHERE DepartmentID = 3 - IT 부서 전용 데이터

결과적으로 4개의 데이터 세트를 얻습니다.

상수로 지정된 필드("관리", "회계", ...)를 사용할 수 있습니다.

일반적으로 우리는 우리에게 요청한 모든 숫자를 추출하고 모든 것을 Excel로 결합하여 감독에게 제공합니다.

흠, 어떡하지?! 또한 우리 부서가 3이 아니라 15라고 상상해 봅시다.

이것이 바로 GROUP BY 절이 이러한 경우에 해당하는 것입니다.

부서 ID	위치 수	EmplCount	급여액	급여평균
없는	0	1	2000	2000
1	1	1	5000	5000
2	1	1	2500	2500
3	2	3	5000	1666.66666666667

우리는 모두 동일한 데이터를 얻었지만 이제 하나의 요청만 사용합니다!

지금은 부서가 숫자 형식으로 표시된다는 사실에주의를 기울이지 말고 모든 것을 아름답게 표시하는 방법을 배웁니다.

예를 들어 부서 및 직책별로 데이터를 그룹화해 보겠습니다.

SELECT DepartmentID, PositionID, COUNT(*) EmplCount, SUM(급여) 급여 FROM 직원 GROUP BY DepartmentID, PositionID

그런 다음 각 조합을 실행하고 집계 함수를 계산합니다.

그런 다음 이 모든 결과가 함께 결합되어 하나의 집합으로 제공됩니다.

주요 항목에서 그룹화(GROUP BY)의 경우 SELECT 블록의 열 목록에서 다음을 확인할 수 있습니다.

GROUP BY 절에 나열된 열만 사용할 수 있습니다.
GROUP BY 블록의 필드와 함께 표현식을 사용할 수 있습니다.
상수를 사용할 수 있습니다. 그들은 그룹화 결과에 영향을 미치지 않습니다
다른 모든 필드(GROUP BY 블록에 나열되지 않음)는 집계 함수(COUNT, SUM, MIN, MAX, ...)에만 사용할 수 있습니다.
SELECT 열 목록의 GROUP BY 절에서 모든 열을 나열할 필요는 없습니다.

그리고 말한 모든 것을 보여줍니다.

SELECT CONCAT("생년 -", YEAR(생일)) YearOfBirthday, COUNT(*) EmplCount FROM 직원 GROUP BY YEAR(생일)

좀 더 복잡한 표현이 있는 예를 살펴보겠습니다. 예를 들어, 출생 연도별로 직원의 등급을 구해 보겠습니다.

범위 이름	EmplCount
1979-1970	1
1989-1980	2
표시되지 않음	2
1970년 초	1

저것들. 이 경우 그룹화는 각 직원에 대해 이전에 계산된 CASE 표현식에 따라 수행됩니다.

물론 GROUP BY 블록의 필드와 표현식을 결합할 수 있습니다.

일반적으로 걱정하지 마십시오. 모두가 간단하게 시작했습니다. 지금은 GROUP BY 절의 요점만 이해하면 됩니다.

마지막으로 GROUP BY를 사용하여 요약 보고서를 작성하는 방법을 살펴보겠습니다.

예를 들어 부서 컨텍스트에서 피벗 테이블을 표시하여 직위별로 직원이 받는 총 급여를 계산해 보겠습니다.

물론 IIF를 사용하여 다시 작성할 수 있습니다.

그러나 IIF의 경우 조건이 충족되지 않으면 반환되는 NULL을 명시적으로 지정해야 합니다.

그리고 집계 함수에서 NULL 값은 고려되지 않는다는 것을 기억합시다.

통합하려면 확장된 요청으로 얻은 데이터를 독립적으로 분석하십시오.

부서 ID	회계사	이사	프로그래머	수석 프로그래머	부서별 합계
1	없는	5000	없는	없는	5000
3	없는	없는	1500	없는	1500
2	2500	없는	없는	없는	2500
3	없는	없는	없는	2000	2000
3	없는	없는	1500	없는	1500
없는	없는	없는	없는	없는	2000

또한 NULL 대신 0을 보고 싶다면 집계 함수에서 반환된 값을 처리할 수 있다는 점도 기억합시다. 예를 들어:

예를 들어 SELECT 블록에서 DepartmentID를 처리하는 CASE 표현식을 추가하여 식별자 대신 부서 이름을 표시합니다.
ORDER BY를 사용하여 부서 이름별로 정렬 추가

이 지경에 이르렀다고 가정해 봅시다.

하나의 테이블에서 WHERE 절로 세부 데이터 선택
하나의 테이블에서 집계 함수 및 그룹화를 사용하는 방법을 알고 있습니다.

예, 하지만 그들은 아직 여러 테이블에서 쿼리를 작성할 수 없다는 점을 고려하지 않았습니다. 다음과 같은 작업을 수행하는 방법을 모릅니다.

때문에 모든 것을 올바르게 설명하면 IT 전문가가 즉시 원하는 것이 무엇인지 이해하고 특히 귀하를 위해 ViewEmployeesInfo라는 보기를 만들었습니다.

다음 명령이 표시되지 않음을 나타냅니다. IT 전문가는 다음을 수행합니다.

이제 일반 테이블과 마찬가지로 이 보기로 작업할 수 있습니다.

예를 들어, 모든 것이 실제로 내가 말한 대로(전체 샘플이 하나의 보기에서 나온 것인지) 확인하기 위해 진술을 작성해 보겠습니다.

SELECT ID, 이름, 급여 FROM ViewEmployeesInfo WHERE 급여가 NULL이 아니고 급여> 0 ORDER BY 이름

이 요청이 명확하기를 바랍니다.

Wikipedia에서 클리핑. SQL은 최종 사용자를 위한 도구로 생각되었지만 결국 너무 복잡해져서 프로그래머의 도구가 되었습니다.

HAVING - 그룹화된 데이터에 선택 조건 부과

예를 들어 보겠습니다.

SELECT DepartmentID, SUM(급여) SalaryAmount FROM Employees GROUP BY DepartmentID HAVING SUM(급여)> 3000

부서 ID	급여액
1	5000
3	5000

저것들. 이 요청은 모든 직원의 총 급여가 3000을 초과하는 부서에 대해서만 그룹화된 데이터를 반환했습니다. "합계(급여)> 3000".

저것들. 여기에서 우선 그룹화가 발생하고 모든 부서에 대한 데이터가 계산됩니다.

SELECT DepartmentID, SUM (Salary) SalaryAmount FROM Employees GROUP BY DepartmentID - 1. 모든 부서에 대한 그룹화된 데이터 가져오기

그리고 이미 HAVING 블록에 지정된 조건이 이 데이터에 적용되었습니다.

HAVING 조건에서 AND, OR 및 NOT 연산자를 사용하여 복잡한 조건을 작성할 수도 있습니다.

SELECT DepartmentID, SUM(급여) SalaryAmount FROM Employees GROUP BY DepartmentID HAVING SUM(급여)> 3000 AND COUNT(*)<2 -- и число людей меньше 2-х

여기에서 볼 수 있듯이 집계 함수("COUNT(*)" 참조)는 HAVING 블록에서만 지정할 수 있습니다.

따라서 HAVING 조건과 일치하는 부서 번호만 표시할 수 있습니다.

SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING SUM(급여) > 3000 AND COUNT(*)<2 -- и число людей меньше 2-х

GROUP BY에 포함된 필드에 HAVING 조건을 사용하는 예:

SELECT DepartmentID, SUM (Salary) SalaryAmount FROM Employees GROUP BY DepartmentID - 1. 그룹화 HAVING DepartmentID = 3 - 2. 그룹화 결과 필터링

이것은 단지 예일 뿐입니다. 이 경우 WHERE 조건을 통해 확인하는 것이 더 논리적입니다.

SELECT DepartmentID, SUM (Salary) SalaryAmount FROM Employees WHERE DepartmentID = 3 - 1. 세부 데이터 필터링 GROUP BY DepartmentID - 2. 선택한 레코드로만 그룹화

저것들. 먼저 부서 3별로 직원을 필터링한 다음 계산을 수행합니다.

메모.실제로 두 쿼리가 다르게 보여도 DBMS 옵티마이저는 동일한 방식으로 쿼리를 실행할 수 있습니다.

HAVING 조건에 대한 이야기는 여기서 끝이라고 생각합니다.

요약하자면

두 번째 및 세 번째 부분에서 얻은 데이터를 요약하고 우리가 연구한 각 구조의 특정 위치를 고려하고 구현 순서를 표시해 보겠습니다.

건설 / 블록	실행 명령	수행된 기능
SELECT 반환 표현식	4	요청에 의해 수신된 데이터 반환
출처에서	0	우리의 경우 이것은 지금까지 테이블의 모든 행입니다.
WHERE 소스 선택 조건	1	조건과 일치하는 행만 선택됩니다.
GROUP BY 그룹화 표현식	2	지정된 그룹화 표현식으로 그룹을 생성합니다. SELECT 또는 HAVING 블록에서 사용되는 이러한 그룹에 대한 집계 값 계산
그룹화된 데이터에 대한 HAVING 필터	3	그룹화된 데이터에 적용된 필터링
결과를 정렬하는 ORDER BY 표현식	5	지정된 표현식으로 데이터 정렬

물론 2부에서 배운 DISTINCT 및 TOP 절을 그룹화된 데이터에 적용할 수도 있습니다.

이 경우 다음 제안이 최종 결과에 적용됩니다.

SELECT TOP 1 - 6. 마지막 SUM (Salary) SalaryAmount FROM Employees GROUP BY DepartmentID HAVING SUM (Salary)> 3000 ORDER BY DepartmentID - 5.결과 정렬

이러한 결과를 얻은 방법을 직접 분석하십시오.

결론

이 부분에서 설정한 주요 목표는 집계 함수와 그룹화의 본질을 밝히는 것입니다.

결과적으로 기초에 대한 확실한 지식은 다음과 같은 SQL 언어의 다양한 확장을 스스로 쉽게 배울 수 있는 기회를 제공합니다.

GROUP BY ROLLUP(…), GROUP BY GROUPING SETS(…),…
피벗, 언피벗
등.

SQL 언어를 배우고 이해하는 데 행운을 빕니다.

4부 -

명령 CASE는 다음을 선택할 수 있습니다. 하나~에서 여러 명령 시퀀스... 이 구성은 1992년부터 SQL 표준에 존재했지만 Oracle8i까지는 Oracle SQL에서 지원되지 않았고 Oracle9i Release 1까지는 PL/SQL에서 지원되지 않았습니다. 이 버전부터 다음과 같은 다양한 CASE 명령이 지원됩니다.

간단한 명령 CASE - 하나 이상의 PL / SQL 명령 시퀀스를 해당 값과 바인딩합니다(실행할 시퀀스는 값 중 하나를 반환하는 표현식을 평가한 결과에 따라 선택됨).
검색팀 CASE - 부울 목록 검사 결과에 따라 실행할 명령 시퀀스를 하나 이상 선택합니다. 첫 번째 조건과 관련된 명령 시퀀스가 실행되고 그 결과는 TRUE입니다.

NULL 또는 알 수 없음?

IF 문에 대한 기사에서 부울 표현식의 결과가 TRUE, FALSE 또는 NULL일 수 있다는 것을 배웠을 것입니다.

PL/SQL에서는 이것이 사실이지만 관계 이론의 더 넓은 맥락에서 Boolean 표현식에서 NULL을 반환하는 것에 대해 이야기하는 것은 잘못된 것으로 간주됩니다. 관계 이론에 따르면 NULL과의 비교는 다음과 같습니다.

2 < NULL

논리적 결과 UNKNOWN을 제공하고 UNKNOWN은 NULL이 아닙니다. 그러나 PL/SQL이 UNKNOWN에 대해 NULL을 사용하는 것에 대해 너무 걱정할 필요는 없습니다. 그러나 3값 논리의 세 번째 값은 UNKNOWN이라는 점에 유의해야 합니다. 그리고 나는 당신이 관계 이론 분야의 전문가들과 3값 논리에 대해 논의할 때 (나처럼!) 잘못된 용어에 빠지지 않기를 바랍니다.

CASE 명령 외에도 PL/SQL은 CASE 표현식도 지원합니다. 이 표현식은 CASE 명령과 매우 유사하며 평가를 위해 하나 이상의 표현식을 선택할 수 있습니다. CASE 표현식의 결과는 하나의 값인 반면 CASE 명령의 결과는 PL/SQL 명령 시퀀스의 실행입니다.

간단한 CASE 명령

간단한 CASE 명령을 사용하면 표현식 평가 결과에 따라 실행할 PL/SQL 명령의 여러 시퀀스 중 하나를 선택할 수 있습니다. 다음과 같이 작성됩니다.

CASE 표현식 WHEN 결과_1 THEN 명령_1 WHEN 결과_2 THEN 명령_2 ... ELSE 명령_else END CASE;

ELSE 분기는 여기에서 선택 사항입니다. 이러한 명령을 실행할 때 PL/SQL은 먼저 표현식을 평가한 다음 결과를 result_1과 비교합니다. 일치하면 commands_1이 실행됩니다. 그렇지 않으면 result_2 값이 확인되는 식입니다.

다음은 직원 유형 변수의 값에 따라 보너스가 계산되는 간단한 CASE 명령의 예입니다.

CASE employee_type WHEN "S" THEN Award_salary_bonus (employee_id); WHEN "H" THEN Award_hourly_bonus(employee_id); WHEN "C" THEN Award_commissioned_bonus(employee_id); ELSE RAISE invalid_employee_type; 종료 사례;

이 예에는 명시적 ELSE 절이 있지만 일반적으로 필요하지 않습니다. ELSE 절이 없으면 PL/SQL 컴파일러는 암시적으로 다음 코드를 대체합니다.

ELSE 인상 사례_NOT_FOUND;

즉, ELSE 키워드를 생략하고 WHEN 절의 결과가 CASE 명령의 표현식 결과와 일치하지 않으면 PL/SQL은 CASE_NOT_FOUND 예외를 발생시킵니다. 이것이 이 명령과 IF의 차이점입니다. IF 명령에 ELSE 키워드가 없으면 조건이 충족되지 않아도 아무 일도 일어나지 않는 반면 CASE 명령에서는 비슷한 상황이 오류를 발생시킵니다.

간단한 CASE 명령을 사용하여 장의 시작 부분에 설명된 보너스 계산 논리를 구현하는 방법을 보는 것은 흥미로울 것입니다. 언뜻보기에는 불가능한 것처럼 보이지만 창의적으로 비즈니스에 착수하면 다음 솔루션에 도달합니다.

CASE TRUE WHEN 급여> = 10000 AND 급여<=20000 THEN give_bonus(employee_id, 1500); WHEN salary >20,000 AND 급여<= 40000 THEN give_bonus(employee_id, 1000); WHEN salary >40000 THEN Give_bonus (employee_id, 500); ELSE Give_bonus (employee_id, 0); 종료 사례;

여기서 중요한 것은 표현식과 결과 요소가 스칼라 값이거나 결과가 스칼라 값인 표현식이 될 수 있다는 것입니다.

동일한 논리를 구현하는 IF ... THEN ... ELSIF 명령으로 돌아가면 CASE 명령에 ELSE 섹션이 정의되어 있는 반면 IF – THEN – ELSIF 명령에는 ELSE 키워드가 없는 것을 볼 수 있습니다. ELSE를 추가하는 이유는 간단합니다. 보너스 조건이 충족되지 않으면 IF 명령은 아무 작업도 수행하지 않으며 보너스는 0입니다. 이 경우 CASE 명령은 오류를 생성하므로 프리미엄이 0인 상황을 명시적으로 프로그래밍해야 합니다.

CASE_NOT_FOUND 오류를 방지하려면 테스트 중인 표현식의 값에 대해 조건 중 하나 이상이 충족되는지 확인하십시오.

위의 CASE TRUE 명령은 어떤 사람들에게는 속임수처럼 들릴 수도 있지만 실제로는 CASE 검색 명령을 구현하는 것일 뿐이며 이에 대해서는 다음 섹션에서 설명합니다.

CASE 검색 명령어

CASE 검색 명령은 부울 표현식 목록을 검사합니다. TRUE와 같은 표현식을 찾으면 연관된 명령 시퀀스를 실행합니다. 본질적으로 CASE 검색 명령은 이전 섹션에 표시된 CASE TRUE 명령과 유사합니다. CASE 검색 명령에는 다음과 같은 표기법이 있습니다.

CASE WHEN 표현식_1 THEN 명령_1 WHEN 표현식_2 THEN 명령_2 ... ELSE 명령_else END CASE; 보너스 발생 논리를 구현하는 데 이상적입니다. CASE WHEN 급여> = 10000 AND 급여<=20000 THEN give_bonus(employee_id, 1500); WHEN salary >20,000 AND 급여<= 40000 THEN give_bonus(employee_id, 1000); WHEN salary >40000 THEN Give_bonus (employee_id, 500); ELSE Give_bonus (employee_id, 0); 종료 사례;

CASE 검색 명령은 간단한 명령과 마찬가지로 다음 규칙을 따릅니다.

명령 실행은 실제 표현식과 연관된 실행 가능한 명령 시퀀스의 실행 직후에 끝납니다. 둘 이상의 표현식이 true이면 첫 번째 표현식과 연관된 명령이 실행됩니다.
ELSE 키워드는 선택 사항입니다. 지정되지 않고 어떤 표현식도 TRUE가 아니면 CASE_NOT_FOUND 예외가 발생합니다.
WHEN 절은 처음부터 끝까지 엄격하게 정의된 순서로 테스트됩니다.

WHEN 조건이 작성된 순서대로 확인된다는 사실을 사용하는 보너스 계산 논리의 또 다른 구현을 고려하십시오. 개별 표현은 더 간단해졌지만 전체 명령의 의미가 더 명확해졌다고 할 수 있을까요?

CASE WHEN 급여> 40000 THEN Give_bonus (employee_id, 500); WHEN 급여> 20000 THEN Give_bonus (employee_id, 1000); WHEN 급여> = 10000 THEN Give_bonus (employee_id, 1500); ELSE Give_bonus (employee_id, 0); 종료 사례;

특정 직원의 급여가 20,000인 경우 처음 두 조건은 FALSE이고 세 번째 조건은 TRUE이므로 직원은 $1,500의 보너스를 받게 됩니다. 급여가 21,000이면 두 번째 조건의 결과는 TRUE이고 보너스는 $ 1,000입니다. CASE 명령의 실행은 두 번째 WHEN 분기에서 종료되고 세 번째 조건은 확인조차 하지 않습니다. CASE 명령을 작성할 때 이 접근 방식을 사용해야 하는지 여부는 논쟁의 여지가 있습니다. 그러나 그러한 명령을 작성할 수 있으며 결과가 표현식의 순서에 따라 달라지는 프로그램을 디버깅 및 편집할 때는 특별한 주의가 필요하다는 점을 염두에 두십시오.

동종 WHEN 분기의 순서에 의존하는 논리는 분기를 재배열하여 발생하는 오류의 잠재적 원인입니다. 예를 들어 급여가 20,000이고 두 WHEN 절의 조건 테스트가 TRUE로 평가되는 다음 CASE 조회 명령을 고려하십시오.

CASE WHEN 급여 BETWEEN 10000 AND 20000 THEN Give_bonus (employee_id, 1500); WHEN 급여 BETWEEN 20000 AND 40000 THEN Give_bonus (employee_id, 1000); ...

이 프로그램의 관리자가 급료 내림차순으로 정렬하기 위해 WHEN 절을 경솔하게 재정렬한다고 상상해 보십시오. 이 가능성을 거부하지 마십시오! 프로그래머는 종종 일종의 내부 순서를 기반으로 아름답게 작동하는 코드를 "조정"하는 경향이 있습니다. 재배열된 WHEN 절이 있는 CASE 명령은 다음과 같습니다.

CASE WHEN 급여 BETWEEN 20000 AND 40000 THEN Give_bonus (employee_id, 1000); WHEN 급여 BETWEEN 10000 AND 20000 THEN Give_bonus (employee_id, 1500); ...

언뜻보기에 모든 것이 정확하지 않습니까? 불행히도 두 개의 WHEN 분기가 겹치기 때문에 프로그램에 교활한 오류가 나타납니다. 이제 급여가 20,000인 직원은 필요한 1500 대신 1000의 보너스를 받게 됩니다. 어떤 상황에서는 WHEN 지점 간에 겹치는 것이 바람직할 수 있지만 가능하면 여전히 피해야 합니다. 분기 순서가 중요하다는 것을 항상 기억하고 이미 작동 중인 코드를 수정하려는 충동을 억제하십시오. "손상되지 않은 것은 수정하지 마십시오".

WHEN 조건이 순서대로 테스트되기 때문에 가장 가능성이 높은 조건을 가진 분기를 목록의 맨 위에 배치하여 코드 효율성을 약간 향상시킬 수 있습니다. 또한 "비싼" 표현식이 있는 분기가 있는 경우(예: 상당한 CPU 시간과 메모리가 필요함) 테스트될 가능성을 최소화하기 위해 끝에 배치할 수 있습니다. 자세한 내용은 중첩 IF 명령 섹션을 참조하십시오.

CASE 검색 명령은 실행할 명령이 논리식 집합으로 정의될 때 사용됩니다. 단순 CASE 명령은 단일 표현식의 결과를 기반으로 결정을 내릴 때 사용됩니다.

중첩된 CASE 명령

IF 명령과 같은 CASE 명령은 중첩될 수 있습니다. 예를 들어 중첩된 CASE 명령은 보너스 논리의 다음(오히려 혼란스러운) 구현에 나타납니다.

CASE WHEN 급여> = 10000 THEN CASE WHEN 급여<= 20000 THEN give_bonus(employee_id, 1500); WHEN salary >40000 THEN Give_bonus (employee_id, 500); WHEN 급여> 20000 THEN Give_bonus (employee_id, 1000); 종료 사례; 언제 급여< 10000 THEN give_bonus(employee_id,0); END CASE;

CASE 명령은 어떤 명령이든 사용할 수 있으므로 내부 CASE 명령을 IF 명령으로 쉽게 대체할 수 있습니다. 마찬가지로 CASE를 비롯한 모든 명령은 IF 문 내에서 중첩될 수 있습니다.

CASE 표현식

CASE 표현식은 CASE 명령과 동일한 작업을 수행하지만 실행 가능한 명령이 아니라 표현식에 대해 수행합니다. 단순 CASE 표현식은 지정된 스칼라 값을 기반으로 평가할 여러 표현식 중 하나를 선택합니다. CASE 검색 표현식은 TRUE로 평가될 때까지 목록의 표현식을 순차적으로 평가한 다음 연관된 표현식의 결과를 반환합니다.

CASE 표현식의 두 가지 유형에 대한 구문은 다음과 같습니다.

Simple_Case_expression: = CASE 표현식 WHEN result_1 THEN result_expression_1 WHEN result_2 THEN result_expression_2 ... ELSE result_expression_else END; Search_Case_expression: = CASE WHEN 식_1 THEN result_expression_1 WHEN 식_2 THEN result_expression_2 ... ELSE result_expression_else END;

CASE 표현식은 평가를 위해 선택한 표현식의 결과인 하나의 값을 리턴합니다. 각 WHEN 절은 하나의 결과 표현식과 연관되어야 합니다(명령이 아님). CASE 표현식 끝에 세미콜론이나 END CASE가 없습니다. CASE 표현식은 END 키워드로 끝납니다.

다음은 Boolean 변수의 값을 표시하기 위해 DBMS_OUTPUT 패키지의 PUT_LINE 프로시저와 함께 사용되는 간단한 CASE 표현식의 예입니다.
(PUT_LINE 프로그램은 부울을 직접 지원하지 않습니다.) 이 예에서 CASE 표현식은 부울 값을 문자열로 변환한 다음 PUT_LINE 프로시저에 의해 출력됩니다.

DECLARE boolean_true BOOLEAN: = TRUE; boolean_false 부울: = 거짓; boolean_null 부울; FUNCTION boolean_to_varchar2(플래그 IN BOOLEAN) RETURN VARCHAR2 IS BEGIN RETURN CASE 플래그 WHEN TRUE THEN "True" WHEN FALSE THEN "False" ELSE "NULL" END; 끝; BEGIN DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_true)); DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_false)); DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_null)); 끝;

보너스 계산 논리를 구현하려면 주어진 급여에 대한 보너스 값을 반환하는 CASE 검색 표현식을 사용할 수 있습니다.

DECLARE 급여 번호: = 20000; 직원 ID 번호: = 36325; 절차 Give_bonus(emp_id IN NUMBER, bonus_amt IN NUMBER)는 DBMS_OUTPUT.PUT_LINE(emp_id)을 시작합니다. DBMS_OUTPUT.PUT_LINE (bonus_amt); 끝; BEGIN Give_bonus (employee_id, CASE WHEN 급여> = 10000 AND 급여<= 20000 THEN 1500 WHEN salary >20,000 AND 급여<= 40000 THEN 1000 WHEN salary >40,000 다음 500 ELSE 0 끝); 끝;

CASE 표현식은 다른 유형의 표현식을 사용할 수 있는 모든 곳에서 사용할 수 있습니다. 다음 예에서는 CASE 표현식을 사용하여 프리미엄을 계산하고 10을 곱한 다음 결과를 DBMS_OUTPUT에 의해 표시되는 변수에 할당합니다.

DECLARE 급여 번호: = 20000; 직원 ID 번호: = 36325; 보너스 금액 NUMBER; BEGIN bonus_amount: = CASE WHEN 급여> = 10000 AND 급여<= 20000 THEN 1500 WHEN salary >20,000 AND 급여<= 40000 THEN 1000 WHEN salary >40000 THEN 500 ELSE 0 끝 * 10; DBMS_OUTPUT.PUT_LINE (보너스_금액); 끝;

CASE 명령과 달리 WHEN 절이 충족되지 않으면 CASE 표현식은 오류를 발생시키지 않고 단순히 NULL을 반환합니다.

인기있는