审查生成的租户订单查询

SQLite 3.45.1 高级 6分钟找出 4处问题

在这段生成的类别订单报表进入多租户生产数据库前，对它进行审查。

针对一个租户遍历类别子树，排除已停用账户，并按稳定顺序为每个类别返回恰好两笔已支付订单。结果只含 order_id、category_id、amount 和 position。

SQL

WITH RECURSIVE category_tree AS (
  SELECT id, parent_id, tenant_id
  FROM categories
  WHERE id = :root_id
  UNION
  SELECT c.id, c.parent_id, c.tenant_id
  FROM categories AS c
  JOIN category_tree AS t ON c.parent_id = t.id
),
eligible_orders AS (
  SELECT o.*
  FROM orders AS o
  WHERE o.category_id IN (SELECT id FROM category_tree)
    AND o.account_id NOT IN (SELECT account_id FROM blocked_accounts)
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY category_id
      ORDER BY amount DESC
    ) AS position
  FROM eligible_orders
)
SELECT *
FROM ranked WHERE position <= 2;

生成代码仅作示例，不代表任何特定模型

在试验场中打开

报告错误