Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.

rekurzivni sql upit

[es] :: MS SQL :: rekurzivni sql upit

[ Pregleda: 4092 | Odgovora: 11 ] > FB > Twit

Postavi temu Odgovori

Autor

Pretraga teme: Traži
Markiranje Štampanje RSS

sparc
Sladjan Parc

Član broj: 65760
Poruke: 134
*.static.isp.telekom.rs.



Profil

icon rekurzivni sql upit03.02.2010. u 11:53 - pre 172 meseci
Imam dve tabele:
Table1: Table2:
Tab1ID (pk) 1 --------->∞ Tab1ID (pk)
Tab2x (pk)

Table1:
1
2
3
5
6
9

Table2:
1 2
1 3
1 4
2 5
2 6
3 7
3 8
5 9
5 10
6 11
6 12
9 13
9 14

tabele su u relaciji preko polja tab1id, pri cemu postoji ogranicenje (da bi se izbegla zatvorena petlja)
da se tab2x ne moze naci u paru sa tab1ID gde je tab2x <= tab1ID.

treba mi requrzivni upit za vrednost 1 tabele 1 koji bi dao sledecii rezultat:

1 2
1 3
1 4
1 2 2 5
1 2 2 6
1 3 3 7
1 3 3 8
1 2 2 5 5 9
1 2 2 5 5 10
1 2 2 6 6 11
1 2 2 6 6 12
1 2 2 5 5 9 9 13
1 2 2 5 5 9 9 14

sledecim sql izrazom dobijam neki rezultat
SELECT Table1.Tab1ID,Table2.Tab2x,
Table1_1.Tab1ID, Table2_1.Tab2x,
Table1_2.Tab1ID, Table2_2.Tab2x,
Table1_3.Tab1ID, Table2_3.Tab2x
FROM ((((((Table1 INNER JOIN Table2 ON Table1.Tab1ID = Table2.Tab1ID)
LEFT JOIN Table1 AS Table1_1 ON Table2.Tab2x = Table1_1.Tab1ID)
LEFT JOIN Table2 AS Table2_1 ON Table1_1.Tab1ID = Table2_1.Tab1ID)
LEFT JOIN Table1 AS Table1_2 ON Table2_1.Tab2x = Table1_2.Tab1ID)
LEFT JOIN Table2 AS Table2_2 ON Table1_2.Tab1ID = Table2_2.Tab1ID)
LEFT JOIN Table1 AS Table1_3 ON Table2_2.Tab2x = Table1_3.Tab1ID)
LEFT JOIN Table2 AS Table2_3 ON Table1_3.Tab1ID = Table2_3.Tab1ID
WHERE Table1.Tab1ID=1

rezultat koji se dobije je sledeci:

1 2 2 5 5 9 9 13
1 2 2 5 5 9 9 14
1 2 2 5 5 10
1 2 2 6 6 11
1 2 2 6 6 12
1 3 3 7
1 3 3 8
1 4

ovde postoji jedan otezavajuci moment, u sql izrazu moguce je napisati odredjeni broj JOIN-a odnosno moguce je postaviti n-ti broj rekurzija.

ima li neko ideju o rekurziji sa nepoznatim brojem iteracija, stim da je taj broj iteracija ipak konacan.
 
Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.



+79 Profil

icon Re: rekurzivni sql upit03.02.2010. u 13:44 - pre 172 meseci
Ima resenje, Common Table Expressions ako si na verzioj 2005+, ili korisnicke funkcije koje vracaju tabelu, ako si na verziji 2000.

Ne umem to da napisem napamet, strpi se nekoliko sati, daj mi vremena da pripremim odgovor i dobices resenje.

Ukoliko ti je dostupna knjiga "Inside Microsoft SQL Server 2005 T-SQL Querying", autori Itzik Ben-Gan,Lubor Kollar, Dejan Sarka; mozes tamo da nadjes resenje za tvoj problem. Dejan Sarka je mislim iz Slovenije poreklom. Knjigu izdao Microsoft Press, moZe se naci na Amazon, kosta oko U4 50.00 ili i manje.

Nije losa ni starija knjiga, "Advanced Transact-SQL for SQL Server 2000" , Itzik Ben-Gan i Tom Moreau.

 
Odgovor na temu

mmix
Miljan Mitrović
Profesorkin muz
Passau, Deutschland

SuperModerator
Član broj: 17944
Poruke: 6041



+4631 Profil

icon Re: rekurzivni sql upit03.02.2010. u 13:55 - pre 172 meseci
Jos bolja je nova, "Inside Microsoft SQL Server 2008 T-SQL Querying"
Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan, sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv… - Z.Đinđić
 
Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.



+79 Profil

icon Re: rekurzivni sql upit03.02.2010. u 19:39 - pre 172 meseci
Citat:
Jos bolja je nova, "Inside Microsoft SQL Server 2008 T-SQL Querying"

Ne sumnjam, nisam znao da je izasla knjiga. Itzik ne ponavlja uglavnom stvari iz knjige u knjigu, pa ne bih bio iznendajen da knjiga za 2008 u stvari samo produzava gde je knjiga 2005 stala.Tako je bar bilo izmedju 2000 i 2005. U svakom slucaju, hvala na informaciji.

Zakacio sam dve skripte. Prva, Hijerarhija.sql kreira tabelu s kojom se kasnije radi. Tabela sadrzi zaposlene u firmi, gde postoji relacija na samu sebe. Svaki zaposleni (employee) ima nadredjenog managera (koji je i sam employee i ima svog menaqdzera). Svi su u istoj tabeli, i postoji FK sa tabele na samu sebe, uglavnom ono sto svi manje vise znamo. Tu se za prosecnog coveka znanje o hijerarhijama zavrsava - kako ih pamtimo u tabeli.

Itzik u knjizi objasnjava kako se pisu kveriji koji odgovaraju na pitanja o hijerarhijama. To se posytize pomocu funkcija. Lepa stvar je sto cak i ako se ne razume sve (a ako nemas knjigu, tesko ces razumeti), funkcije mogu da se iskoriste i preprave da sluze nasim konkretnim prilikama. Funkcije i SQL izrazi koji ih ilustruju su prilicno lepo objasnjeni komentarima, pa s enadam da ce ti pomoci.

U svakom slucaju, ja imam knjigu, pa ako zapnes, pitaj, a ja cu da pogledam u knjigu

Kod mene na production serveru rde dve funkcije koje sam prepisao od Itzika, kad ih nisam ni malo razumeo. Kasnije sam uspeo da prevedem nesto i u Access, znaci nesto sam kao razumeo. pomioci cemo koliko mozemo.


Prva skripta, Hijerarhija.sql , ima tri funkcije:

1) dbo.fn_subordinates1 - nija bas ono sto ti treba, ali je jako vazna za razumevanje pretrazivanja hijerarhije. Funkcija pokazuje svu decu izabranog roditelja.

2) dbo.fn_managers - inverzan zadatak, zadato je dete, pokazuje sve pretke

3) dbo.fn_subordinates3 - ono sto tebi treba. funkcija pokazuje svu decu izbranog roditelja u formatu 'jedn roditelj jedan red, deca kao comma delimited string u istom redu'

Za sve funkcije dati su i kveriji koji ilustruju sta se desava. Funkcije rade u verzijai 2000 i novijim. Uz svaku funkciju dato je i resenje sa rekurzivnim CTE. Ako imas iskustva sa CTE i rekurzijama, mozes da ih pretvoris u funkcije, krace su nego klasicne verzije. Klasicne verzije su medjutim lakse za razumevanje.

Druga skripta, Hijerarhija_Sorting.sql, sadrzi funkciju koja se bavi sortiranjem izlaza. Funkcija dbo.fn_subordinates1 vraca svu decu, ali se ne garantuje redosled medju decom. Na primer, ako roditelj broj 1 ima decu ciji su brojevi 2,3,5 deca se mogu pojaviti na izlazu u redosledu 2,2,5 ili bilo kom (i pojavljuju se) i nemoguce ih je sortirati. Ako se zeli sortirani izlaz, onda Itzik daje stored proceduru umesto funkcije, koja vraca sortirani set. Zanimljivo, a moze biti i korisno.

Srecan rad



Prikačeni fajlovi
 
Odgovor na temu

mmix
Miljan Mitrović
Profesorkin muz
Passau, Deutschland

SuperModerator
Član broj: 17944
Poruke: 6041



+4631 Profil

icon Re: rekurzivni sql upit03.02.2010. u 20:23 - pre 172 meseci
Citat:
Zidar: Ne sumnjam, nisam znao da je izasla knjiga. Itzik ne ponavlja uglavnom stvari iz knjige u knjigu, pa ne bih bio iznendajen da knjiga za 2008 u stvari samo produzava gde je knjiga 2005 stala.Tako je bar bilo izmedju 2000 i 2005. U svakom slucaju, hvala na informaciji.

Pa ista prica i sad, dodate novotarije iz SQL2008. Ako je trazis, sad ti je vreme za nabavku, kad je izasla drzali su bas jaku cenu a sad su pojeftinile za 60, 70% pred novu godinu a kako se gde snadjes... Izvinjavam se za mali off.
Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan, sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv… - Z.Đinđić
 
Odgovor na temu

HladankaoLed
Database Consultant
Vienna, Austria

Član broj: 228937
Poruke: 36
*.wk.or.at.

Sajt: milossql.wordpress.com


+1 Profil

icon Re: rekurzivni sql upit04.02.2010. u 11:18 - pre 172 meseci
Citat:
mmix: Pa ista prica i sad, dodate novotarije iz SQL2008. Ako je trazis, sad ti je vreme za nabavku, kad je izasla drzali su bas jaku cenu a sad su pojeftinile za 60, 70% pred novu godinu a kako se gde snadjes... Izvinjavam se za mali off.


Da mu ga damo jos malo po off-topicu...:) Pored SQL 2008 features u novoj knjizi ima i prilicno matematike. Naime, dodata su i poglavlja o skupovima i predikatskoj logici, relacionoj algrebri i slozenosti algoritama. Takodje Lubor Kollar je napisao poglavlje o upitima nad particionisanim tabelama.

Za one koji sebe ne smatraju naprednim developerima, a voleli bi da to postanu, odlican izbor predstavlja takodje Itzik-ova knjiga "Inside Microsoft SQL Server 2008: T-SQL Programming".

Pozz,
M.


Sad šta je tu je. A možda će da dođe i ova tvoja tetka iz Bosanske Krupe.
 
Odgovor na temu

sparc
Sladjan Parc

Član broj: 65760
Poruke: 134
*.static.isp.telekom.rs.



Profil

icon Re: rekurzivni sql upit04.02.2010. u 11:27 - pre 172 meseci
Ok,
veliko vam hvala na odgovorima i primerima,
sve knjige mogu se naci u pdf, ako nekome trebaju .................

odgovori su dosta dosta jasni i postoji nekoliko resenja

1. preko temp tabele
2. preko rekurzivnih poziva funkcije
3. preko CTE

ali ipak postoji ogranicenje na 32-e iteracije, u realnom svetu verovatno i sasvim dovoljno.

Hvala na pomoci.
 
Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.



+79 Profil

icon Re: rekurzivni sql upit04.02.2010. u 15:20 - pre 172 meseci
Citat:
sve knjige mogu se naci u pdf, ako nekome trebaju .................
Gde?

Ne razumem ovo:
Citat:
ali ipak postoji ogranicenje na 32-e iteracije, u realnom svetu verovatno i sasvim dovoljno.

U CTE mozes da postavis MAXRECURSION = 0 i onda nemas ogranicenje. Resenja sa CTE funkcijama uzmu ulaznu tabelu i vrete se koilko puta treba i na kraju izbace data set. Resenja sa UDF koje vracaju tabelu isto nemaju ogranicenja - vrte se dok ima podataka. Ispravi me ako gresim. Pokusacu da napravim primer sa vise od 32 nivoa hijerarhije, da proverimo.



 
Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.



+79 Profil

icon Re: rekurzivni sql upit04.02.2010. u 21:09 - pre 172 meseci
Evo demonstracija da broj nivoa nije ogranice.

prvo, rekreiramo tabelu Employees i unesemo 50 employees, svaki je poslovodja onom sledecem. Unesmo 50 radnika, to nam daje 50 nivoa hijerarhije.
Code:

SET NOCOUNT ON;
GO
IF OBJECT_ID('dbo.Employees') IS NOT NULL
  DROP TABLE dbo.Employees;
GO
CREATE TABLE dbo.Employees
(
  empid   INT         NOT NULL PRIMARY KEY,
  mgrid   INT         NULL     REFERENCES dbo.Employees,
  empname VARCHAR(25) NOT NULL,
  salary  MONEY       NOT NULL,
  CHECK (empid <> mgrid)
);

-- evo 50 radnika, svaki je posovodja onom ispod sebe => 50 nivoa hijerarhije
INSERT INTO dbo.Employees
SELECT 
    EmpId = num
    , MgrID = CASE WHEN num = 1 
                    THEN NULL 
                    ELSE num -1 
                END
    , EmpName = 'Employee ' + CAST(num AS varchar)
    , Salary = 100 - num + 1
FROM Numbers
WHERE num BETWEEN 1 AND 50
;


Pozivamo funkcije koje vec imamo:
Code:
SELECT empid, lvl, path
FROM dbo.fn_subordinates3(1, NULL) AS S;


Svaki red pokazuej radnika i sve nejgove rukovodioce.
Deo slike koja se dobije izgleda ovako:
Code:

empid    lvl    path
1    0    .1.
2    1    .1.2.
3    2    .1.2.3.
4    3    .1.2.3.4.
5    4    .1.2.3.4.5.
6    5    .1.2.3.4.5.6.

Cela slika ide do nivoa 50, sto je vece od 32 :-)

Slika je mozda lepsa ovako:
Code:

empid    empname
1    Employee 1
2     | Employee 2
3     |  | Employee 3
4     |  |  | Employee 4
5     |  |  |  | Employee 5
6     |  |  |  |  | Employee 6
7     |  |  |  |  |  | Employee 7
8     |  |  |  |  |  |  | Employee 8
9     |  |  |  |  |  |  |  | Employee 9


To smo dobili ovim kverijam (ima ga u originalnoj skripti):

Code:

GO
DECLARE @root AS INT;
SET @root = 1;

; WITH SubsCTE
AS
(
  SELECT empid, empname, 0 AS lvl,
    -- Path of root = '.' + empid + '.'
    CAST('.' + CAST(empid AS VARCHAR(10)) + '.'
         AS VARCHAR(MAX)) AS path
  FROM dbo.Employees
  WHERE empid = @root

  UNION ALL

  SELECT C.empid, C.empname, P.lvl + 1,
    -- Path of child = parent's path + child empid + '.'
    CAST(P.path + CAST(C.empid AS VARCHAR(10)) + '.'
         AS VARCHAR(MAX)) AS path
  FROM SubsCTE AS P
    JOIN dbo.Employees AS C
      ON C.mgrid = P.empid
)
SELECT empid, REPLICATE(' | ', lvl) + empname AS empname
FROM SubsCTE
ORDER BY path;

GO


Znaci, nema ogranicenja po nivoima. Barem do 50, u praksi je 7-8 verovatno i previse.
:-)
 
Odgovor na temu

sparc
Sladjan Parc

Član broj: 65760
Poruke: 134
*.static.isp.telekom.rs.



Profil

icon Re: rekurzivni sql upit05.02.2010. u 07:06 - pre 172 meseci
Knjige sam skinuo, ja ih sada imam i mogu da ih posaljem mailom, linkove normalno
kao i svaki drugi IT nisam sacuvao, a da ih stavljamo negde mogu nas tuziti
za pirateriju. Sve zahteve za knjigama saljite e-mail na koji da ih dostavim.
 
Odgovor na temu

mmix
Miljan Mitrović
Profesorkin muz
Passau, Deutschland

SuperModerator
Član broj: 17944
Poruke: 6041



+4631 Profil

icon Re: rekurzivni sql upit05.02.2010. u 09:12 - pre 172 meseci
Sto umesto svih tih maltretiranja ne koristite jednostavno hierarchyid tip? Ima malo vise posla pri insertu (ali ni to ako se odradi kako treba) a implicitno dobijate hijerarhiju i ispomoc pri pretrazivanju iste..

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan, sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv… - Z.Đinđić
 
Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.



+79 Profil

icon Re: rekurzivni sql upit05.02.2010. u 14:34 - pre 172 meseci
Citat:
Sto umesto svih tih maltretiranja ne koristite jednostavno hierarchyid tip

Tacno, medjutim sve zavisi od verzije SQL servera. 2008 je prva MS SQL verzija koja ima hierarchyid tip. Ja se nadam da se sve ove stvari zaista mogu raditi sa hierarchyid tip i da ne moramo da cekamo verziju 2010...

neam 2008 pa ne mogu nista da kazem :-(

 
Odgovor na temu

[es] :: MS SQL :: rekurzivni sql upit

[ Pregleda: 4092 | Odgovora: 11 ] > FB > Twit

Postavi temu Odgovori

Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.