我正试图在子查询中执行一些递归,但我似乎无法使用WITH
- 我是否错过了某些内容或者是否有解决方法?
WHERE swn.stocknode_id in ( WITH Hierachy(stocknode_id, short_desc, PARENTNODE_ID, level) AS ( SELECT a.stocknode_id, 0 AS level FROM stock_website_node AS a WHERE a.short_desc = 'XXXXXXXX' AND a.PARENTNODE_ID = 0 UNION ALL SELECT a.stocknode_id, ch.level + 1 FROM stock_website_node AS a INNER JOIN Hierachy ch ON a.PARENTNODE_ID = ch.stocknode_id ) SELECT stocknode_id FROM Hierachy WHERE level > 0 )
我遇到了:
Incorrect syntax near the keyword 'WITH'.
公用表表达式必须位于顶层.
您需要从该WHERE
子句中取出CTE,并且只需WHERE
像这样重新引用stocknode_id :
WHERE swn.stocknode_id in (SELECT stocknode_id FROM Hierachy WHERE level > 0)