Databázy 2 - Prednáška č. 3
Created: 2009-03-08 - 20:00
Strana 1
Obsah: WITH tabuľky (a to je asi tak všetko, ostatné nám dá k dispozícii)
<![CDATA[
WITH post(k) AS
(
SELECT i = 1 --tu moze byt hocijaka premenna, alebo staci len SELECT 1
UNION ALL
SELECT j = k+1 FROM post --tu moze byt napr aj k = k+1
WHERE k<6
)
SELECT * FROM post;
WITH faktorial(k,fakt) AS
(
SELECT k = 1, fakt = 1 -- rovnako to zbehne aj s : SELECT 1,1
UNION ALL
SELECT k = k + 1, fakt = fakt * (k+1) FROM faktorial -- SELECT k + 1, fakt * (k+1) FROM faktorial
WHERE k<12
)
SELECT CAST(k AS CHAR(2))+ '! = ' + CAST (fakt AS CHAR(10)) FROM faktorial --WHERE k = 6
USE OsobaVztah
GO
WITH potomokPredok(idPot,idPred,vzdialenost) AS
(
SELECT d.id, r.id, 1
FROM Osoba d JOIN Osoba r
ON r.id IN (d.matka,d.otec)
UNION ALL
SELECT d.id, r.idPred, vzdialenost+1
FROM Osoba d JOIN potomokPredok r
ON r.idPot IN (d.matka,d.otec)
)
SELECT idPot,o1.priezvisko + o1.meno, idPred, o2.priezvisko + o2.meno FROM potomokPredok p
JOIN Osoba o1
ON o1.id = p.idPot
JOIN Osoba o2
ON o2.id = p.idPred
ORDER BY vzdialenost
]]>
