Jeden zapeklity SQL dotaz

Radim Kubacki radim.kubacki na rtscs.cz
Čtvrtek Březen 30 17:12:56 CEST 2000


>Radim Kubacki wrote:
>>
>> Zeby takhle?
>>
>> select Osnova.IDUcet,
>>     sum(MD.Cena)/count(distinct DAL.rowid) as MDSuma,
>>     sum(DAL.Cena)/count(distinct MD.rowid) as DalSuma
>> from Osnova
>>     left outer join PDenik MD on Osnova.IDUcet=MD.MDUcet
>>     left outer join PDenik DAL on Osnova.IDUcet=Dal.DalUcet
>> group by Osnova.IDUcet
>> Prave proto, aby se to nezapocitavalo vicekrat. Misto rowid by sel
>pouzit i
>> nejaky klic z PDenik.
>
>Mozna ano, ale kdyz dojde k deleni nulou, neni ve vysledku vubec radek s
>IDUcet=311. Ale zkusim zkombinovat s uniony.
>

Normalne je nato treba v Oraclu funkce NVL(expr1,expr2), ktera vrati prvni
vyraz, pokud neni NULL, jinak druhy. Ale v danem pripade by mohlo byt taky
ABS(expr-0.5)+0.5 a nula je nahle jednickou a kladna prirozena cisla
zustavaji nezmenena, absolutni hodnota je snad vsude. Uniony jsou dost draha
operace.

>> Holt SQL ma spoustu much, ktere muze clovek kritizovat. Treba by si to
>> clovek predstavoval takto:
>>
>> select *
>> from
>>     (select Osnova.IDUcet, sum(MD.Cena) as MDSuma
>>     from Osnova
>>     left outer join PDenik MD on Osnova.IDUcet=MD.MDUcet
>>     group by Osnova.IDUcet) TMD
>>     outer join
>>     (select Osnova.IDUcet, sum(MD.Cena) as MDSuma
>>     from Osnova
>>     left outer join PDenik MD on Osnova.IDUcet=MD.MDUcet
>>     group by Osnova.IDUcet) TDAL on TMD.IDUcet = TDAL.IDUcet
>>
>
>To by po uprave slo, ale LocalSQL v BDE od Borlandu nezvlada
>slozeny SELECT. Dik za snahu.
>
Neni jediny. Jinak jeden z tech outer joinu muze byt normalni join a
vysledky zustanou stejne. Vubec, relacni algebra ma nekdy mnohem peknejsi
zapisy ;)

Radim



Další informace o konferenci Test