În acest articol, vă voi spune despre operatorul CASE condiționat, fără de care contabilitatea datelor într-o serie de sarcini s-ar transforma în grămezi de bucăți de cod, precum și despre cum să o utilizați în interogările SQL.
Indiferent cât de bine ai proiecta baza de date, vor exista întotdeauna probleme în care declarațiile condiționate sunt indispensabile. De exemplu, în loc de numere uriașe, obțineți o descriere verbală sau, în funcție de prezența (absența) datelor, adăugați caractere suplimentare la rând sau, ca exemplu mai complex, în funcție de diferite nuanțe, atribuiți o înregistrare unuia sau alt grup. Sunt o mulțime de situații.
În principiu, un anumit operator are cel puțin două forme în baze de date diferite. Prima opțiune seamănă cu o comutare obișnuită de la orice limbaj de programare. Al doilea depinde de expresii logice.
Cu toate acestea, în sensul acestui articol, voi lua în considerare a doua opțiune, deoarece nu are probleme cu situații precum CASE WHEN NULL (null în baza de date nu este o valoare specifică, deci nu poate fi utilizat într-o declarație de tip comutator). ). În plus, în viața de zi cu zi, sarcinile apar cel mai adesea special pentru a doua opțiune - calculul prin expresii logice. Prin urmare, este mai bine să învățați imediat și să continuați să îl utilizați.
De obicei, este descris astfel (sintaxa poate varia în funcție de baza de date):
CAZUL CÂND bool_expression1 THEN value1 ..... WHEN bool_expressionN THEN valueN ELSE valueElse END
bool_expressionX este o condiție booleană
valueX este valoarea care va fi înlocuită dacă este îndeplinită condiția logică corespunzătoare
valueElse este valoarea care va fi înlocuită dacă nu a fost îndeplinită anterior nicio condiție.
După aceste mici informații, să trecem la exersare.
Nota: Apropo, merită să știți că, de obicei, acest operator poate fi folosit nu numai în select, ci și în orice loc unde pot fi utilizate câmpuri. De exemplu, atunci când se unesc tabele sau chiar se filtrează (având) când se grupează (grupează după).
Pentru a înțelege mai bine declarația condițională CAZ... CÂND... ATUNCI, să ne imaginăm o mică problemă. Să presupunem că aveți un tabel cu date despre clienți și numărul total de achiziții ale acestora. Și sarcina este de a crea în mod dinamic o reducere. Ar fi posibil, desigur, setarea manuală a reducerii. Dar, aveți un prag, iar pragurile sunt conectate (ceva de genul: o sumă mai mare de 1000 va primi o reducere de 2%, iar mai mult de 5000 vor primi un 5%) și ați dori să automatizați acest proces, astfel încât să nu Nu trebuie să căutați erori de fiecare dată când intrați în baza de date (clientul a acumulat suma necesară - automat apare reducerea).
Să luăm un tabel de clienți condiționat cu trei clienți. De exemplu, acestea vor fi destul de suficiente.
Acum, să setăm mai multe condiții pentru furnizarea automată a unei reduceri în funcție de sarcină. În același timp, credem că clientului i se acordă în orice caz reducerea maximă.
1. Suma de la 1000 - 2% reducere
2. Suma de la 5000 - 5% reducere
3. Suma de la 10.000 - 8% reducere
4. Numar de comenzi de la 10 - 7% reducere
5. Numar de comenzi de la 20 - 8% reducere
După cum puteți vedea, reducerea depinde de doi factori: cantitatea și cantitatea. Acum, să încercăm să creăm condiții din ele pe baza reducerii, adică regulile sunt inverse, astfel încât să poată fi folosite într-o interogare sql. Obținem următoarele:
1. 2% - Sumă de la 1000 la 4999 și numărul de comenzi este mai mic de 10.
2. 5% - Sumă de la 5000 la 9999 și numărul de comenzi este mai mic de 10.
3. 7% - Număr de comenzi de la 10 la 19 și sumă mai mică de 10.000
4. 8% - Cantitate de la 20 sau suma de la 10.000
Acum nu mai rămâne decât să-l notăm. Să obținem următoarea interogare SQL
Afișăm numele și alte date select name, order_count, total_sum, -- Și acum afișăm reducerea CASE -- Prima regulă 2% WHEN c.total_sum >= 1000 și c.total_sum<= 4999 and c.order_count < 10 THEN 2 -- Второе правило 5% WHEN c.total_sum >= 5000 și c.total_sum<= 9999 and c.order_count < 10 THEN 5 -- Третье правило 7% WHEN c.total_sum < 10000 and c.order_count >= 10 și c.order_count<= 19 THEN 5 -- Четвертое правило 8% WHEN c.total_sum >= 10000 sau c.order_count >= 20 THEN 5 -- Nu sunt îndeplinite reguli, ceea ce înseamnă că reducerea este 0. ELSE 0 END ca reducere de la client c
În urma execuției obținem următorul tabel:
După cum puteți vedea, doi clienți au primit o reducere de 8% și un client a primit o reducere de 2%. În acest caz, cu fiecare comandă procentul va fi calculat automat și nu va trebui să ajustați nimic. De exemplu, dacă suma lui Petya crește la 5000, atunci reducerea sa va crește automat la 5% (cel puțin, deoarece există încă un număr de comenzi).
Este dificil de explicat sintaxa clauzei SQL Server WHERE, așa că să ne uităm la câteva exemple.
Vom începe prin a vedea cum să folosim clauza WHERE cu o singură condiție.
SELECT * FROM angajati WHERE prenume = "Jane";
În acest exemplu de clauză WHERE pentru SQL Server, am folosit clauza WHERE pentru a filtra rezultatele noastre din angajati masă. Instrucțiunea SELECT de mai sus va returna toate rândurile din angajati masa unde prenume este „Jane”. Deoarece * este folosit în SELECT, toate câmpurile din angajati tabelul ar apărea în setul de rezultate.
Să vedem cum să folosiți clauza WHERE cu condiția AND.
SELECT * FROM angajati WHERE last_name = "Anderson" AND employee_id >= 3000;
Acest exemplu de clauză WHERE SQL Server utilizează clauza WHERE pentru a defini mai multe condiții. În acest caz, această instrucțiune SELECT folosește condiția AND pentru a returna toate angajati care au o nume lui „Anderson” și ID-ul angajatului este mai mare sau egal cu 3000.
Să vedem cum să folosiți clauza WHERE cu condiția OR.
SELECTAȚI ID-ul angajatului, numele de familie, prenumele FROM angajații WHERE nume de familie = "Johnson" SAU prenume = "Danielle";
Acest exemplu de clauză WHERE SQL Server utilizează clauza WHERE pentru a defini mai multe condiții, dar în loc să folosească condiția AND , folosește condiția OR . În acest caz, această instrucțiune SELECT ar returna toate ID-ul angajatului, nume, și prenume valorile din angajati masa unde nume este „Johnson” sau prenume este „Danielle”.
Să ne uităm la cum să folosim clauza WHERE atunci când combinăm condițiile AND & OR într-o singură instrucțiune SQL.
SELECTAȚI * FROM angajații WHERE (stat = „California” AND last_name = „Smith”) SAU (employee_id = 82);
Acest exemplu de clauză WHERE SQL Server utilizează clauza WHERE pentru a defini mai multe condiții, dar combină condiția AND și condiția SAU. Acest exemplu ar returna totul angajati care rezidă în stat al „Californiei” şi al cărui nume este „Smith” precum şi toţi angajaţii ai căror ID-ul angajatului este egal cu 82.
Parantezele determină ordinea în care sunt evaluate condițiile AND și SAU. Așa cum ați învățat în ordinea operațiilor la ora de matematică!
Să ne uităm la cum să folosim clauza WHERE atunci când unim mai multe tabele împreună.
SELECTE employees.employee_id, contacts.last_name FROM angajati INNER JOIN contacts ON employees.employee_id = contacts.contact_id WHERE employees.first_name = "Sarah";
Acest exemplu de clauză WHERE SQL Server utilizează clauza WHERE pentru a uni mai multe tabele într-o singură instrucțiune SELECT. Această instrucțiune SELECT va returna toate rândurile în care prenumeîn angajati masa este „Sarah”. Iar cel angajat s și contacte mesele sunt unite pe ID-ul angajatului din angajati masa si contact_id din contacte masă.
Implementările limbajului SQL pot efectua conversie implicită de tip. Deci, de exemplu, în T-SQL atunci când se compară sau se combină valori de tipuri smallintŞi int, tip de date smallint sunt implicit turnate la tip int. Puteți citi mai multe despre conversia de tip explicită și implicită în MS SQL Server în BOL.
Exemplu.
Tipăriți prețul mediu al notepad-urilor pentru computer cu textul precedent „preț mediu =”.
Încercarea de a finaliza o solicitare
Aceste. Am folosit o conversie implicită a argumentului întreg într-un tip numeric exact (NUMERIC EXACT), înmulțind-o cu o unitate reală și apoi am aplicat o distribuție explicită tipului de rezultat al funcției de agregare.
Conversii de tip similar pot fi efectuate folosind funcția CONVERTIT:
Să presupunem că doriți să afișați o listă cu toate modelele de PC-uri indicând prețurile acestora. În plus, dacă modelul nu este la vânzare (nu în tabelul RS), atunci în loc de preț, afișați textul: „Stoc epuizat”.
O listă cu toate modelele de PC cu prețuri poate fi obținută folosind cererea:
SELECTARE DISTINCT produs.model, pret FROM produs LEFT JOIN pc c ON product.model=c.model WHERE product.type="pc"; |
model | preţ |
1121 | 850 |
1232 | 350 |
1232 | 400 |
1232 | 600 |
1233 | 600 |
1233 | 950 |
1233 | 980 |
1260 | 350 |
2111 | NUL |
2112 | NUL |
model | preţ |
1121 | 850 |
1232 | 350 |
1232 | 400 |
1232 | 600 |
1233 | 600 |
1233 | 950 |
1233 | 980 |
1260 | 350 |
2111 | Nu este disponibil |
2112 | Nu este disponibil |
Operator CAZ poate fi folosit într-una din cele două forme de notație sintactică:
forma I
CAZ<проверяемое выражение>
CÂND<сравниваемое выражение 1>
APOI<возвращаемое значение 1>
…
CÂND<сравниваемое выражение N>
APOI<возвращаемое значение N>
Sfârşit
forma a II-a
CAZ
CÂND<предикат 1>
APOI<возвращаемое значение 1>
…
CÂND<предикат N>
APOI<возвращаемое значение N>
Sfârşit
Toate ofertele CÂND trebuie să aibă aceeași formă sintactică, adică Nu puteți amesteca prima și a doua formă. CÂND Când se folosește prima formă sintactică, condiția mulțumit de îndată ce valoarea
expresia fiind testată CÂND va deveni egală cu valoarea expresiei specificate în propoziție CÂND. Când se folosește a doua formă sintactică, condiția mulțumit de îndată ce
predicat CAZ evaluează la TRUE. Dacă condiția este îndeplinită, operatorul returnează valoarea specificată în clauza corespunzătoare CÂND APOI ALTE. ALTE Dacă nici una dintre condiţii Dacă condiția este îndeplinită, operatorul a eșuat, se va folosi valoarea specificată în clauză
. CAZ.
In lipsa , va fi returnată o valoare NULL. Dacă sunt îndeplinite mai multe condiții, valoarea propoziției va fi returnată primul dintre ei. CAZ:
În exemplul de mai sus, a fost folosită a doua formă a operatorului
Rețineți că pentru a verifica NULL, standardul oferă o formă mai scurtă a operatorului - , va fi returnată o valoare NULL. Dacă sunt îndeplinite mai multe condiții, valoarea propoziției va fi returnată COALESCE
. Acest operator are un număr arbitrar de parametri și returnează prima valoare non-NULL. Pentru doi parametri, operatorul COALESCE(A, B) este echivalent cu următorul operator CAZ CAZUL CÂND A NU ESTE NUL APOI A ELSE B END Soluția pentru exemplul de mai sus folosind operatorul poate fi rescris astfel:
Folosind prima formă sintactică a operatorului poate fi demonstrată în cele ce urmează exemplu : Afișează toate modelele de PC disponibile cu prețuri. Marcați cele mai scumpe și mai ieftine modele. SELECTARE DISTINCT model, preț, |
Ca urmare a executării cererii, obținem
model | preţ | comentariu |
1232 | 350 | Cel mai ieftin |
1260 | 350 | Cel mai ieftin |
1232 | 400 | Pret mediu |
1233 | 400 | Pret mediu |
1233 | 600 | Pret mediu |
1121 | 850 | Pret mediu |
1233 | 950 | Pret mediu |
1233 | 980 | Cel mai scump |
SQL este considerat unul dintre cele mai comune limbaje de programare pentru lucrul cu baze de date. Construcțiile de limbaj vă permit nu numai să creați o bază de date, ci și să efectuați diverse manipulări cu aceasta pentru a modifica datele sau a le selecta.
Pentru a selecta date din baza de date, utilizați constructul Selectați [set de date] din [numele tabelului]. Experiența arată că, în 80% din cazuri, atunci când utilizați interogări pentru selectarea datelor, trebuie să utilizați diferite condiții - parametri. Pentru a face acest lucru, condiția SQL-Where a fost introdusă în limbaj ca o completare la interogare, pentru a o complica.
Destul de des, programatorul trebuie să selecteze, în principal pentru raportare, datele stocate în baza de date. Pentru a face acest lucru, este posibil să nu fie suficient să construiți o interogare simplă de selectare. De regulă, este necesar să se țină seama și de diferite condiții, parametri de eșantionare, dintre care pot fi destul de mulți, sau să le verifice. dacă datele se încadrează într-un interval definit sau într-un anumit tabel.
Construcția SQL-Where poate fi folosită pentru a seta condiții pentru selectarea datelor sau pentru a verifica dacă datele sunt incluse într-un tabel select sau străin.
Dacă trebuie să specificați anumiți parametri pentru selectarea dintr-o bază de date de raportare, sintaxa constructului SQL-Where poate fi organizată destul de simplu. Pentru a face acest lucru, puteți folosi următoarele reguli:
Trebuie să construiți o interogare standard folosind constructul Select * din.
Folosind constructul cheie Join, determinați din ce tabele se va face selecția.
Folosind constructul Where, specificați o listă de parametri pentru selecție.
Aceste tipuri de interogări sunt destul de simplu de construit și nu provoacă dificultăți chiar și pentru începători.
Dacă programatorul are sarcina nu numai să selecteze datele dintr-un tabel pe baza unei condiții, ci și să verifice includerea acestora în unul sau mai multe tabele ale altui plan, constructul SQL-Where va fi indispensabil.
Folosind sintaxa acestui construct, puteți construi aceleași condiții, dar cu interogări imbricate care vor verifica dacă rândurile selectate sunt incluse într-un set de tabele de baze de date terțe.
De regulă, în astfel de scopuri, se formează un tabel temporar în care se înregistrează întregul set de date necesare verificării apariției.
Acum vor fi date exemple Pentru început, să ne imaginăm că există două tabele cu date - Tovar și TovarAmount. Prima conține numele mărfurilor vândute, prețul, data vânzării și clientul care a achiziționat bunurile. Al doilea indică disponibilitatea produsului, sau mai precis, care este în stoc.
Un exemplu de solicitare cu un parametru care va indica toate produsele vândute într-un anumit număr de zile ar fi următoarea construcție:
Selectați * din Produs
Unde T_Date >= „12/01/2016” și T_Date<= ‘’12/07/1016 and T_PriceOut >0
O interogare de acest tip va returna o listă de produse, date din tabel, care au fost vândute în primele șapte zile ale lunii decembrie, așa cum este indicat de una dintre condițiile de selecție: T_PriceOut >0.
Dacă luăm în considerare condiția retragerii mărfurilor care sunt în stoc, atunci designul va fi după cum urmează:
Selectați * din Produs
Unde T_Tovar în (selectați TA_Tovar unde TA_ Suma >0)
Pot exista multe condiții imbricate în Where, dar merită menționat că cu cât sunt impuse mai multe condiții, cu atât interogarea va dura mai mult pentru a rula. Acesta este tocmai motivul utilizării tabelelor temporare. Este mult mai rapid să creați mai multe dintre acestea și apoi să comparați datele din ele decât să construiți o condiție cu mai multe niveluri de verificare a datelor.
Atunci când selectăm înregistrări dintr-un tabel, este aproape întotdeauna necesar să stabilim anumite condiții prin care stabilim de ce înregistrări avem nevoie și de care nu. Și aceste condiții pot fi setate folosind clauzele WHERE în SQL. V-am prezentat deja, dar în acest articol am decis să vă prezint UNDE putin mai aproape.
Voi începe cu un exemplu simplu de eșantionare folosind clauzele WHERE în SQL:
SELECT * FROM tabelul WHERE count=5
Vor fi returnate înregistrările în care câmpul „ conta"contează 5 . Acum să complicăm interogarea:
SELECT * FROM tabelul WHERE count=5 AND id< 100
Astfel, vor fi returnate înregistrările al căror câmp „ conta"contează 5 ŞI câmp " id„contează mai puțin 100 .
Desigur, puteți folosi și alte operațiuni logice. Lista lor completă:
Exemplu de utilizare a mai multor operatori logici:
SELECTAȚI * DIN tabelul WHERE !(id<= 120 AND (count=10 OR date > "10/11/1980"))
Acest lucru este atât de complicat la prima vedere interogare SQL. Încercați să vă dați seama singur.
Asemenea Clauza WHERE în SQL poate contine CA. CA vă permite să determinați dacă un șir specificat se potrivește cu un anumit model. Pentru a fi un pic mai clar, voi da un exemplu:
SELECTAȚI * FROM tabelul WHERE text LIKE „%some text%”
Dat interogare SQL va reveni set_rezultat care conțin înregistrări în care câmpul „ text" are următorul text: " ceva text". Rețineți că acesta nu este un test de egalitate. Textul poate fi uriaș, dar dacă conține linia: " ceva text", Asta CA va reveni adevărat.
Lasă-mă să scriu cum să setați modelul pentru LIKE:
Cunoștințe și capacitate de utilizare CA foarte important, crede-mi experiența. Cel mai simplu exemplu de utilizare a LIKE- Asta căutare pe site. La urma urmei, conținutul se află în baza de date și trebuie să scoateți numai acele înregistrări care conțin șirul specificat în șirul de căutare. Și apoi vine la salvare CA. Exact așa am implementat căutarea mea pe acest site.