Proslo je vise od jednog dana, pa evo i kompletnog resenja:
Code:
SELECT MAX(CASE WHEN z.tip = 'G'
THEN z.id
ELSE NULL
END) AS sifra_grada,
MAX(CASE WHEN z.tip = 'G'
THEN z.naziv
ELSE NULL
END) AS ime_grada,
MAX(CASE WHEN z.tip = 'R'
THEN z.id
ELSE NULL
END) AS sifra_regiona,
MAX(CASE WHEN z.tip = 'R'
THEN z.naziv
ELSE NULL
END) AS ime_regiona,
MAX(CASE WHEN z.tip = 'S'
THEN z.id
ELSE NULL
END) AS tip_smestaja,
MAX(CASE WHEN z.tip = 'S'
THEN z.naziv
ELSE NULL
END) AS ime_smestaja
FROM (SELECT 'G' AS tip, g1.id AS id, g1.naziv AS naziv, COUNT(g2.id) AS rbr
FROM gradovi AS g1
INNER JOIN
gradovi AS g2
ON g1.id >= g2.id
GROUP BY g1.id, g1.naziv
UNION ALL
SELECT 'R' AS tip, r1.id AS id, r1.naziv AS naziv, COUNT(r2.id) AS rbr
FROM regioni AS r1
INNER JOIN
regioni AS r2
ON r1.id >= r2.id
GROUP BY r1.id, r1.naziv
UNION ALL
SELECT 'S' AS tip, s1.id AS id, s1.naziv AS naziv, COUNT(s2.id) AS rbr
FROM tip_smestaja AS s1
INNER JOIN
tip_smestaja AS s2
ON s1.id >= s2.id
GROUP BY s1.id, s1.naziv
) AS z
GROUP BY z.id, z.rbr
ORDER BY z.rbr
Ako hocete nesto da naucite iz gornjeg upita pokrenite zasebno podupit:
Code:
SELECT 'G' AS tip, g1.id AS id, g1.naziv AS naziv, COUNT(g2.id) AS rbr
FROM gradovi AS g1
INNER JOIN
gradovi AS g2
ON g1.id >= g2.id
GROUP BY g1.id, g1.naziv
UNION ALL
SELECT 'R' AS tip, r1.id AS id, r1.naziv AS naziv, COUNT(r2.id) AS rbr
FROM regioni AS r1
INNER JOIN
regioni AS r2
ON r1.id >= r2.id
GROUP BY r1.id, r1.naziv
UNION ALL
SELECT 'S' AS tip, s1.id AS id, s1.naziv AS naziv, COUNT(s2.id) AS rbr
FROM tip_smestaja AS s1
INNER JOIN
tip_smestaja AS s2
ON s1.id >= s2.id
GROUP BY s1.id, s1.naziv
Kao rezultat smo dobili skup svih gradova, regiona i tipova smestaja. Ovaj skup pored osnovnih informacija sadrzi i informaciju o poreklu (G, R ili S) kao i redni broj numerisan po poreklu.
Spoljni upit grupise ovu pomocnu tabelu i razdvaja njene kolone na vise kolona (CASE deo) u zavisnosti od porekla.
MAX jednostavno uklanja pocetne NULL-ove svake kolone.
[Ovu poruku je menjao chachka dana 15.04.2007. u 10:58 GMT+1]
"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo