时间戳

首页 > 解决方案 > 从表中获取值（使用条件）并在一个命令中存储在另一个表中

database - 从表中获取值（使用条件）并在一个命令中存储在另一个表中

问题描述

我尝试使用满足另一个表中的条件的值的总和来更新表中的值。

在第一个命令中，我想获取共享文章的数量（此处为 3）：

db=# select article_id,liked,shared, COUNT(article_id) FROM social WHERE article_id > 0 GROUP BY article_id,liked,shared HAVING article_id=2087 AND shared=true;
 article_id | liked | shared | count 
------------+-------+--------+-------
       2087 | f     | t      |     3
(1 row)

我有另一个表，我需要将此值 (3) 放在右栏中：

db=# SELECT article_id,shared FROM articles WHERE article_id=2087;
 article_id | shared 
------------+--------
       2087 |       
(1 row)

我可以用 Python 中的两个命令来做到这一点，但我确信在一个 SQL 请求中有更好的方法来做到这一点？

编辑：

我试过这个命令：

db=# UPDATE articles SET shared=subquery.count FROM ( SELECT count(article_id) FROM social WHERE article_id=2087 AND shared=true) AS subquery WHERE article_id=2087;
UPDATE 1

db=# SELECT article_id,shared FROM articles WHERE article_id=2087;
 article_id | shared 
------------+--------
       2087 |      3
(1 row)

我只需要设置一次article_id，我可能会很好。有什么建议吗？

标签： databasepostgresqldml

解决方案

我会将计数包装在 CTE 中，并根据 CTE 的结果进行更新：

with counts as (
  select article_id,liked,shared, COUNT(article_id) as article_count
  FROM social
  WHERE article_id > 0
  GROUP BY article_id,liked,shared
  HAVING article_id=2087 AND shared=true
)
update articles a
set shared = c.article_count
from counts c
where a.article_id = c.article_id

其他一些挑剔的事情......

  1. HAVING article_id=2087 and shared=true最好移到该where子句。当然，这会起作用，但HAVING通常保留给聚合函数 (having count (*) > 1或having sum (qty) = 0。将它移动到 where 子句的好处是可以避免抓取和聚合最终被过滤掉的数据。我不确定这一点，但它甚至可以更好地利用索引。

  2. shared=true可以缩短为shared。您可能不喜欢不清楚这意味着什么，所以接受或放弃它。

  3. 第一个查询和您要更新的内容不一致...具体来说，初始查询可能会返回多个结果，因为您不只是按 article_id 分组——因此您可能会为同一个 article_id 获得大量结果。您相信这个构造会更新正确的构造。这是你的意思吗？

这三个建议的总结和一些假设：

with counts as (
  select article_id, COUNT(article_id) as article_count
  FROM social
  WHERE article_id > 0 and article_id=2087 AND shared
  GROUP BY article_id
)
update articles a
set shared = c.article_count
from counts c
where a.article_id = c.article_id

我意识到article_id > 0并且article_id=2087是多余的，但我认为后者是您最终会删除的测试用例，因此您可以一次更新所有记录。

推荐阅读