问题

以下是翻译的部分内容：

考虑 `sales` 表：

| sale_id | item_id | quantity | sale_date  |
|---------|---------|---------:|------------|
| 100     | P1      |        5 | 2023-02-18 |
| 101     | P1      |        4 | 2023-02-17 |
| 103     | B2      |        7 | 2023-02-19 |
| 104     | P1      |        1 | 2023-02-20 |

`stock_balance` 表：

| item_id | balance |
|---------|--------:|
| P1      |       6 |
| B2      |       5 |

我想创建一个 SQL 查询，显示哪个销售订单具有要交付的余额，较早的销售订单具有优先权。此查询将生成以下结果

| sale_id | item_id | quantity | sale_date  | balance_start | has_balance | reserved | balance_end |
|---------|---------|---------:|------------|--------------:|-------------|---------:|------------:|
| 103     | B2      | 7        | 2023-02-19 | 5             | false       | 0        | 5           |
| 101     | P1      | 4        | 2023-02-17 | 6             | true        | 4        | 2           |
| 100     | P1      | 5        | 2023-02-18 | 2             | false       | 0        | 2           |
| 104     | P1      | 1        | 2023-02-20 | 2             | true        | 1        | 1           |

我尝试创建以下查询

```sql
with sale_reserved as (
  SELECT 
    s1.*, 
    sb.balance as balance_start,
    CASE 
      WHEN s1.quantity <= sb.balance AND sb.balance - s1.quantity >= (
        SELECT COALESCE(SUM(s2.quantity), 0) 
        FROM sales s2 
        WHERE s2.item_id = s1.item_id AND s2.sale_date < s1.sale_date
      ) THEN s1.quantity
      ELSE 0
    END AS reserved
  FROM 
    sales s1 
    JOIN stock_balance sb ON s1.item_id = sb.item_id 
)
select 
sale_id, item_id, quantity, sale_date , balance_start, 
reserved>0 as has_balance,
reserved,
(balance_start-reserved) as balance_end 
from sale_reserved
ORDER BY item_id, sale_date;

这将产生：

可以看到起始余额不正确，销售订单 '104' 具有余额并显示为 false。

with sale_reserved as (
  SELECT 
    s1.*, 
    sb.balance as balance_start,
    CASE 
      WHEN s1.quantity <= sb.balance AND sb.balance - s1.quantity >= (
        SELECT COALESCE(SUM(s2.quantity), 0) 
        FROM sales s2 
        WHERE s2.item_id = s1.item_id AND s2.sale_date < s1.sale_date
      ) THEN s1.quantity
      ELSE 0
    END AS reserved
  FROM 
    sales s1 
    JOIN stock_balance sb ON s1.item_id = sb.item_id 
)
select 
sale_id, item_id, quantity, sale_date , balance_start, 
reserved>0 as has_balance,
reserved,
(balance_start-reserved) as balance_end 
from sale_reserved
ORDER BY item_id, sale_date;

答案1

得分: 1

您可以使用递归的cte（公共表达式）来实现：

with recursive cte(sale_id, item_id, m, quantity, balance_start, has_balance, reserved, balance_end) as (
   select s.sale_id, s1.item_id, s1.m, s.quantity, b.balance, b.balance >= s.quantity, 
         case when b.balance >= s.quantity then s.quantity else 0 end, 
         case when b.balance >= s.quantity then b.balance - s.quantity else b.balance end 
   from (select s.item_id, min(s.sale_date) m from sales s group by s.item_id) s1 
   join sales s on s.item_id = s1.item_id and s.sale_date = date(s1.m) 
   join stock_balance b on b.item_id = s1.item_id
   union all
   select s2.sale_id, c.item_id, s2.sale_date, s2.quantity, c.balance_end, c.balance_end >= s2.quantity, 
         case when c.balance_end >= s2.quantity then s2.quantity else 0 end, 
         case when c.balance_end >= s2.quantity then c.balance_end - s2.quantity else c.balance_end end 
   from cte c 
   cross join lateral (select s.* from sales s where s.item_id = c.item_id 
       and s.sale_date = (select min(s1.sale_date) from sales s1 where s1.item_id = c.item_id and s1.sale_date > c.m)) s2
)
select c.sale_id, c.item_id, c.quantity, c.m sale_date, c.balance_start, c.has_balance, c.reserved, c.balance_end
from cte c order by c.item_id, c.m

查看示例

with recursive cte(sale_id, item_id, m, quantity, balance_start, has_balance, reserved, balance_end) as (
select s.sale_id, s1.item_id, s1.m, s.quantity, b.balance, b.balance >= s.quantity, 
case when b.balance >= s.quantity then s.quantity else 0 end, 
case when b.balance >= s.quantity then b.balance - s.quantity else b.balance end 
from (select s.item_id, min(s.sale_date) m from sales s group by s.item_id) s1 
join sales s on s.item_id = s1.item_id and s.sale_date = date(s1.m) 
join stock_balance b on b.item_id = s1.item_id
union all
select s2.sale_id, c.item_id, s2.sale_date, s2.quantity, c.balance_end, c.balance_end >= s2.quantity, 
case when c.balance_end >= s2.quantity then s2.quantity else 0 end, 
case when c.balance_end >= s2.quantity then c.balance_end - s2.quantity else c.balance_end end 
from cte c 
cross join lateral (select s.* from sales s where s.item_id = c.item_id 
and s.sale_date = (select min(s1.sale_date) from sales s1 where s1.item_id = c.item_id and s1.sale_date > c.m)) s2
)
select c.sale_id, c.item_id, c.quantity, c.m sale_date, c.balance_start, c.has_balance, c.reserved, c.balance_end
from cte c order by c.item_id, c.m