Στις Thursday 13 January 2011 14:13:21 ο/η Achilleas Mantzios έγραψε:
> SELECT distinct m.id,coalesce(m.givenname,''),coalesce(m.midname,''),m.surname from marinerstates ms,vessels
vsl,marinerm
> where m.id=ms.marinerid and ms.vslid=vsl.id and ms.state='Active' and coalesce(ms.endtime,now())::date >=
'2006-07-15'and
> ms.starttime::date <= '2007-01-11' and m.marinertype='Mariner' and m.id not in
> (SELECT distinct mold.id from marinerstates msold,vessels vslold,mariner mold where mold.id=msold.marinerid and
msold.vslid=vslold.id
> and msold.state='Active' and coalesce(msold.endtime,now())::date >= '2006-07-15' and msold.starttime::date <=
'2007-01-11'and exists
> (select 1 from marinerstates msold2 where msold2.marinerid=msold.marinerid and msold2.state='Active' and
msold2.id<> msold.id and
> msold2.starttime<msold.starttime AND (msold.starttime-msold2.endtime)<='18 months') and
mold.marinertype='Mariner')
> order by m.surname,coalesce(m.givenname,''),coalesce(m.midname,'')
Rewriting the query with NOT EXISTS (a practice quite common pre 8.3, IIRC) as:
SELECT distinct m.id,coalesce(m.givenname,''),coalesce(m.midname,''),m.surname from marinerstates ms,vessels
vsl,marinerm
where m.id=ms.marinerid and ms.vslid=vsl.id and ms.state='Active' and coalesce(ms.endtime,now())::date >= '2006-07-15'
and
ms.starttime::date <= '2007-01-11' and m.marinertype='Mariner' and NOT EXISTS
(SELECT distinct mold.id from marinerstates msold,vessels vslold,mariner mold where
mold.id=msold.marineridand
msold.vslid=vslold.id and msold.state='Active' and coalesce(msold.endtime,now())::date >=
'2006-07-15'and
msold.starttime::date <= '2007-01-11' and exists
(select 1 from marinerstates msold2 where msold2.marinerid=msold.marinerid and
msold2.state='Active' and msold2.id <> msold.id and msold2.starttime<msold.starttime AND
(msold.starttime-msold2.endtime)<='18 months') and mold.marinertype='Mariner' AND mold.id=m.id)
order by m.surname,coalesce(m.givenname,''),coalesce(m.midname,'');
Is fast.
--
Achilleas Mantzios