在update中存储子查询的值，供以后使用

Question

试图计算价格的%变化。下面的查询可以工作，但希望有一种方式，我不必首先调用相同的子查询？除非我弄错了，否则我不认为这里有工作。

update changes rt1
    set change = coalesce((rt1.price - (SELECT 
        price
      FROM clothing_price
      WHERE changes.id = clothing_price.id AND clothing_price.timestamp > NOW() - INTERVAL '24 HOURS'
      ORDER BY timestamp ASC
      LIMIT 1) / (SELECT 
        price
      FROM clothing_price
      WHERE changes.id = clothing_price.id AND clothing_price.timestamp > NOW() - INTERVAL '24 HOURS'
      ORDER BY timestamp ASC
      LIMIT 1)), 0)
where clothing_type = 1;

Answer 1

(在修复无效引用之后)查询将刻录为：

UPDATE changes rt1
SET    change = CASE WHEN (
   SELECT cp.price
   FROM   clothing_price cp
   WHERE  cp.id = rt1.id   -- fixed reference!
   AND    cp.timestamp > now() - interval '24 hours'
   ORDER  BY cp.timestamp
   LIMIT  1) IS NULL THEN 0 ELSE rt1.price - 1 END
WHERE  clothing_type = 1;

除此之外，为了“存储”相关查询的结果，必须在一个changes子句中自联接到更新后的表USING的另一个实例。但现在看来没什么意义了.