sqlite 如何在SQL查询条件中存储中间结果

yi0zb3m4  于 12个月前  发布在  SQLite
关注(0)|答案(2)|浏览(230)
select id, (sum(col2) - sum(col1)) as net_col1_col2
    from data4sim
    where end_time <= :input_time
    and (sum(col2) + sum(col1)) > 0
    and (
      (sum(col1) + sum(col2))
      <= abs(coalesce(col3, 0) - (col4 - (sum(col2) - sum(col1)))) + :EPS
    )
    group by id

字符串
这是我第一次使用SQL,我试图写一个查询,上面的条件。我注意到,我计算(sum(col1) + sum(col2))(sum(col2) - sum(col1))两次,我不知道如何存储中间结果,以便我可以在第二个中重用它。
我可以这样做吗:

where time < :input_time
  and (sum(col1) + sum(col2)) as temp
  and (temp > 0)
  and (temp < col3) <= ....


我想一般来说,我不知道如何存储中介结果的条件。

sc4hvdpw

sc4hvdpw1#

在sqlite中,您可以在SELECT子句中为列设置别名,然后在HAVING子句中引用该别名(这是您应该移动基于SUM()等聚合构建的条件的位置)。

CREATE TABLE test (id integer, val1 integer, val2 integer);
INSERT INTO test (id, val1, val2) VALUES (1,1,5);
INSERT INTO test (id, val1, val2) VALUES (2,3,6);
INSERT INTO test (id, val1, val2) VALUES (3,5,7);

SELECT SUM(val1 + val2) as derived_col
FROM test
WHERE id IN (1,2)
HAVING derived_col > 3;

字符串
dbfiddle here

falq053o

falq053o2#

我不知道你的数据,但像这样的东西应该这样做:

select id, 
       (sum(col2) - sum(col1)) as net_col1_col2,
       (sum(col2) + sum(col1)) as total
from data4sim
where end_time <= :input_tum    
group by id
having total > 0
   and total <= abs(coalesce(col3, 0) - (col4 - net_col1_col2)) + :EPS

字符串
可以在having子句中使用select的别名。

相关问题