Tonkuma wrote:
> Bob Badour wrote:
>
>>select id_lwr, min_t as lwr_bound, min(max_t) as upr_bound
>>from (
>> select id as id_lwr, t as min_t from W W1 where not exists (
>> select 1 from W W2 where W1.t = W2.t + 1 and W1.id = W2.id
>> )
>>) WL,
>>(
>> select id as id_upr, t as max_t from W W3 where not exists (
>> select 1 from W W4 where W3.t = W4.t - 1 and W3.id = W4.id
>> )
>>) WU
>>where id_lwr = id_upr and max_t > min_t
>>group by id_lwr, min_t
>>;
>>
>>Depending on your flavour of SQL, you might have to change the simple
>>additions to function calls like DATE_ADD etc.
>
> Same kind of thinking. But it is not necessary to use nested table
> expression.
> SELECT lbound.id
> , CHAR(lbound.t,USA) "LowerBound"
> , CHAR(MIN(ubound.t),USA) "UpperBound"
> FROM W lbound
> , W ubound
> WHERE NOT EXISTS
> (SELECT *
> FROM W lower
> WHERE lower.id = lbound.id
> AND lower.t = lbound.t - 1 day
> )
> AND ubound.id = lbound.id
> AND ubound.t >= lbound.t
> AND NOT EXISTS
> (SELECT *
> FROM W upper
> WHERE upper.id = lbound.id
> AND upper.t = ubound.t + 1 day
> )
> GROUP BY
> lbound.id, lbound.t
How is a table expression nested in the where clause any less nested
than one in the from clause?
Received on Tue Dec 12 2006 - 08:21:50 CST
