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

Question

我有一个评级表。

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      |

因为一位用户对卖家进行了评分。

我做错了什么？

Answer 1

Quote from the manual

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot “see” one another's effects on the target tables

^{(强调我的)}

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

This [...] means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query

您需要在现有行和插入行之间使用 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