分层 SQL 查询不返回级别

发布于 2024-09-02 07:35:32 字数 476 浏览 4 评论 0原文

我有一个典型的 SQL Server 分层查询:

WITH bhp AS (
   SELECT name, 0 AS level
   FROM dbo.BhpNode
   WHERE parent_id IS NULL
   UNION ALL
   SELECT a.name, level + 1
   FROM dbo.BhpNode a
   INNER JOIN dbo.BhpNode b
      ON b.bhp_node_id = a.parent_id )
  SELECT * FROM bhp

这似乎与我在网上找到的分层查询的各种示例相匹配,但由于某种原因它产生了此错误:

消息 207,第 16 级,状态 1,第 12 行
列名“level”无效。

我确信我错过了一些明显的东西,但我已经盯着它看太久了,看不到它。知道我哪里出错了吗?

I have a typical SQL Server hierarchical query:

WITH bhp AS (
   SELECT name, 0 AS level
   FROM dbo.BhpNode
   WHERE parent_id IS NULL
   UNION ALL
   SELECT a.name, level + 1
   FROM dbo.BhpNode a
   INNER JOIN dbo.BhpNode b
      ON b.bhp_node_id = a.parent_id )
  SELECT * FROM bhp

This seems to match the various examples of hierarchical queries I've found around the web, but for some reason it's producting this error:

Msg 207, Level 16, State 1, Line 12
Invalid column name 'level'.

I'm sure I'm missing something obvious, but I've stared at it too long to see it. Any idea where I'm going wrong?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

凝望流年 2024-09-09 07:35:32

您的查询不是递归的 - 您必须从递归 CTE 的第二部分内的 bhp 中进行选择。试试这个:

WITH bhp AS (
   SELECT *, 0 AS [level]
   FROM dbo.BhpNode
   WHERE parent_id IS NULL
   UNION ALL
   SELECT b.*, [level] + 1
   FROM bhp a
   INNER JOIN dbo.BhpNode b
   ON a.bhp_node_id = b.parent_id)
SELECT * FROM bhp

Your query isn't recursive - you have to select from bhp inside the second part of the recursive CTE. Try this instead:

WITH bhp AS (
   SELECT *, 0 AS [level]
   FROM dbo.BhpNode
   WHERE parent_id IS NULL
   UNION ALL
   SELECT b.*, [level] + 1
   FROM bhp a
   INNER JOIN dbo.BhpNode b
   ON a.bhp_node_id = b.parent_id)
SELECT * FROM bhp
千紇 2024-09-09 07:35:32

在 CTE 的递归部分中,您引用的表之一应该是 CTE 本身,不是吗?目前,您只是自连接 BhpNode,它本身没有 level 列。

In the recursive section of the CTE, one of the tables you reference should be the CTE itself, shouldn't it? At the moment you are just self-joining BhpNode, and it doesn't have a level column itself.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文