递归公共表表达式（CTE）在降序表与升序公式相遇处返回项

编程语言 2026-07-11

我想在数值向下递归的那部分使用 c 在 pc 表中的值；向上递归时则使用 plus_one 的递归公式，直到来自 plus_one 的值大于来自 pc.c 的值，然后返回 plus_one 的值（从表中降序推进一个值，从公式中升序推进一个值，当表中的值小于公式中的值时，返回公式中的值）。

create table pc(
  c integer not null
) strict;

create index ipc on pc(c desc);

insert into pc(c) values (8);
insert into pc(c) values (6);
insert into pc(c) values (4);
insert into pc(c) values (2);
insert into pc(c) values (0);

with recursive meet_in_middle(
  cit,
  plus_one
) as (
  values (
    0x7FFFFFFFFFFFFFFF,
    0
  )
  union all
  select
    pc.c as cit,
    meet_in_middle.plus_one + 1
  from
    pc,
    meet_in_middle
  where
    meet_in_middle.plus_one > cit
)
select max(meet_in_middle.plus_one) from meet_in_middle;

伪代码：

formula_value = 0
sort_descending(table_values)
for value in table_values
{
  if formula_value > value
  {
    return formula_value
  }
  formula_value += 1
}
return formula_value

因此SQL应按如下方式迭代：

first row from `pc` = 8
first value from iterative calculation = 0
Check 0 > 8, which is false, so iterate again

next row from `pc` = 6
next value from iterative calculation = 1
Check 1 > 6, which is false, so iterate again

next row from `pc` = 4
next value from iterative calculation = 2
Check 2 > 4, which is false, so iterate again

next row from `pc` = 2
next value from iterative calculation = 3
Check 3 > 2, which is TRUE, so stop iterating

return 3

解决方案

下面的代码对你的代码做了两处修改。

首先，你需要对源数据进行枚举，这样就可以一次处理一行。我使用ROW_NUMBER来实现。

其次，递归分支的WHERE子句应该在你希望继续迭代时返回TRUE，而不是在你想要停止时返回FALSE。

最后，你需要从公共表表达式（CTE）中只取最后一行。

WITH RECURSIVE 
  enumerated (
    id,
    c
  )
AS
(
  SELECT
    ROW_NUMBER() OVER (ORDER BY c DESC)   id,
    c
  FROM
    pc
),
  meet_in_middle (
    c_id,
    c_val, 
    iterated_calc
  )
AS
(
  -- Anchor dataset with a dummy row, in case `pc` is empty, 
  -- and the first iterative calculation value
  VALUES (0, 0, 0)

  UNION ALL

  SELECT
    -- next row of `pc`
    next.*,
    -- next calculated value
    prev.iterated_calc + 1 
  FROM
    meet_in_middle   AS prev
  INNER JOIN
    enumerated       AS next
      ON next.id = prev.c_id + 1
      -- The id of the next `pc` row
      -- Similarity to iteratively calculated value is coincidental
  WHERE
    prev.iterated_calc <= next.c
    -- Only iterate if the previous iterated calculation
    -- does NOT satisfy our condition
)
SELECT
  iterated_calc
FROM
  meet_in_middle
ORDER BY
  c_id DESC
LIMIT
  1
-- Get only the last row from the CTE
;