时间戳

首页 > 解决方案 > 查询在插入数据之前返回值

sql - 查询在插入数据之前返回值

问题描述

我有一个评分表。

CREATE TABLE merchants_rating(
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
    merchant_id INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
    rating      INTEGER NOT NULL
);

我想在其中插入数据，得到卖家评分和评分用户数的总和。

我提出了一个要求。

WITH INSERT_ROW AS (
  INSERT INTO MERCHANTS_RATING (USER_ID, MERCHANT_ID, RATING) 
  VALUES(147, 92, 2)
)
SELECT SUM(R.RATING) AS SUMMA_RATING, COUNT(R.USER_ID) AS USER_COUNT 
FROM MERCHANTS_RATING AS R 
WHERE R.MERCHANT_ID = 92

数据添加成功，但是输出有问题。当表为空并且我第一次向其中添加数据时，我会得到这样的值。

 SUMMA_RATING | USER_COUNT | 
----------------------------
     NULL     |     0      |

虽然我期待收到。

 SUMMA_RATING | USER_COUNT | 
----------------------------
      2      |      1      |

由于一位用户评价了卖家。

我做错了什么？

标签： sqlpostgresqlsql-insertcommon-table-expression

解决方案

从手册中引用

WITH 中的子语句彼此同时执行，并与主查询同时执行。因此，当在 WITH 中使用数据修改语句时，指定更新实际发生的顺序是不可预测的。所有语句都使用同一个快照执行（参见第 13 章），因此它们无法“看到”彼此对目标表的影响

（强调我的）

幸运的是，该手册还解释了如何解决这个问题：

这 [...] 意味着返回数据是在不同 WITH 子语句和主查询之间传达更改的唯一方法

您需要在现有行和插入的行之间使用 UNION：

WITH insert_row AS (
  INSERT INTO merchants_rating (user_id, merchant_id, rating) 
  VALUES (147, 92, 2)
  returning * --<< return the inserted row to the outer query
)
SELECT sum(r.rating) AS summa_rating, count(r.user_id) AS user_count 
FROM (
  SELECT rating, user_id
  FROM merchants_rating
  WHERE merchant_id = (SELECT merchant_id FROM insert_row)
  UNION ALL
  SELECT rating, user_id
  FROM insert_row
) r;

如果您打算在第一步中插入多行，则需要r.merchant_id =将r.merchant_id IN

在线示例：https ://rextester.com/BSCI15298

推荐阅读