ÏÂÁ ×ÁÒÉÁÎÔÁ ÎÉ Ë Þ£ÒÔÕ :)
select p.* from from recurs_groups(:id_groups) g
join products p on g.id=p.id_group
ÔÁË ÂÕÄÅÔ ÌÕÞØÛÅ, Ô.Ë. ÎÁÓËÏÌØËÏ Ñ ÐÏÍÎÀ, × Ô×Ï£Í ÓÌÕÞÁÅ, ÄÌÑ ëáöäïê
ÚÁÐÉÓÉ products ÂÕÄÅÔ ×ÙÚÙ×ÁÔØÓÑ recurs_groups ÄÁ ÅÝ£ plan natural :(
÷ ÍÏ£Í ÖÅ ×ÁÒÉÁÎÔÅ recurs_groups ×ÙÚÙ×ÁÅÔÓÑ 1 ÒÁÚ ÄÁ ÅÝ£ É ÉÎÄÅËÓ ÉÚ
products ÉÓÐÏÌØÚÕÅÔÓÑ. åÓÌÉ ËÏÎÅÞÎÏ ID ÜÔÏ Õ ÔÅÂÑ Primary Key :)
äÁ? äÌÑ ÜËÓÐÅÒÉÍÅÎÔÏ× ×ÚÑÔ FB2rc3 ôÏÇÄÁ ÚÁÐÒÏÓ:
select p.name,p.fullname,p.description,
sum(di.qty),di.price_prov,di.price,di.discount,
d.date_docum,d2.date_docum,f.name,g.name,f.code
from products p, docum_items di, documents d, firms f,
product_cards pc, docum_items di2, documents d2, groups g,
(select id from recurs_groups(0)) rg
where (p.id_product = di.id_product)
and rg.id=p.id_group
and (d.id_docum = di.id_docum)
and (d.date_docum between '01.09.2006' and '09.11.2006')
and (d.id_operation = 2)
and (d.id_firm = f.id_firm)
and (di.id_card = pc.id_card)
and (pc.id_docitem = di2.id_docitem)
and (di2.id_docum = d2.id_docum)
and (p.id_group = g.id_group)
group by p.name,p.fullname,p.description,
di.price_prov,di.price,di.discount,d.date_docum,d2.date_docum,f.name,g.name,f.code
ÐÌÁÎ
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (GROUPS INDEX (RDB$PRIMARY7),
RECURS_GROUPS NATURAL), GROUPS INDEX (GROUPS_IDX__PARENT_NAME)), JOIN (G
INDEX (RDB$PRIMARY7), P INDEX (RDB$FOREIGN43)), DI INDEX (RDB$FOREIGN65)),
PC INDEX (RDB$PRIMARY10), D INDEX (RDB$PRIMARY3)), DI2 INDEX
(RDB$PRIMARY4))(F INDEX (RDB$PRIMARY6))(D2 INDEX (RDB$PRIMARY3)))
ÚÁÐÒÏÓ ×ÙÐÏÌÎÑÅÔÓÑ 13ÓÅËÕÎÄ, ÓËÁÎ ÐÏÞÔÉ ×ÓÅÈ ÔÁÂÌÉÃ ÄÏ 132ÔÙÓ ÚÁÐÉÓÅÊ,
groups 59 ÚÁÐÉÓÉ
ðÅÒÅÐÉÓÙ×ÁÅÍ ÚÁÐÒÏÓ
select p.name,p.fullname,p.description,
sum(di.qty),di.price_prov,di.price,di.discount,
d.date_docum,d2.date_docum,f.name,g.name,f.code
from products p, docum_items di, documents d, firms f,
product_cards pc, docum_items di2, documents d2, groups g
where (p.id_product = di.id_product)
and exists (select id from recurs_groups(0) where id=p.id_group)
and (d.id_docum = di.id_docum)
and (d.date_docum between '01.09.2006' and '09.11.2006')
and (d.id_operation = 2)
and (d.id_firm = f.id_firm)
and (di.id_card = pc.id_card)
and (pc.id_docitem = di2.id_docitem)
and (di2.id_docum = d2.id_docum)
and (p.id_group = g.id_group)
group by p.name,p.fullname,p.description,
di.price_prov,di.price,di.discount,d.date_docum,d2.date_docum,f.name,g.name,f.code
ÐÌÁÎ
PLAN (GROUPS INDEX (RDB$PRIMARY7))(RECURS_GROUPS NATURAL)(GROUPS INDEX
(GROUPS_IDX__PARENT_NAME))
PLAN SORT (JOIN (D INDEX (DOCUMENTS_IDX, RDB$FOREIGN62), F INDEX
(RDB$PRIMARY6), DI INDEX (RDB$FOREIGN21), PC INDEX (RDB$PRIMARY10), P INDEX
(RDB$PRIMARY9), G INDEX (RDB$PRIMARY7), DI2 INDEX (RDB$PRIMARY4), D2 INDEX
(RDB$PRIMARY3)))
ÚÁÐÒÏÓ ×ÙÐÏÌÎÑÅÔÓÑ 5 ÓÅËÕÎÄ, ÓËÁÎ ÐÏÞÔÉ ×ÓÅÈ ÔÁÂÌÉÃ 5 ÔÙÓ ÚÁÐÉÓÅÊ, Á groups
110 ÔÙÓ ÚÁÐÉÓÅÊ
Ñ ÄÕÍÁÀ ÞÔÏ ÍÏÊ ×ÁÒÉÁÎÔ ÐÏÌÕÞÛÅ. èÏÔÑ ÎÕÔÒÏÍ ÞÕÀ ÞÔÏ-ÔÏ ÚÄÅÓØ ÎÅ ÄÏ ËÏÎÃÁ
ÄÏÄÕÍÁÎÏ, ÍÏÖÅÔ ËÔÏ ÉÚ ÇÕÒÕ ÒÁÚ×ÅÅÔ ÍÏÉ ÍÙÓÌÉ?
