sql - 我可以在 Postgres 中使用来自其他表的聚合数据更新表中的多个字段吗？

Question

我想出了如何一次处理一个字段(请参阅下面的两个更新查询)。有没有办法在单个查询中执行此操作？

UPDATE upload_data sd
SET
  photo_count =
  (SELECT sum(photo_count) 
  FROM media_uploads mu
  WHERE mu.user_id=sd.user_id
  AND mu.date=current_date
  GROUP BY mu.user_id);


UPDATE upload_data sd
SET
  video_count =
  (SELECT sum(video_count) 
  FROM media_uploads mu
  WHERE mu.user_id=sd.user_id
  AND date_trunc('month', mu.date)=date_trunc('month', sd.date)
  GROUP BY mu.user_id);

Answer 1

我认为您可以通过 JOIN 和 UPDATE 来完成此操作。在 postgresql 中为 UPDATE syntax ，它会是这样的:

UPDATE upload_data sd
SET  sd.photo_count = m.photo_count,
     sd.video_count = m.video_count
FROM
(
  SELECT 
    user_id, 
    sum(photo_count) AS  photo_count, 
    SUM(video_count) AS video_count
  FROM media_uploads 
  WHERE mu.date = current_date
  GROUP BY user_id
) AS m 
WHERE m.user_id = sd.user_id;