SQL dla zielonych i żółtych

Klauzula WITH oraz UNION ALL pozwala na uzyskanie pętli.

Wybrać coś z czegoś czy z czegoś coś. Takie rozważania swego czasu opisałem.

http ://zielonysql.blogspot.com/2015/11/wybrac-cos-z-czegos-czy-z-czegos-cos.html

Klauzula WITH oraz UNION ALL pozwala na uzyskanie pętli.

Taką pętle wykorzystałem do rozkładania ciągu znaków podzielonego średnikami na osobne kolumny. To sprawa opisana tu:

http ://zielonysql.blogspot.com/2017/02/cvs-ciag-znakow-podzielony-separatorem.html

Ale najprościej to można podejrzeć, poćwiczyć na wyprodukowaniu sobie kalendarza.

Obrazek, kwerenda i drobny opis jest tutaj.

https://drive.google.com/drive/folders/0B0DNBH1DOPAfNHJ1ajhuUm1JUWM?usp=sharing

KWERENDA:

WITH kalendarz (numer, data)

AS ( SELECT 1, convert(date, '2012-01-01')
     UNION ALL
    SELECT numer+1, dateadd(day, 1, data)
    FROM kalendarz WHERE data<'2012-01-31'
    )

SELECT * FROM kalendarz

OPTION    (maxrecursion 400) ;

-- to jest potrzebne, gdy dni chcemy dużo więcej uzupełnić niż 100,
-- bo domyślnie jest ograniczenie
-- do rekurencji 100

CVS (ciąg znaków podzielony separatorem) wciśnięty w jedną kolumnę rozkładam na kolumny na MS SQL Serwerze

Działamy na MS SQL Serwerze. Jedna kolumna, a w niej ciąg znaków, wartości rozdzielone średnikami. Ewidentnie CVS wrzucony do tabeli i tyle, ale w jedną kolumnę. I jak to rozłożyć do osobnych kolumn. Można wyeksportować do pliku, otworzyć przy pomocy EXCEL, zapisać już jako plik w formacie EXCEL i importować na Server. Jak bez EXCELA to zrobić. Chcesz się dowiedzieć jak ja to zrobiłem?
Na MS SQL Serverze nie ma tak jak na ORACLE funkcji INSTR, która pozwala namierzyć zadane/konkretne wystąpienie znaku i następie ciąć przy pomocy SUBSTRING.
Co wykorzystałem:
ROW_NUMBER () over ( order by …)
CHARINDEX (.... , …. , …)
SUBSTRING
RTRIM
I klauzule WITH i możliwość uzyskania przy jej pomocy rekurencji.
Oczywiście jeszcze jakiś WIDOK, zapisywałem dane nie na ekran ale przy pomocy klauzuli INTO do tabelki, robiłem złączenia, podzapytania… Prezentacja w całości pokazująca rozwiązanie, kod SQL to wszystko jest dostępne do przeczytania i do poćwiczenia.

https://drive.google.com/drive/folders/0B0DNBH1DOPAfbVdZckUyRF9TekU?usp=sharing

Cel: uzyskanie rekordu z np. największą wartością w skali grupy

Jak wyświetlić rekord (wszystkie atrybuty tj. zawartość wszystkich kolumn) z zestawu rekordów (czyli po prostu tabeli), który charakteryzuje się/zawiera informację dotyczącą osobnika o najwyższym wzroście.

To takie zadanko da się rozwiązać podzapytaniem, ale najprawdopodobniej wynikiem będzie jeden rekord i tyle (może być więcej, ale tylko wtedy gdy więcej niż jeden osobnik ma ten sam najwyższy wzrost).

Jak wyświetlić wszystkie rekordy (wszystkie atrybuty tj. zawartość wszystkich kolumn) z zestawu rekordów (czyli po prostu z tabeli), które jest zestawieniem np. umów leasingowych a warunki klasyfikacji/wyboru do zestawienia to: wartość przedmiotu leasingu jest maksymalna i od razu uwzględniamy rok zawarcia umowy (grupujemy po roku zawarcia umowy).

Podkreślam: od razu uwzględnić rok zawarcia umowy i by wynik od razu jedną kwerenda dostać, a nie zapuszczać kilka kwerend zmieniając rok.

Patent najbardziej uniwersalny przedstawiam na przykładzie, patent/pomysł zadziała i na MS SQL Serverze i na Oracle (na Oracle osobiście lubię parę wartości i operator IN, ale to nie działa na serwerze firmy Microsoftu).

Po kolei:

Najpierw wskazuję bazę, to poniższy zapis ustawi odpowiednią bazę:

use zielony;

Podglądam tabelę, co w niej jest, jakie kolumny:

select top 10 * from kontrakt_start;

Klauzulę: top 10 należy używać, oczywiście może być top 20 etc. To otrzymamy próbkę w tym przypadku 10 rekordów tabeli. Nie należy do podglądania pobierać całych tabel i dobry zwyczaj nakazuje jawnie pobierać próbki, a nie zdawać się na „mądrość” używanego oprogramowania. Top nie oznacza, maksymalnych wartości, to może zmylić, bo niby jakie kryterium?

Jak już widzimy to wiemy jakie mamy kolumny, to przechodzimy do rzeczy, rozwiązanie jest następujące.

select
k.*

from

kontrakt_start k -- ta tabela ma alias k

     , (select
          max(wartosc_przedmiotu) max_wartosc
         , year(data_umowy) rok_umowy
         from kontrakt_start

        group by year(data_umowy) ) m -- zapytanie generujące tabelkę pośrednią o aliasie m
   where
   k.wartosc_przedmiotu = m.max_wartosc
   and year(k.data_umowy) = m.rok_umowy;

Czyli wyciągam dane z tabeli o aliasie „k” (czyli po prostu z wykazu umów), komplet/wszystkie atrybuty (wartości w kolumnach) ale łączę z drugą tabelą o aliasie „m”.

Tabela „m” to dwa pola zawierające maksymalną wartość przedmiotu jako maks_wartosc pogrupowaną na rok , drugie pole to właśnie rok umowy pozyskany funkcją YEAR z daty umowy.

I jak złączymy „k” i „m”, po klauzuli WHERE odpowiednimi warunkami to tym zapytaniem od razu dostaniemy wykaz umów, wszystkie atrybuty, z każdego roku, z maksymalną wartością w danym roku.

I przećwiczyć można, a wszystko jest do przećwiczenia na bazie testowej ZIELONYCH :-)

I jest zadanie, zmodyfikuj tak kwerendę, by uzyskać umowy z maksymalną wartością przedmiotu leasingu na tle grupy: marki (producenta przedmiotu leasingu).

Tu szukaj bazy testowej.

A tu obrazki i kwerenda, w tym rozwiązanie zadanka.

PS. Patent, że tak powiem wypracowany samodzielnie, ale też opisany w Roz. 15 książki „Antywzorce języka SQL” jako właściwe rozwiązanie. Maczkiem o posiadaniu tej książki i jej czytaniu, bo powiem otwarcie, ze 300 stron ma książka, a jak jedną trzecią zrozumiem co autor napisał, to będzie dobrze. No nie ma co się chwalić...