CASE Statement & Nested Case i SQL Server: T-SQL Eksempel

Oversikt over Case i det virkelige liv!

Igjen, i det virkelige liv utfører vi forskjellige handlinger avhengig av utfallet av forskjellige forhold. For å utdype dette ytterligere, se på eksemplet nedenfor:

• Hvis flybillettene er mindre enn $100, vil jeg besøke Los Angeles.
• Hvis flybillettene koster mellom 100 og 200 dollar, så vil jeg besøke New York.
• Hvis flybillettene koster mellom 200 og 400 dollar, så vil jeg besøke Europa.
• Ellers foretrekker jeg å besøke et turistmål i nærheten.

La oss vurdere å kategorisere tilstanden og handlingen separat fra eksemplet ovenfor:

I eksemplet ovenfor kan vi se at utfallet av de ulike betingelsene styrer en separat handling. For eksempel vil den besøkende kun utføre handlingen med å besøke New York under forutsetning av at flybilletten er mellom $100 og $200. På samme måte MS SQL CASE-setningen gir også muligheten til å utføre forskjellige T-SQL-setninger basert på resultatet av forskjellige betingelser.

Hva er CASE Statement i SQL Server?

CASE-uttalelse i SQL Server er forlengelsen av HVIS…ANNET setning. I motsetning til IF…ELSE, hvor maksimalt én betingelse er tillatt, lar CASE brukeren bruke flere betingelser for å utføre forskjellige sett med handlinger i MS SQL. Den returnerer en tilsvarende verdi knyttet til betingelsen definert av brukeren.

La oss lære hvordan vi bruker CASE i SQL og konseptet i de følgende avsnittene. I MS SQL finnes det to typer CASE:

• Enkel CASE
• Søkte i CASE

Enkel CASE

Syntaksen for enkel kasus

CASE <Case_Expression>
     WHEN Value_1 THEN Statement_1
     WHEN Value_2 THEN Statement_2
     .
     .
     WHEN Value_N THEN Statement_N
     [ELSE Statement_Else]   
END AS [ALIAS_NAME]

Her:

• Parameteren Case_Expression angir uttrykket som til slutt vil bli sammenlignet med Value_1, Value_2 og så videre.
• Parametrene Statement_1, Statement_2… angir setningene som vil bli utført hvis Case_Expression = Value_1, Case_Expression = Value_2, og så videre.
• Kort sagt er betingelsen om Case_Expression = Value_N, og handlingen er utførelsen av Statement_N hvis resultatet ovenfor er TRUE.
• ALIAS_NAME er valgfritt og er aliasnavnet som gis til resultatet av SQL Server CASE-setningen. Det brukes mest når vi bruker CASE i SQL Server SELECT-klausulen.

Regler for enkel sak

• Simple Case tillater bare en likhetssjekk av Case_Expression med Value_1 til Value_N.
• Case_Expression sammenlignes med en verdi, i rekkefølge, startende fra den første verdien, dvs. Value_1. Nedenfor er utførelsesmetoden:
• Hvis Case_Expression er ekvivalent med Value_1, hoppes ytterligere WHEN...THEN-setninger over, og CASE-kjøringen AVSLUTERES umiddelbart.
• Hvis Case_Expression ikke samsvarer med Verdi_1, sammenlignes Case_Expression med Verdi_2 for ekvivalens. Denne prosessen med å sammenligne Case_Expression med en verdi fortsetter til Case_Expression finner en samsvarende ekvivalent verdi fra settet med Verdi_1, Verdi_2 og så videre.
• Hvis ingenting samsvarer, går kontrollen til ELSE-setningen, og Statement_Else kjøres.
• ANDET er valgfritt.
• Hvis ELSE ikke finnes og Case_Expression ikke samsvarer med noen av verdiene, vises NULL.

Diagrammet nedenfor illustrerer utførelsesflyten for et enkelt tilfelle:

Eksempler

Anta at vi har en tabell som heter 'Guru99' med to kolonner, Tutorial_ID og Tutorial_name, og fire rader, som vist nedenfor. Vi vil bruke denne 'Guru99'-tabellen i eksemplene som følger.

Spørsmål 1: ENKEL CASE med NO ELSE-alternativet

SELECT Tutorial_ID, Tutorial_name,
CASE Tutorial_name
	WHEN 'SQL' THEN 'SQL is developed by IBM'
	WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
END AS Description
FROM Guru99

Resultat: Diagrammet nedenfor forklarer utførelsesflyten for et SIMPLE CASE uten NO ELSE.

Spørsmål 2: SIMPLE CASE med ELSE-alternativet

SELECT Tutorial_ID, Tutorial_name,
CASE Tutorial_name
	WHEN 'SQL' THEN 'SQL is developed by IBM'
	WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
	ELSE 'This is NO SQL language.'
END AS Description
FROM Guru99

Resultat: Diagrammet nedenfor forklarer utførelsesflyten for et SIMPLE CASE med ELSE.

Søkte i CASE

Syntaksen for søkt stor- eller liten-sak

CASE 
     WHEN <Boolean_Expression_1> THEN Statement_1
     WHEN <Boolean_Expression_2> THEN Statement_2
     .
     .
     WHEN <Boolean_Expression_N> THEN Statement_N
     [ELSE Statement_Else]   
END AS [ALIAS_NAME]

Her:

• Parameteren Boolean_Expression_1… angir uttrykket som skal evalueres for SANN eller USANN.
• Parametrene Statement_1, Statement_2… angir setningene som vil utføres hvis det tilsvarende resultatet av Boolean_Expression_1 eller Boolean_Expression_2 er SANN.
• Kort sagt er betingelsen Boolsk_Uttrykk_1… og handlingen er utførelsen av Statement_N hvis det ovennevnte Boolsk_Uttrykk_1 er SANN.
• ALIAS_NAME er valgfritt og er aliasnavnet som gis til resultatet av CASE-setningen. Det brukes mest når vi bruker CASE i SELECT-klausulen.

Regler for søkt sak

• I motsetning til det enkle tilfellet er ikke det søkte tilfellet begrenset til bare en likhetssjekk, men tillater et boolsk uttrykk.
• Det boolske uttrykket evalueres i rekkefølge, startende fra det første boolske uttrykket, dvs. Boolsk_Uttrykk_1. Nedenfor er utførelsesmetoden:
• Hvis Boolean_Expression_1 er TRUE, hoppes det over ytterligere WHEN…THEN-setninger, og CASE-kjøringen END umiddelbart.
• Hvis Boolsk_Uttrykk_1 er USANN, evalueres Boolsk_Uttrykk_2 for en SANN-betingelse. Denne prosessen med å vurdere det boolske uttrykket fortsetter til et av de boolske uttrykkene returnerer SANN.
• Hvis ingenting samsvarer, går kontrollen til ELSE-setningen, og Statement_Else kjøres.
• I likhet med enkelt tilfelle er ELSE også valgfritt i søkt tilfelle.
• Hvis ELSE ikke finnes, og ingen av de boolske uttrykkene returnerer TRUE, vises NULL.

Diagrammet nedenfor illustrerer utførelsesflyten for den søkte saken:

Eksempler

Spørsmål 1: SØKT SAKS med alternativet NO ELSE

SELECT Tutorial_ID, Tutorial_name,
CASE 
 	WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM'
	WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
END AS Description
FROM Guru99

Resultat: Diagrammet nedenfor forklarer utførelsesflyten for den SØKTE SAKEN uten NOEN ANNET.

Spørsmål 2: SØKT SAK med ELLERS-alternativet

SELECT Tutorial_ID, Tutorial_name,
CASE 
	WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM'
	WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
	ELSE 'This is NO SQL language.'
END AS Description
FROM Guru99

Resultat: Diagrammet nedenfor forklarer utførelsesflyten for SØKTE SAKE med ELSE.

Forskjellen mellom utførelsesmetoden: SIMPLE og SEARCH CASE

La oss se på eksemplet med det ENKELT TILfellet nedenfor:

SELECT Tutorial_ID, Tutorial_name,
CASE Tutorial_name
	WHEN 'SQL' THEN 'SQL is developed by IBM'
	WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
	ELSE 'This is NO SQL language.'
END AS Description
FROM Guru99

Her er «Tutorial_name» en del av CASE-uttrykket i SQL. Deretter sammenlignes verdien «Tutorial_name» med hver WHEN-verdi, dvs. «SQL» ... inntil «Tutorial_name» samsvarer med en WHEN-verdi.

Tvert imot har SEARCH CASE-eksemplet ikke noe CASE-uttrykk:

SELECT Tutorial_ID, Tutorial_name,
CASE 
 	WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM'
	WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
END AS Description
FROM Guru99

Her har hver WHEN-setning sitt eget betingede boolske uttrykk. Hvert boolske uttrykk, dvs. Tutorial_name = 'SQL'… evalueres for TRUE/FALSE inntil det første boolske uttrykket som evalueres til TRUE.

Forskjellen mellom enkel og søkt sak

Nestet CASE: CASE i IF ELSE

Vi kan bruke CASE i en IF…ELSE-setning. Vi deklarerer en variabel for flybilletten og filialen basert på verdien. Nedenfor er et eksempel på MS-SQL-kode:

DECLARE @Flight_Ticket int;
SET @Flight_Ticket = 190;
IF @Flight_Ticket > 400
   PRINT 'Visit Nearby Tourist Location';
ELSE 
BEGIN
    SELECT
	CASE 
	WHEN @Flight_Ticket BETWEEN 0 AND 100 THEN 'Visit Los Angeles'
	WHEN @Flight_Ticket BETWEEN 101 AND 200 THEN 'Visit New York'
	WHEN @Flight_Ticket BETWEEN 201 AND 400 THEN 'Visit Europe'
	END AS Location	
END

I eksemplet ovenfor er CASE nestet i IF…ELSE-setningen. Først utføres IF-setningen, og hvis CASE-betingelsen i SQL Server er usann, utføres ELSE-setningen. ELSE-delen inneholder en nestet CASE-setning i SQL. Avhengig av flybillettens verdi vises ett av følgende resultater:

• Systemet skriver ut «Besøk turistdestinasjon i nærheten» hvis flybillettene er over $400.
• Systemet skriver ut «Besøk Los Angeles» hvis flybillettene er MELLOM $0 OG $100.
• Systemet skriver ut «Besøk New York» hvis flybillettene er MELLOM $101 OG $200.
• Systemet skriver ut «Besøk Europa» hvis flybillettene er MELLOM $201 OG $400.

Resultatet nedenfor viser plasseringen som returneres når den nestede CASE kjører inne i ELSE-grenen:

Nestet CASE: CASE inni CASE

Vi kan bruke CASE inni et annet CASE i SQL. Nedenfor er et eksempel på MS-SQL-kode:

DECLARE @Flight_Ticket int;
SET @Flight_Ticket = 250;
SELECT
CASE 
WHEN @Flight_Ticket >= 400 THEN 'Visit Nearby Tourist Location.'
WHEN @Flight_Ticket < 400 THEN 
    	CASE 
		WHEN @Flight_Ticket BETWEEN 0 AND 100 THEN 'Visit Los Angeles'
		WHEN @Flight_Ticket BETWEEN 101 AND 200 THEN 'Visit New York'
		WHEN @Flight_Ticket BETWEEN 201 AND 400 THEN 'Visit Europe'
		END	
END AS Location

I eksemplet ovenfor er CASE nestet i en annen CASE-setning. Systemet utfører den ytre CASE-setningen først; hvis Flight_Ticket < $400, kjøres den indre CASE-setningen. Den returnerte plasseringen følger de samme billettområdene som forrige eksempel, så en billett på $250 faller i området $201–$400 og returnerer 'Visit Europe'.

Resultatet nedenfor viser plasseringen som returneres av det indre CASE-et for en billett på 250 dollar:

CASE med OPPDATERING

Anta igjen at vi har 'Guru99' tabell med to kolonner og fire rader, som vist nedenfor:

Vi kan bruke CASE med UPDATE. Nedenfor er eksempelet på MS-SQL-koden:

UPDATE Guru99
SET Tutorial_Name = 
	(
	CASE
	WHEN Tutorial_Name = 'SQL' THEN 'Structured Query language.'
	WHEN Tutorial_Name = 'PL/SQL' THEN 'Oracle PL/SQL'
	WHEN Tutorial_Name = 'MSSQL' THEN 'Microsoft SQL.'
	WHEN Tutorial_Name = 'Hadoop' THEN 'Apache Hadoop.'
	END
	)

I eksemplet ovenfor brukes CASE i UPDATE-setningen. Avhengig av verdien i Tutorial_Name oppdateres kolonnen Tutorial_Name med verdien i THEN-setningen:

• Hvis Tutorial_Name = 'SQL', oppdater Tutorial_Name til 'Structured Query language'.
• Hvis Tutorial_Name = 'PL/SQL', oppdater deretter Tutorial_Name til 'Oracle PL/SQL'.
• Hvis Tutorial_Name = 'MSSQL', oppdater deretter Tutorial_Name til 'Microsoft SQL'.
• Hvis Tutorial_Name = 'Hadoop', oppdater Tutorial_Name til 'Apache Hadoop'.

Når setningen kjøres, oppdateres de samsvarende radene, som vist nedenfor:

La oss spørre etter 'Guru99'-tabellen for å sjekke de oppdaterte verdiene:

CASE med Bestill etter

Vi kan bruke CASE med ORDER BY. Nedenfor er et eksempel på MS-SQL-kode:

Declare @Order Int;
Set @Order = 1
Select * from Guru99 order by 
CASE 
	WHEN @Order = 1 THEN Tutorial_ID
	WHEN @Order = 2 THEN Tutorial_Name
	END
DESC

Her brukes CASE med ORDER BY. @Order settes til 1, og siden det første boolske WHEN-uttrykket evalueres til TRUE, velges Tutorial_ID for ORDER BY-betingelsen. Resultatet vises nedenfor:

Interessante fakta!

• CASE kan nestes i en annen CASE så vel som i en IF…ELSE-setning.
• I tillegg til SELECT kan CASE brukes med andre SQL-klausuler som UPDATE og ORDER BY.

Spørsmål og svar