在子查询中重用查询结果

Question

我有这个丑陋的查询...

sum(CASE 
        WHEN effective_from_date < '2011-05-24' THEN (rate * (effective_to_date - '2011-05-24' + 1)) 
        WHEN effective_to_date > '2011-05-28' THEN (rate * ('2011-05-28' - effective_from_date + 1)) 
        ELSE (rate * (effective_to_date - effective_from_date + 1))
    END
    ) as price_cal_rate
        FROM calendar_event
        WHERE property_rid = (SELECT rid FROM property WHERE web_id = 'T28314') AND 
        ((effective_from_date BETWEEN '2011-05-24' AND '2011-05-28')  OR (effective_to_date BETWEEN '2011-05-24' AND '2011-05-28')) 
         AND 
         NOT EXISTS (

         SELECT days_diff FROM (


        SELECT  ((effective_from_date - lag(effective_to_date) OVER (PARTITION BY NULL ORDER BY effective_from_date ASC))) AS days_diff, effective_from_date, effective_to_date
             FROM calendar_event
             WHERE property_rid = (SELECT rid FROM property WHERE web_id = 'T28314') AND 
        ((effective_from_date BETWEEN '2011-05-26' AND '2011-05-28') OR (effective_to_date BETWEEN '2011-05-26' AND '2011-05-28')) 


        ) AS t WHERE COALESCE(days_diff, 0) > 1 

        ) AND EXISTS (select * from  (
          select min(effective_from_date) as min_date, max(effective_to_date) as max_date FROM calendar_event
        WHERE property_rid = (SELECT rid FROM property WHERE web_id = 'T28314') AND 
        ((effective_from_date BETWEEN '2011-05-24' AND '2011-05-28')  OR (effective_to_date BETWEEN '2011-05-24' AND '2011-05-28'))
        ) as max_min WHERE min_date <= '2011-05-24' and max_date >= '2011-05-28')

查询正在计算日期范围内的速率...查询很好...但是查询中有很多重复...我想知道是否有一个很好的方法来存储这个子查询的结果

FROM calendar_event
        WHERE property_rid = (SELECT rid FROM property WHERE web_id = 'T28314') 
AND 
            ((effective_from_date BETWEEN '2011-05-24' AND '2011-05-28')  OR (effective_to_date BETWEEN '2011-05-24' AND '2011-05-28'))  

并在我的查询中使用它......

Answer 1

您可以使用临时表，因为“mu 太短”建议，但如果您只需要单个“主”查询中的结果并且您使用的是 PostgreSQL 8.4 或更高版本，您也可以使用 带有查询