[es] - Problematičan SQL upit (detalj, deo, proizv)

Hyperborejac
Kotor

Član broj: 9988
Poruke: 56
*.6.crnagora.net.

+1 Profil

Problematičan SQL upit (detalj, deo, proizv)

^{08.11.2011. u 11:01 - pre 156 meseci}

'bar dan svima.

Prolazim jedan dokument sa SQL upitima i radim primjere iz njega. Naišao sam na jedan problem koji ne znam da riješim. Daću i svoje verzije sličnog problema koje sam uspio da napišem čisto kako ne bi pomislili da sam lijen :). Hvala onima koji ovo pročitaju i potrude se. Inače mi nije hitno, ne treba mi za domaći (davno se fakultet završio), nego obnavljam SQL i prosto me nervira što ovo ne mogu da napišem.

Baza je sledeća:

Dobavljač({ID_DOBAVLJAČA, NAZIV, STATUS, GRAD}, {ID_DOBAVLJAČA})
Deo({ID_DETALJA, NAZIV, BOJA, TEŽINA, GRAD}, {ID_DETALJA})
Proizvod({ID_PROIZVODA, NAZIV, GRAD}, {ID_PROIZVODA})
SPJ({ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, KOLIČINA}, {ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA})

Dakle, tabele su Dobavljač, Deo, Proizvod i SPJ. Svi entiteti su dati u prvoj zagradi, ključevi tabela u drugoj. Problematičan zadatak glasi ovako:

--- Prikazati ID svih proizvoda koji isporučuju samo SVE one detalje koje isporučuje dobavljač DOB1. ---
Objašnjenje traženog: u tabeli SPJ imam trojku dobavljač, detalj, proizvod (količina je nebitna, pa je ne gledam). Npr.
(dob1, det1, pro1)
(dob1, det2, pro1)
(dob2, det2, pro3)
Ono što se traži je da prvo vidim koje to sve detalje isporučuje dobavljač dob1? Iz primjera iznad, vidim da:
(dob 1 isporučuje det1 i det2)
Sada se traži takav pro gdje će on imati det1 i det2. Dakle, nije rešenje ako ima samo det1, ako ima samo det2, ako ima det1, det2 i još neki det... ne, pro treba da ima (jedno ili više) pojavljivanja detalja det1 i jedno ili više pojavljivanja det2. Nadam se da sam pojasnio ono što želim da napišem.

Uzgred, dok sam pokušavao da ga riješim postavio sam par sličnih sebi i uspio da ih napišem (nadam se ispravno). Recimo:

--Prikazati sve proizvode koji isporučuju samo detalje koje isporučuje dobavljač DOB1.

Code:
SELECT DISTINCT SpjX.ID_PROIZVODA FROM Spj SpjX WHERE NOT EXISTS(
SELECT SpjY.ID_PROIZVODA FROM Spj SpjY WHERE SpjX.ID_PROIZVODA=SpjY.ID_PROIZVODA AND SpjY.ID_DETALJA NOT IN(
SELECT DISTINCT SpjZ.ID_DETALJA FROM Spj SpjZ WHERE SpjZ.ID_DOBAVLJAČA='DOB1'));

Dakle, ovo bi nam vratilo i situaciju kada za fiksirano pro imamo: samo det1; samo det2; det1 i det2.

-- Prikazati sve proizvode koji isporučuju bar SVE one detalje koje isporučuje dobavljač DOB1.

Dakle, Ovo vraća pro koji ima bar det1 i det2. Kao bonus može da ima, recimo, i det3 itd., ali manje od det1 i det2 nema sigurno.

Eto, pokrih sve varijante osim one koja mi treba. Toliko od mene i hvala na odgovoru.

Milivojev pas je siledžija. Siledžija!

Odgovor na temu

Hyperborejac
Kotor

Član broj: 9988
Poruke: 56
*.6.crnagora.net.

+1 Profil

Re: Problematičan SQL upit (detalj, deo, proizv)

^{08.11.2011. u 14:33 - pre 156 meseci}

Da odgovorim sam sebi jezivo neelegantnim rešenjem:

Code:
SELECT DISTINCT SpjX.ID_PROIZVODA FROM Spj SpjX WHERE NOT EXISTS(
    SELECT SpjY.ID_DETALJA FROM Spj SpjY WHERE SpjY.ID_DOBAVLJAČA='DOB2' AND SpjY.ID_DETALJA NOT IN(
        SELECT SpjZ.ID_DETALJA FROM Spj SpjZ WHERE SpjZ.ID_PROIZVODA=SpjX.ID_PROIZVODA)
    )
    AND NOT EXISTS(
        SELECT SpjY.ID_DETALJA FROM Spj SpjY WHERE SpjY.ID_PROIZVODA=SpjX.ID_PROIZVODA AND SpjY.ID_DETALJA NOT IN(
            SELECT SpjZ.ID_DETALJA FROM Spj SpjZ WHERE SpjZ.ID_DOBAVLJAČA='DOB2'));

Ako neko ima boju ideju, biće dobro :). Pozdrav.

Milivojev pas je siledžija. Siledžija!

Odgovor na temu

vujkev
Beograd

Član broj: 8072
Poruke: 1347
*.dynamic.isp.telekom.rs.

+104 Profil

Re: Problematičan SQL upit (detalj, deo, proizv)

^{08.11.2011. u 15:03 - pre 156 meseci}

Za početak napiši SQL skriptu za kreiranje tabela i nekih testnih podataka kako bi ljudima bilo lakše da ti pomognu.

Naučio sam...
Da je važnije biti ljubazan nego biti u pravu

Odgovor na temu

Hyperborejac
Kotor

Član broj: 9988
Poruke: 56
*.6.crnagora.net.

+1 Profil

Re: Problematičan SQL upit (detalj, deo, proizv)

^{09.11.2011. u 08:45 - pre 156 meseci}

vujkev

Citat:

Za početak napiši SQL skriptu za kreiranje tabela i nekih testnih podataka kako bi ljudima bilo lakše da ti pomognu.

Napisao sam gore strukturu baze, mislio sam da je to dovoljno. Evo svakako tabela:

-- {Dobavljač({ID_DOBAVLJAČA, NAZIV, STATUS, GRAD}, {ID_DOBAVLJAČA}

Code:
CREATE TABLE Dobavljač(
    ID_DOBAVLJAČA VARCHAR(10),
    NAZIV VARCHAR(20),
    STATUS INT UNSIGNED,
    GRAD VARCHAR(20) DEFAULT 'Kotor',
    PRIMARY KEY(ID_DOBAVLJAČA)
) CHARACTER SET cp1250;

--Deo({ID_DETALJA, NAZIV, BOJA, TEŽINA, GRAD}, {ID_DETALJA}),

Code:
CREATE TABLE Deo(
    ID_DETALJA VARCHAR(10),
    NAZIV VARCHAR(20) NOT NULL,
    BOJA VARCHAR(20) NOT NULL,
    TEŽINA FLOAT UNSIGNED,
    GRAD VARCHAR(20) DEFAULT 'Kotor',
    PRIMARY KEY (ID_DETALJA)
) CHARACTER SET cp1250;

--Proizvod({ID_PROIZVODA, NAZIV, GRAD}, {ID_PROIZVODA}),

Code:
CREATE TABLE Proizvod(
    ID_PROIZVODA VARCHAR(10),
    NAZIV VARCHAR(20),
    GRAD VARCHAR(20) DEFAULT 'Kotor',
    PRIMARY KEY (ID_PROIZVODA)
) CHARACTER SET cp1250;

-- SPJ({ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, KOLIČINA}, {ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA}) },
-- SPJ[ID_DOBAVLJAČA] 'je podskup od' Dobavljač[ID_DOBAVLJAČA ],
-- SPJ[ ID_DETALJA ] 'je podskup od' Deo[ ID_DETALJA ],
-- SPJ[ ID_PROIZVODA ] 'je podskup od' Proizvod[ ID_PROIZVODA ]}

Code:
CREATE TABLE Spj(
    ID_DOBAVLJAČA VARCHAR(10) REFERENCES Dobavljač(ID_DOBAVLJAČA),
    ID_DETALJA VARCHAR(10) REFERENCES Deo(ID_DETALJA),
    ID_PROIZVODA VARCHAR(10) REFERENCES Proizvod(ID_PROIZVODA),
    KOLIČINA INT UNSIGNED,
    PRIMARY KEY(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA)
) CHARACTER SET cp1250;

Evo i proizvoljnog unosa (sama baza je testna, zato ovakve proizvoljnosti pri unosu):

Code:
INSERT INTO Dobavljač(ID_DOBAVLJAČA, NAZIV, STATUS, GRAD) VALUES('DOB1', 'Jugopetrol',1, 'Kotor');
INSERT INTO Dobavljač(ID_DOBAVLJAČA, NAZIV, STATUS, GRAD) VALUES('DOB2', 'Mimoza',2,'Tivat');
INSERT INTO Dobavljač(ID_DOBAVLJAČA, NAZIV, STATUS, GRAD) VALUES('DOB3', 'Expo',12, 'Kotor');
INSERT INTO Dobavljač(ID_DOBAVLJAČA, NAZIV, STATUS, GRAD) VALUES('DOB4', 'Duvanski kombinat',4, 'Podgorica');
INSERT INTO Dobavljač(ID_DOBAVLJAČA, NAZIV, STATUS, GRAD) VALUES('DOB5', 'Hipotekarna banka',4, 'Podgorica');
INSERT INTO Dobavljač(ID_DOBAVLJAČA, NAZIV, STATUS, GRAD) VALUES('DOB6', 'Nekret Lima', 25, 'Novi Sad');
INSERT INTO Dobavljač(ID_DOBAVLJAČA, NAZIV, STATUS, GRAD) VALUES('DOB7', 'Dijamant',27,'Zrenjanin');
INSERT INTO Dobavljač(ID_DOBAVLJAČA, NAZIV, STATUS, GRAD) VALUES('DOB8', 'Drvoinvest',27,'Beograd');
INSERT INTO Deo(ID_DETALJA, NAZIV, BOJA, TEŽINA, GRAD) VALUES('D1','Mobilni','Plava', 4.17, 'Podgorica');
INSERT INTO Deo(ID_DETALJA, NAZIV, BOJA, TEŽINA, GRAD) VALUES('D2','Igla','Crvena', 4.17, 'Podgorica');
INSERT INTO Deo(ID_DETALJA, NAZIV, BOJA, TEŽINA, GRAD) VALUES('D3','Olovka','Crna', 1.17, 'Kotor');
INSERT INTO Deo(ID_DETALJA, NAZIV, BOJA, TEŽINA, GRAD) VALUES('D4','Tastatura','Bijela', 7.17, 'Beograd');
INSERT INTO Deo(ID_DETALJA, NAZIV, BOJA, TEŽINA, GRAD) VALUES('D5','Štampač','Crna', 17.17, 'Beograd');
INSERT INTO Deo(ID_DETALJA, NAZIV, BOJA, TEŽINA, GRAD) VALUES('D6','Štampač','Crvena', 127.17, 'Zrenjanin');
INSERT INTO Deo(ID_DETALJA, NAZIV, BOJA, TEŽINA, GRAD) VALUES('D7','Kazaljka','Crvena', 127.17, 'Zrenjanin');
INSERT INTO Deo(ID_DETALJA, NAZIV, BOJA, TEŽINA, GRAD) VALUES('D8','Monitor','Crvena', 800, 'Novi Sad');
INSERT INTO Deo(ID_DETALJA, NAZIV, BOJA, TEŽINA, GRAD) VALUES('D9','Monitor','Crvena', 700, 'Novi Sad');
INSERT INTO Deo(ID_DETALJA, NAZIV, BOJA, TEŽINA, GRAD) VALUES('D10','Šaraf','Siva', 1700, 'Kotor');
INSERT INTO Proizvod(ID_PROIZVODA, NAZIV, GRAD) VALUES('P1','Auto','Kotor');
INSERT INTO Proizvod(ID_PROIZVODA, NAZIV, GRAD) VALUES('P2','Voz','Bijelo Polje');
INSERT INTO Proizvod(ID_PROIZVODA, NAZIV, GRAD) VALUES('P3','Avion','Nikšić');
INSERT INTO Proizvod(ID_PROIZVODA, NAZIV, GRAD) VALUES('P4','Tramvaj','Beograd');
INSERT INTO Proizvod(ID_PROIZVODA, NAZIV, GRAD) VALUES('P5','Brod','Užice');
INSERT INTO Proizvod(ID_PROIZVODA, NAZIV, GRAD) VALUES('P6','Podmornica','Zrenjanin');
INSERT INTO Proizvod(ID_PROIZVODA, NAZIV, GRAD) VALUES('P7','Raketa','Novi Sad');
INSERT INTO Proizvod(ID_PROIZVODA, NAZIV, GRAD) VALUES('P8','Bušotina','Lajkovac');
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB1','D4','P5',17);
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB1','D3','P4',23);
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB2','D1','P1',107);
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB2','D4','P1', 127);
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB4','D3','P5', 109);
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB5','D3','P5', 300);
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB5','D2','P5', 700);
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB6','D8','P7', 7100);
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB7','D1','P4', 900);
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB7','D2','P4', 900);
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB7','D3','P4', 900);
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB7','D4','P4', 900);
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB7','D5','P4', 900);
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB7','D6','P4', 900);
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB7','D7','P4', 900);
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB7','D8','P7', 7100);
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB7','D9','P4', 900);
INSERT INTO Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB1','D10','P8', 900);

U prvom postu sam napisao sta mi tacno treba, a u drugom kako sam rijesio. Opet, to sigurno moze bolje - nekako mi djeluje da je elegantno rešenje bolno prosto, ali da ga ne vidim. Pozdrav i hvala.

P.S. Radim u MySQL-u, ali ne bi trebalo da je bitno. Kome ne moze da prodje ovo CHARACTER SET, neka obrise taj dodatak i obrise nasa slova (č, ć, ž...).

Milivojev pas je siledžija. Siledžija!

Odgovor na temu

Zidar
Canada

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

+79 Profil

Re: Problematičan SQL upit (detalj, deo, proizv)

^{09.11.2011. u 15:57 - pre 156 meseci}

Test podaci nisu dovoljno dobri. Ni jedan proizvod ne zadovoljava uslov "Prikazati sve proizvode koji isporučuju samo detalje koje isporučuje dobavljač DOB1"

Code:

SELECT ID_DOBAVLJAČA, ID_PROIZVODA, ID_DETALJA,  KOLIČINA
FROM  #Spj WHERE ID_DOBAVLJAČA= 'DOB1'
ORDER BY ID_DOBAVLJAČA, ID_PROIZVODA, ID_DETALJA

ID_DOBAVLJAČA ID_PROIZVODA ID_DETALJA KOLIČINA
------------- ------------ ---------- -----------
DOB1          P4           D3         23
DOB1          P5           D4         17
DOB1          P8           D10        900

(3 row(s) affected)

Ovo su proizvodi:

Code:

SELECT DISTINCT ID_PROIZVODA, ID_DETALJA
FROM #Spj
ORDER BY ID_PROIZVODA, ID_DETALJA

ID_PROIZVODA ID_DETALJA
------------ ----------
P1           D1
P1           D4

P4           D1
P4           D2
P4           D3
P4           D4
P4           D5
P4           D6
P4           D7
P4           D9

P5           D2
P5           D3

P5           D4

P7           D8

P8           D10

(15 row(s) affected)

Dobavlajc DOB1 isporucuje tri porizvoda, koji nam daju detalje (D3,D4,D10).
Uslov 1: " "Prikazati sve proizvode koji isporučuju SAMO detalje koje isporučuje dobavljač DOB1" moze da se shvati ovako: Proizvod sme da sadrzi samo detalje iz skupa (D3,D4,D10) = samo podskup skupa (D3,D4,D10), i nista vise. Iz ulaznih podataka ocigledno je da postoji samo jedan takav proizvod - P8, koji sadrzi samo D10.

Uslov "Prikazati sve proizvode koji isporučuju bar SVE one detalje koje isporučuje dobavljač DOB1." zahteva da proizvod sadrzi ceo skup (D3,D4,D10) a moze i ponesto vise. Takav uslov ne zadovoljava ni jedan proizvod, sto mozemo lalko proveriti gledajuci u tabelu Spj ili kroz select izkaz

Code:

SELECT DISTINCT ID_PROIZVODA, ID_DETALJA
FROM #Spj
ORDER BY ID_PROIZVODA, ID_DETALJA

AKo dodamo nesto novih podatak, mozemo da proverimo kveri za drugi uslov.

Code:

INSERT INTO #Proizvod(ID_PROIZVODA, NAZIV, GRAD) VALUES('P9','Bušotina','Lazarevac');
INSERT INTO #Proizvod(ID_PROIZVODA, NAZIV, GRAD) VALUES('P10','Bušotina','Mojkovac');

INSERT INTO #Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB4','D3','P9', 7100);
INSERT INTO #Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB4','D4','P9', 900);
INSERT INTO #Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB4','D10','P9', 900);

INSERT INTO #Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB4','D3','P10', 7100);
INSERT INTO #Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB4','D4','P10', 900);
INSERT INTO #Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB4','D10','P10', 900);
INSERT INTO #Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB4','D7','P10', 900);
INSERT INTO #Spj(ID_DOBAVLJAČA, ID_DETALJA, ID_PROIZVODA, Količina) VALUES('DOB4','D8','P10', 900);

Sada imamo

Code:

SELECT DISTINCT ID_PROIZVODA, LEN(ID_PROIZVODA), ID_DETALJA, LEN(ID_DETALJA)
FROM #Spj
ORDER BY LEN(ID_PROIZVODA), ID_PROIZVODA, LEN(ID_DETALJA), ID_DETALJA
;

ID_PROIZVODA             ID_DETALJA
------------ ----------- ---------- -----------
P1           2           D1         2
P1           2           D4         2

P4           2           D1         2
P4           2           D2         2
P4           2           D3         2
P4           2           D4         2
P4           2           D5         2
P4           2           D6         2
P4           2           D7         2
P4           2           D9         2

P5           2           D2         2
P5           2           D3         2
P5           2           D4         2

P7           2           D8         2

P8           2           D10        3

P9           2           D3         2
P9           2           D4         2
P9           2           D10        3

P10          3           D3         2
P10          3           D4         2
P10          3           D7         2
P10          3           D8         2
P10          3           D10        3

(23 row(s) affected)

Sadasve elemente skupa (D3,D4,D10) imamo u P9 (D3,D4,D10) i P10 (D3,D4,D7,D8,D10)

Testiramo kverijae:

Code:

--Prikazati sve proizvode koji isporučuju samo detalje koje isporučuje dobavljač DOB1.
SELECT DISTINCT SpjX.ID_PROIZVODA
FROM            #Spj SpjX
WHERE           NOT EXISTS
                ( SELECT SpjY.ID_PROIZVODA
                FROM    #Spj SpjY
                WHERE   SpjX.ID_PROIZVODA=SpjY.ID_PROIZVODA
                AND     SpjY.ID_DETALJA NOT IN
                                               ( SELECT DISTINCT SpjZ.ID_DETALJA
                                               FROM             #Spj SpjZ
                                               WHERE            SpjZ.ID_DOBAVLJAČA='DOB1'
                                               )
                )
;
ID_PROIZVODA
------------
P8
P9

(2 row(s) affected)

Tacno. P8 sadrzi D10 i nista vise a to jeste podskup od (D3,D4,D10). P9 sadrzi u sebi ceo skup (D3,D4,D10) - bas kakao je trazeno.

Code:

-- Prikazati sve proizvode koji isporučuju bar SVE one detalje koje isporučuje dobavljač DOB1.
SELECT DISTINCT SpjX.ID_PROIZVODA
FROM            #Spj SpjX
WHERE           NOT EXISTS
                ( SELECT SpjY.ID_DETALJA
                FROM    #Spj SpjY
                WHERE   SpjY.ID_DOBAVLJAČA='DOB2'
                AND     SpjY.ID_DETALJA NOT IN
                        ( SELECT SpjZ.ID_DETALJA
                        FROM    #Spj SpjZ
                        WHERE   SpjZ.ID_PROIZVODA=SpjX.ID_PROIZVODA
                        )
                )
;
ID_PROIZVODA
------------
P1
P4

(2 row(s) affected)

REzultat nije tacan. Uslov trazi da proizvod sadrzi barem ceo skup (D3,D4,D10). Ni P1 ni P4 ne zadovoljavaju taj uslov. P1 sadrzi samo D4. P4 sadrzi (D3,D4) ali ne i D10. Jedini proizvodi koji zadovoljavaju drugi uslov su dva nova, koja smo dodali, P9 i P10. P9 ssadrzi ceo skup (D3,D4,D10). i nista vise, a P10 sadri i druge detaljepored trazena tri.

Evo kako moze:

Code:

WITH DetaljiProizvoda AS
(
SELECT DISTINCT ID_PROIZVODA,  ID_DETALJA
FROM #Spj
)
, DetaljiPosmatranogDobavljaca AS
(
SELECT ID_DETALJA
FROM  #Spj WHERE ID_DOBAVLJAČA = 'DOB1'
)
SELECT
ID_PROIZVODA,   Cnt = COUNT(*)
FROM DetaljiProizvoda AS P
JOIN DetaljiPosmatranogDobavljaca AS D ON D.ID_DETALJA = P.ID_DETALJA
GROUP BY ID_PROIZVODA
HAVING COUNT(*)=3
;

ID_PROIZVODA Cnt
------------ -----------
P10          3
P9           3

(2 row(s) affected)

I prvi uslov se moze napisati na drugaciji nacin, mozda se lakse rzaume sta je napisano. Izvrsava se potpuno isto kao i kveri koji si ti napisao, isti je plan koji daje query optimizer.

Code:

-- Prikazati sve proizvode koji isporučuju SAMO detalje koje isporučuje dobavljač DOB1.
-- Kazano na drugi nacin: Proizvod ne sme da sadrzi ni jedan detalj koji ne isporucuje DOB1
-- Dovoljno je pronaci Proizvode koji sadrze neki deo koji  ne isporucuje DOB1 i onda uzeti one druge proizvide
WITH DetaljiPosmatranogDobavljaca AS
(
SELECT ID_DETALJA
FROM  #Spj WHERE ID_DOBAVLJAČA = 'DOB1'
)
, LosiProizvodi AS
(
-- Proizvodi koji sadrze detalje koje ne isporucuje DOB1:
SELECT  P.ID_PROIZVODA,  P.ID_DETALJA
FROM #Spj AS P
LEFT JOIN DetaljiPosmatranogDobavljaca AS D ON P.ID_DETALJA = D.ID_DETALJA
WHERE D.ID_DETALJA IS NULL
)
SELECT DISTINCT ID_PROIZVODA
FROM #Spj
WHERE ID_PROIZVODA NOT IN (SELECT ID_PROIZVODA FROM LosiProizvodi)
;

ID_PROIZVODA
------------
P8
P9

(2 row(s) affected)

Da li su ovi SELECT iskazi optimalni? tesko je reci na malom broju podataka. Za ovako zakukuljene uslove, za pocetak je dovoljno da se napise kveri koji pre svega vraca tacan rezultat. U realnim uslovima, sa milionima redova, ne bi me iznenadilo da se svi subkveriji prvo pretvore u temp tabele pa se onda pokupe u zavrsnom kveriju. Cesto se tako dobija na brzini, ali nema pravila, sve zavisi.

Odgovor na temu

Hyperborejac
Kotor

Član broj: 9988
Poruke: 56
*.6.crnagora.net.

+1 Profil

Re: Problematičan SQL upit (detalj, deo, proizv)

^{10.11.2011. u 09:40 - pre 156 meseci}

Svaka čast kolega zidar :). Hvala na odgovoru. Par stvari bi samo dodao:

1. Moj upit za koji si napisao 'rezultat nije tačan' je tačan. Razlog je što si u uslovu napisao DOB2 (vjerovatno si uradio samo copy/paste onog mog navedenog), a treba DOB1 (za DOB1 gledamo). Kad se to uradi, dobije se rezultat koji je i tražen. Hvala za varijantu tog upita sa with koju si poslao!

2. Da razložimo problem još jednom (na osnovu ovog inserta gore). Unosi su sledeći:
a) Dobavljač DOB1 isporučuje detalje D3, D4 i D10
b) Proizvod P8 ima samo detalj D10 - dakle on ima PRAVI podskup skupa (D3, D4 i D10)
c) Proizvod P9 ima TAČNO ova tri detalja, odnosno ima detalje (D3, D4 i D10), ni više ni manje
d) Proizvod P10 pored detalja D3, D4 i D10 ima još par detalja viška.

Koje varijante smo pokrili?
--Prikazati sve proizvode koji isporučuju samo detalje koje isporučuje dobavljač DOB1.
Smisao: ispiši šifre samo onih prozvoda čiji su detalji podskup skupa (D3, D4 i D10). Naravno, skup je sam sebi podskup. Ovo ispisuje P8 i P9, što i treba. Imamo dvije verzije upita za ovo, obije vraćaju ispravan rezultat.

-- Prikazati sve proizvode koji isporučuju bar SVE one detalje koje isporučuje dobavljač DOB2.
Smisao: ispiši šifre samo onih prozvoda čiji skup detalja u sebi ima čitav podskup (D3, D4 i D10). To nam kao rešenje daje P9 i P10, što je tačno. I tu imamo dvije verzije upita koje rade.

-- Treće (razlog otvaranja topika): Prikazati sve proizvode koji isporučuju samo SVE one detalje koje isporučuje dobavljač DOB1.
Smisao: ispiši šifre samo onih proizvoda kod kojih je DISTINCT detalja čitav skup (D3, D4 i D10). Dakle, oni ne smiju da imaju manje detalja od ova tri, ali ne smiju da imaju ni jedan detalj viška. Dakle, moraju da imaju tačno ova tri detalja.

Rezultat bi trebao da bude P9. Upit je dat u mom drugom postu na ovoj temi (i tu treba staviti DOB1!). Dakle, imamo rešenje. Ali, to je nekako bezveze - prvo gledam da li postoje, pa od ovih izdvajam koji ne postoje... Možda komplikujem situaciju bez potrebe. Kad sam otvorio temu nisam imao odgovor, da jesam ne bi je otvarao. Vjerovatno postoji neki optimalniji način, možda sa famoznim with na koji si mi skrenuo pažnju, a o kome tek sada čitam.

Još jednom - hvala na odgovoru i udubljivanju u materiju. Srdačan pozdrav.

Milivojev pas je siledžija. Siledžija!

Odgovor na temu

Zidar
Canada

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

+79 Profil

Re: Problematičan SQL upit (detalj, deo, proizv)

^{10.11.2011. u 14:58 - pre 156 meseci}

Citat:

Ali, to je nekako bezveze - prvo gledam da li postoje, pa od ovih izdvajam koji ne postoje... Možda komplikujem situaciju bez potrebe. Kad sam otvorio temu nisam imao odgovor, da jesam ne bi je otvarao. Vjerovatno postoji neki optimalniji način, možda sa famoznim with na koji si mi skrenuo pažnju, a o kome tek sada čitam.

Nist nisi uradio pogresno. Jedna od najefikasnijih metoda za resavanje teskih problema je metoda dvostruke kontradikcije. Cesto je mnogo lakse naci skup A: one redove koji ne zadovoljavaju zadati uslov (prav kontradikcija), pa onda uzeti samo one redove koji nisu u skupu A (druga kontradikcija).

WITH je lepo znati, jer se tako pisu citljiviji upiti. Umesto da napises

Code:

SELECT A.*, B.*
FROM    (SELECT x,y,z
        FROM QQQQ AS Q
        JOIN TTTT AS T ON Q.y = T.z
        WHERE x>y) AS A
JOIN    (SELECT x,p,q,r, COUNT(*)
        FROM ZZZ
        WHERE x> (SELECT COUNT(*) FROM DDD)
        GROUP BY x,p,q,r) AS B
        ON A.x = B.x

mozes da napises ovo:

Code:

; WITH SetA AS
    (-- objasnjenej za SetA
    SELECT x,y,z
    FROM   qqqq AS q
    JOIN tttt AS t ON q.y = t.z
    WHERE  x > y
    )
,  SetB AS
    (-- objasnjenje za SetB
    SELECT x,p,q,r,COUNT(*)
    FROM   zzz
    WHERE  x > (SELECT COUNT(*)
             FROM   ddd)
    GROUP  BY x,p,q,r
    )
--- objasnjejne za konacni iskaz
SELECT A.*,  B.*
FROM    SetA AS A
JOIN    SetB AS B ON A.x = A.x

Odgovor na temu