stromova struktura va databazi
nevite nekdo o databazi, co sama umi razeni dat do stromovy struktury?
ted je to reseny tak, ze kazdej zaznam odkazuje na nadrazenou polozku (jedna tabulka, protoze cela struktura ma variabilni hloubku ktera se navic meni ;-)) a kdyz potrebuju precist polozku + vsechny co jsou 'nad ni' (coz potrebuju prakticky porad), tak musim pouzit vic selectu...
imho by to slo udelat pres subselecty (mysql/4.1++), ale to bych zase musel znat presnou hloubku...
mno a nebo stored procedures, coz mam taky smulu ;-)
ted je to reseny tak, ze kazdej zaznam odkazuje na nadrazenou polozku (jedna tabulka, protoze cela struktura ma variabilni hloubku ktera se navic meni ;-)) a kdyz potrebuju precist polozku + vsechny co jsou 'nad ni' (coz potrebuju prakticky porad), tak musim pouzit vic selectu...
imho by to slo udelat pres subselecty (mysql/4.1++), ale to bych zase musel znat presnou hloubku...
mno a nebo stored procedures, coz mam taky smulu ;-)
<HTML>no rekurze zrovna neni vec, ktera by si s sql tykala ;-)
v postgresu by to ale pomoci pl/sql mohlo jit ne ?</HTML>
v postgresu by to ale pomoci pl/sql mohlo jit ne ?</HTML>
Mno, zrovna jsem před pár dny četl ve skriptech (rozuměj učebnici) o rekurzi a SQL. Mluvilo se o tom v souvislosti s acyklickými grafy (využití tedy i u stromů). Přečet jsem to jen stručně (tj. nic si z toho nepamatuji), neboť jsem si říkal, že to asi nebudu nikdy potřebovat. Takže mi nezbývá, než Ti doporučit literaturu: Pokorný, Dotazovací jazyky, UK
Pamatuji si, že se to dalo udělat nějak pomocí dvou zahnízděných SELECT- WHERE bloků na jednu tabulku se dvěma aliasy a chytře udělanou podmínkou. Jo a taky si pamatuji, že je to dost náročný na systém (jako asi každá rekurze). :-)
Pamatuji si, že se to dalo udělat nějak pomocí dvou zahnízděných SELECT- WHERE bloků na jednu tabulku se dvěma aliasy a chytře udělanou podmínkou. Jo a taky si pamatuji, že je to dost náročný na systém (jako asi každá rekurze). :-)
hm, diky, ale pokud vim, tak na wz je povoleny jenom mysql, ne?
vim, ze to nejak musi jit, ale mohla by bejt nejaka databazicka (zrejme by se ji nerikalo relacni ;-) ), ve ktery by byly data ulozeny ve forme nejakyho dynamickyho stromu...
vim, ze to nejak musi jit, ale mohla by bejt nejaka databazicka (zrejme by se ji nerikalo relacni ;-) ), ve ktery by byly data ulozeny ve forme nejakyho dynamickyho stromu...
MySQL je konkrétní implementace SQL. Od SQL podle standardu se určitě nějak liší (syntakticky i sémanticky), ale způsob, který jsem nastínil já, používá pouze základní konstrukce, které by měly fungovat snad všude a problém by mohlo dělat jen to hnízdění, ale pokud to MySQL nepodporuje, tak je to trapné.
Andrew: no vidis, zrovna z Pokorneho prednasek dotazovacich jazyku si pamatuju, ze by to jit nemelo (mozna si pamatuju blbe, to by nebylo tak divne)
doufam teda, ze myslime totez:
mam tabulku treba:
id int
id_parent int null (pokud je vrchol stromu tak null, jinak ukazuje na predka)
...
cilem je efektivne zobrazit cely strom (nezname hloubky)
osobne mi nenapada zadne reseni, ktere bych povazoval za hezke, asi bych si ukladal u kazdeho uzlu (zaznamu) i odkaz (id) na koren abych to pak mohl aspon v jednom dotazu dostat z databaze a na klientske strane uz to nejak preformatovat do stromu ... pokud by to melo byt delane ohleduplne k databazi
jkt: no pro obycejne uzivatele ano ... ale pro tebe klidne rucne vytvorim ucet v postgresu (na jinem stroji) nebo mysql 4.1
doufam teda, ze myslime totez:
mam tabulku treba:
id int
id_parent int null (pokud je vrchol stromu tak null, jinak ukazuje na predka)
...
cilem je efektivne zobrazit cely strom (nezname hloubky)
osobne mi nenapada zadne reseni, ktere bych povazoval za hezke, asi bych si ukladal u kazdeho uzlu (zaznamu) i odkaz (id) na koren abych to pak mohl aspon v jednom dotazu dostat z databaze a na klientske strane uz to nejak preformatovat do stromu ... pokud by to melo byt delane ohleduplne k databazi
jkt: no pro obycejne uzivatele ano ... ale pro tebe klidne rucne vytvorim ucet v postgresu (na jinem stroji) nebo mysql 4.1
:-) Á, tady někdo chodil na Pokorného. Mno tak to seš na tom líp, neboť já na jeho přednášky nechodil. Myslíme to samý. Jak jsem říkal, tu část jsem v tý knížce jen přelít očima a zůstal mi v hlavě jen náznak. Asi si ji ještě půjčím a najdu to tam - samotnýho by mě to teď zajímalo, neboť stromová struktura se přeci jen někdy hodit může. Vím určitě, že tam něco o rekurzi zmíněno bylo (doufám, že to nebylo v části, kde se rozebíraly řídící struktury v novém standardu SQL), ale už si nejsem jist tou ohleduplností k databázi.
2 mira: klidne, ucty se hodi vzdycky ;-), ale zatim to neni nezbytny - stejne si to nejdriv vyzkousim doma a ne na webu, to jenom kdyz mi to bude chodit (muhehe...)
jinak koren nema parent_id==null, ale '0000000000...', coz je samozrejme detail
a ukladat si odkaz na koren? proc? strom ma jeden koren, kterej si muzu pamatovat nekde jinde, nez pro kazdej zaznam, ne?
(jde o ukladani informaci o clancich do databaze, clanky muzou mit podclanky atd, zatim se to realizuje pres vic selectu, pro kazdou uroven jeden, coz zase neni zase az tak pomaly)
btw, sql je jazyk pro relacni databaze (nebo ne? na 100% to nevim), takze se v nem podobna funkce asi jen tak neobjevi...
moje otazka: umi stromovou strukturu databaze nejakej jinej engine?
jinak koren nema parent_id==null, ale '0000000000...', coz je samozrejme detail
a ukladat si odkaz na koren? proc? strom ma jeden koren, kterej si muzu pamatovat nekde jinde, nez pro kazdej zaznam, ne?
(jde o ukladani informaci o clancich do databaze, clanky muzou mit podclanky atd, zatim se to realizuje pres vic selectu, pro kazdou uroven jeden, coz zase neni zase az tak pomaly)
btw, sql je jazyk pro relacni databaze (nebo ne? na 100% to nevim), takze se v nem podobna funkce asi jen tak neobjevi...
moje otazka: umi stromovou strukturu databaze nejakej jinej engine?
<HTML>Andrew: akorat ja jsem na ne chodil v Brne, treba nas chtel necim osalit a ziskat tak Praze konkurencni vyhodu ;-)
jkt: koren proto, abys mohl v jedne tabulce uchovat vice takovych stromu (abys jednim dotazem dostal jen jeden ten strom) ... ale to zalezi na tom, co vlastne presne potrebujes
> umi stromovou strukturu databaze nejakej jinej engine?
mozna v xml databazich by to slo lip, ale v tom se moc neorientuju</HTML>
jkt: koren proto, abys mohl v jedne tabulce uchovat vice takovych stromu (abys jednim dotazem dostal jen jeden ten strom) ... ale to zalezi na tom, co vlastne presne potrebujes
> umi stromovou strukturu databaze nejakej jinej engine?
mozna v xml databazich by to slo lip, ale v tom se moc neorientuju</HTML>
Tak jsem se opět dostal k tej knize a mira měl pravdu. Má tomu dokonce věnovanou kapitolu "Omezená síla příkazu SELECT". Na druhou stranu v některých implementacích (což u MySQL asi bohužel nebude, ale nevím - ještě jsem se nekoukal) existuje složka WITH, která tento problém řeší. Celé to pak vypadá takto (pro DB/2/V2):
WITH Pod_nad(Objekt, Nadobjekt) AS (SELECT Objekt, Nadobjekt FROM Je_casti WHERE Nadobjekt = 'RDBD' UNION ALL SELECT J.Objekt, J. Nadobjekt FROM Je_casti AS J, Pod_nad AS P WHERE P. Objekt = J.Nadobjekt)
Podom by následovalo
SELECT Objekt FROM Pod_nad
Nechtějte ale po mě to vyvětlovat. :-)) To jak jsem nastiňoval na začátku bylo myšleno podobně, ale bez té složky WITH, takže by to asi nefungovalo. Nicméně nebyl jsem skoro zas tak daleko :-)
Jo abych nezapomněl a dodržel autorská práva, tak uvedený příklad byl citací z Pokorný J., Dotazovací jazyky; Karolinum 2002
WITH Pod_nad(Objekt, Nadobjekt) AS (SELECT Objekt, Nadobjekt FROM Je_casti WHERE Nadobjekt = 'RDBD' UNION ALL SELECT J.Objekt, J. Nadobjekt FROM Je_casti AS J, Pod_nad AS P WHERE P. Objekt = J.Nadobjekt)
Podom by následovalo
SELECT Objekt FROM Pod_nad
Nechtějte ale po mě to vyvětlovat. :-)) To jak jsem nastiňoval na začátku bylo myšleno podobně, ale bez té složky WITH, takže by to asi nefungovalo. Nicméně nebyl jsem skoro zas tak daleko :-)
Jo abych nezapomněl a dodržel autorská práva, tak uvedený příklad byl citací z Pokorný J., Dotazovací jazyky; Karolinum 2002
nahodou jsem narazil na kapitolku "hierarchical queries" pri hledani v sql reference od oraclu (v pdf strana 503 pokud by to nekoho zajimalo)
hm, a nejaka citace by nebyla?
4jkt: Nějakej moc pohodlnej :-))
<HTML>to pdfko si urcite stahni, oznacit to sice jde, ale nebude to vypadat moc citelne ;-)
Hierarchical Queries If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause: hierarchical_query_clause::= n START WITH specifies the root row(s) of the hierarchy. n CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row. For example, ... PRIOR expr = expr or ... expr = PRIOR expr If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator. For example: CONNECT BY last_name != King AND PRIOR employee_id = manager_id In addition, the CONNECT BY condition cannot contain a subquery. START WITH condition CONNECT BY condition
PRIOR is a unary operator and has the same precedence as the unary + and -arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query. PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error. The manner in which Oracle processes a WHERE clause (if any) in a hierarchical query depends on whether the WHERE clause contains a join: n If the WHERE predicate contains a join, Oracle applies the join predicates before doing the CONNECT BY processing. n If the WHERE clause does not contain a join, Oracle applies all predicates other than the CONNECT BY predicates after doing the CONNECT BY processing without affecting the other rows of the hierarchy. Oracle uses the information from the hierarchical query clause to form the hierarchy using the following steps: 1. Oracle processes the WHERE clause either before or after the CONNECT BY clause depending on whether the WHERE clause contains any join predicates (as described in the preceding bullet list). 2. Oracle selects the root row(s) of the hierarchy those rows that satisfy the START WITH condition. 3. Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows. 4. Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 3, and then the children of those children, and so on. Oracle always selects children by evaluating the CONNECT BY condition with respect to a current parent row. 5. If the query contains a WHERE clause without a join, then Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition. See Also: "Examples" on page 8-5
6. Oracle returns the rows in the order shown in Figure 8 1. In the diagram, children appear below their parents. For an explanation of hierarchical trees, see Figure 2 1, "Hierarchical Tree" on page 2-87. Figure 8 1 Hierarchical Queries To find the children of a parent row, Oracle evaluates the PRIOR expression of the CONNECT BY condition for the parent row and the other expression for each row in the table. Rows for which the condition is true are the children of the parent. The CONNECT BY condition can contain other conditions to further filter the rows selected by the query. The CONNECT BY condition cannot contain a subquery. If the CONNECT BY condition results in a loop in the hierarchy, then Oracle returns an error. A loop occurs if one row is both the parent (or grandparent or direct ancestor) and a child (or a grandchild or a direct descendent) of another row. Examples The following hierarchical query uses the CONNECT BY clause to define the relationship between employees and managers: SELECT employee_id, last_name, manager_id FROM employees Note: In a hierarchical query, do not specify either ORDER BY or GROUP BY, as they will destroy the hierarchical order of the CONNECT BY results. If you want to order rows of siblings of the same parent, then use the ORDER SIBLINGS BY clause. See order_ by_clause on page 18-25. 1 7 8 ROOT 2 9 3 4 10 12 11 6 5
CONNECT BY PRIOR employee_id = manager_id; EMPLOYEE_ID LAST_NAME MANAGER_ID ----------- ------------------------- ---------- 101 Kochhar 100 108 Greenberg 101 109 Faviet 108 110 Chen 108 111 Sciarra 108 112 Urman 108 113 Popp 108 200 Whalen 101 . . . The next example is similar to the preceding example, but uses the LEVEL pseudocolumn to show parent and child rows: SELECT employee_id, last_name, manager_id, LEVEL FROM employees CONNECT BY PRIOR employee_id = manager_id; EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL ----------- ------------------------- ---------- ---------- 101 Kochhar 100 1 108 Greenberg 101 2 109 Faviet 108 3 110 Chen 108 3 111 Sciarra 108 3 112 Urman 108 3 113 Popp 108 3 . . See Also: n LEVEL on page 2-86 for a discussion of how the LEVEL pseudocolumn operates in a hierarchical query n SYS_CONNECT_BY_PATH on page 6-152 for information on retrieving the path of column values from root to node</HTML>
Hierarchical Queries If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause: hierarchical_query_clause::= n START WITH specifies the root row(s) of the hierarchy. n CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row. For example, ... PRIOR expr = expr or ... expr = PRIOR expr If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator. For example: CONNECT BY last_name != King AND PRIOR employee_id = manager_id In addition, the CONNECT BY condition cannot contain a subquery. START WITH condition CONNECT BY condition
PRIOR is a unary operator and has the same precedence as the unary + and -arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query. PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error. The manner in which Oracle processes a WHERE clause (if any) in a hierarchical query depends on whether the WHERE clause contains a join: n If the WHERE predicate contains a join, Oracle applies the join predicates before doing the CONNECT BY processing. n If the WHERE clause does not contain a join, Oracle applies all predicates other than the CONNECT BY predicates after doing the CONNECT BY processing without affecting the other rows of the hierarchy. Oracle uses the information from the hierarchical query clause to form the hierarchy using the following steps: 1. Oracle processes the WHERE clause either before or after the CONNECT BY clause depending on whether the WHERE clause contains any join predicates (as described in the preceding bullet list). 2. Oracle selects the root row(s) of the hierarchy those rows that satisfy the START WITH condition. 3. Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows. 4. Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 3, and then the children of those children, and so on. Oracle always selects children by evaluating the CONNECT BY condition with respect to a current parent row. 5. If the query contains a WHERE clause without a join, then Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition. See Also: "Examples" on page 8-5
6. Oracle returns the rows in the order shown in Figure 8 1. In the diagram, children appear below their parents. For an explanation of hierarchical trees, see Figure 2 1, "Hierarchical Tree" on page 2-87. Figure 8 1 Hierarchical Queries To find the children of a parent row, Oracle evaluates the PRIOR expression of the CONNECT BY condition for the parent row and the other expression for each row in the table. Rows for which the condition is true are the children of the parent. The CONNECT BY condition can contain other conditions to further filter the rows selected by the query. The CONNECT BY condition cannot contain a subquery. If the CONNECT BY condition results in a loop in the hierarchy, then Oracle returns an error. A loop occurs if one row is both the parent (or grandparent or direct ancestor) and a child (or a grandchild or a direct descendent) of another row. Examples The following hierarchical query uses the CONNECT BY clause to define the relationship between employees and managers: SELECT employee_id, last_name, manager_id FROM employees Note: In a hierarchical query, do not specify either ORDER BY or GROUP BY, as they will destroy the hierarchical order of the CONNECT BY results. If you want to order rows of siblings of the same parent, then use the ORDER SIBLINGS BY clause. See order_ by_clause on page 18-25. 1 7 8 ROOT 2 9 3 4 10 12 11 6 5
CONNECT BY PRIOR employee_id = manager_id; EMPLOYEE_ID LAST_NAME MANAGER_ID ----------- ------------------------- ---------- 101 Kochhar 100 108 Greenberg 101 109 Faviet 108 110 Chen 108 111 Sciarra 108 112 Urman 108 113 Popp 108 200 Whalen 101 . . . The next example is similar to the preceding example, but uses the LEVEL pseudocolumn to show parent and child rows: SELECT employee_id, last_name, manager_id, LEVEL FROM employees CONNECT BY PRIOR employee_id = manager_id; EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL ----------- ------------------------- ---------- ---------- 101 Kochhar 100 1 108 Greenberg 101 2 109 Faviet 108 3 110 Chen 108 3 111 Sciarra 108 3 112 Urman 108 3 113 Popp 108 3 . . See Also: n LEVEL on page 2-86 for a discussion of how the LEVEL pseudocolumn operates in a hierarchical query n SYS_CONNECT_BY_PATH on page 6-152 for information on retrieving the path of column values from root to node</HTML>
To jsem si počet :-))
ehm, asi si to radsi stahnu ;-)
Nové příspěvky
Python MySQL Connecter na WZ?
ukládání do db
Only full group by mode
Připojení k MySQL z venčí
chyba po přihlášení do DB
Pčipojení do DB
Povoleni views, triggres, functions
Uživatelské účty
nelze vytvořit db
Otazníky místo Š a Ž v textech v databázi
nefunguje mysqli?
Nefunkčný import údajov do databázy
SQL connection timeout
Nepřístupná Databáze
Problém s databázou
ProBlreM
phpBB 2 cast
Záložka event chýba
Zase ta reklama
Dotaz k údajům v databázy
joomla instalace
Počet Zátnamů
Stránkování
Jak vertikalne vycentrovat text v divu?
Nová databáze
Další témata
STATUS
FTP
HTTP
SQL
PHP
© 2002 - 2024 Web zdarma s.r.o. — zvládnete to sami