PostgreSQL单UPDATE查询实现时间条件更新与健康值调整

首先是你的表结构：

CREATE TABLE pet (
  _id SERIAL PRIMARY KEY,
  player_id int REFERENCES player(_id),
  feed_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  num_poop integer DEFAULT 0,
  clean_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  play_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  health integer DEFAULT 100
);

完全可以用单条UPDATE语句实现你要的逻辑，不需要先查询再在应用层处理。直接看实现代码：

UPDATE pet
SET
  -- 分别处理三个时间字段：满足条件则更新为当前时间，否则保留原值
  feed_time = CASE WHEN feed_time < CURRENT_TIMESTAMP - INTERVAL '1 hour' THEN CURRENT_TIMESTAMP ELSE feed_time END,
  clean_time = CASE WHEN clean_time < CURRENT_TIMESTAMP - INTERVAL '1 hour' THEN CURRENT_TIMESTAMP ELSE clean_time END,
  play_time = CASE WHEN play_time < CURRENT_TIMESTAMP - INTERVAL '1 hour' THEN CURRENT_TIMESTAMP ELSE play_time END,
  -- 计算health：只要有一个字段满足更新条件就加20，且不超过100
  health = LEAST(
    health + CASE 
      WHEN feed_time < CURRENT_TIMESTAMP - INTERVAL '1 hour' 
        OR clean_time < CURRENT_TIMESTAMP - INTERVAL '1 hour' 
        OR play_time < CURRENT_TIMESTAMP - INTERVAL '1 hour' 
      THEN 20 ELSE 0 END,
    100
  )
-- 只更新至少有一个字段需要修改的行，避免无意义操作
WHERE
  feed_time < CURRENT_TIMESTAMP - INTERVAL '1 hour'
  OR clean_time < CURRENT_TIMESTAMP - INTERVAL '1 hour'
  OR play_time < CURRENT_TIMESTAMP - INTERVAL '1 hour';

逻辑说明： #

• 时间字段更新：每个字段通过CASE表达式判断，只有当现有值早于当前时间1小时以上时，才替换为当前时间，否则保持原数值。
• health计算：用CASE判断三个时间字段是否至少有一个符合更新条件，符合就加20，否则加0；再通过LEAST()函数确保health不会超过100的上限。
• WHERE过滤：只筛选出需要更新的行，避免对所有行执行更新操作，提升查询效率。

如果需要针对特定玩家的宠物更新，只要在WHERE子句末尾追加AND player_id = 目标玩家ID即可。

内容的提问来源于stack exchange，提问作者M S