YAN HONG YE <[email protected]> wrote:
> I have a 2 table:
> 1. tb1:
>
> aa bb cc mdate
> 1 d1 2.5 2012-08-07
> 2 d2 3.3 2012-08-07
> 3 d1 6.4 2012-08-09
>
> 2. tb2:
> aa bb cc
> 1. d1 5.3
> 2. d2 7.6
>
> mysql sql cmd is:
>
> select tb2.bb,tb2.cc,(max(tb1.mdate)-min(tb1.mdate)) as mmd,(mmd.
> max(tb1.mdate).cc-mmd. min(tb1.mdate).cc) as mmc from tb1,tb2
> where tb2.bb in (select bb from tb1 where bb group by bb having count(bb)) ;
>
> I wanna the result is:
> bb cc mmd mmc
> d1 5.3 2 3.9
>
> the result 3.9 mean is 6.4-2.5 , mdate 2012-08-09 - 2012-08-07 =2, and 6.4
> - 2.5=3.9
It's not quite clear what you want. See if something like this helps:
select tb2.bb, tb2.cc,
julianday(tbmax.mdate)-julianday(tbmin.mdate) mmd,
tbmax.cc - tbmin.cc mmc
from tb2, tb1 tbmin, tb1 tbmax
where tbmin.aa = (select aa from tb1 inner where inner.bb = tb2.bb order by
mdate asc limit 1)
and tbmax.aa = (select aa from tb1 inner where inner.bb = tb2.bb order by
mdate desc limit 1)
and tbmin.aa != tbmax.aa;
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
