--- proxi...@land.ru wrote:
From: Michael <proxi...@land.ru>
To: sqlite-users@sqlite.org
Subject: [sqlite] double calculation in SELECT
Date: Sun, 04 Oct 2009 15:31:47 +0700
Hello,
I have following SELECT statement in my program (Delphi+sqlite3):
_________________________________________________________________________________________
select
id,power,
(select sum
((strftime("%s",table2.endtime)-strftime("%s",table2.begintime))*table2.power)
from table2 where table1.id=table1_id),
(
select min(point) from points where points.table1_id=table1.id and
point >=
(select sum
((strftime("%s",table2.endtime)-strftime("%s",table2.begintime))*table2.power)
from table2 where table1.id=table2.table1_id)
),
(
(
select min(point) from points where points.table1_id=table1.id
and point >=
(select sum
((strftime("%s",table2.endtime)-strftime("%s",table2.begintime))*table2.power)
from table2 where table1.id=table2.table1_id )
)
-
(select sum
((strftime("%s",table2.endtime)-strftime("%s",table2.begintime))*table2.power)
from table2 where table1.id=table1_id)
) / power
from table1
where standing=1
group by table1.id
order by table1.id
________________________________________________________________________________________
This statement contains text that is repeated 4 times:
(select sum
((strftime("%s",table2.endtime)-strftime("%s",table2.begintime))*table2.power)
from table2 where table1.id=table2.table1_id)
How to reduce this statement and avoid double calculations?
Thanks,
Michael.
_______________________________________________
Hello, the SQL below uses an 'inline view' type of sub-query. Edzard.
SELECT
v.id,
v.power,
v.sum1,
v.sum1 / v.power,
...
FROM
(
SELECT
table1.id,
table1.power,
SUM (<complex expression>) AS sum1
FROM
table1,
table2
WHERE
table2.table1_id = table1.id
GROUP BY
table1.id,
table1.power
) v
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
