PostgreSQL中WITH SELECT结合random()返回零/多行的原因咨询

核心原因 #

random() 函数在WHERE子句中会逐行执行，而非在查询启动时生成一个固定的随机值。

你的查询本意是生成1-8之间的一个随机数，匹配对应的num行，但实际执行逻辑完全偏离预期：

• 针对numered_test中的每一行，都会重新调用一次random()生成新的随机数
• 由此引发两种异常结果：
  • 若所有行的num都不等于该行判断时生成的随机数，查询返回零行
  • 若多个行在判断时，生成的随机数恰好等于自身的num，查询返回多行

逻辑验证示例 #

假设某次执行过程：

• 行num=1，random()生成3 → 不匹配
• 行num=2，random()生成2 → 匹配，返回该行
• 行num=3，random()生成3 → 匹配，返回该行
• 后续行的随机数均不对应自身num
  最终查询会返回2行。若所有行判断时的随机数都不匹配num，则返回零行。

正确实现方式 #

要实现「随机返回1行」的需求，需让random()仅生成一次固定值，再用该值匹配数据。以下是两种可行写法：

写法1：用CTE固定随机值 #

WITH random_num AS (
  SELECT FLOOR(random() * 8 + 1) AS target_num
), numered_test AS (
  SELECT
    "id",
    row_number() over (order by "id") num
  FROM test
)
SELECT nt.* 
FROM numered_test nt
JOIN random_num rn ON nt.num = rn.target_num;

写法2：用子查询生成固定随机值 #

SELECT *
FROM (
  SELECT
    "id",
    row_number() over (order by "id") num
  FROM test
) t
WHERE num = (SELECT FLOOR(random() * 8 + 1));

以上两种写法中，random()仅执行一次生成固定值，结合num的唯一性（1-8），能确保返回0或1行结果。

内容的提问来源于stack exchange，提问作者hloroform