如何将select和insert组合到sql中

Question

我有一个补充请求：

INSERT INTO LIKES_PRODUCTS AS L (L.USER_ID, L.PRODUCT_ID) VALUES('7', '1')

表中的行数请求如下：

SELECT COUNT(L.USER_ID) AS LIKES FROM LIKES_PRODUCTS AS L

是否可以将它们合并到一个查询中，以便首先进行添加，然后只对表中的行进行计数？

Answer 1

你可以用一个数据修改CTE来做这个

with new_row as (
  insert into likes_products (user_id, product_id)
  values (7,1)
)
select count(user_id) as likes
from likes_products;

然而，最终的select没有看到以前的CTE的效果。如果总是插入一行，则只需在select中插入count(user_id) + 1即可。另一个选项是返回插入的行并将它们添加到计数中：

with new_rows as (
  insert into likes_products (user_id, product_id)
  values (7,1),(8,2)
  returning *
)
select count(user_id) + (select count(*) from new_rows) as likes
from likes_products;