CTE SQL到JPA的简单子集查询 [英] cte sql to simple subset query for JPA

问题描述

如何将以下CTE查询(递归)更改为简单的子集sql:

How can we change below CTE query (recursive) to simple subset sql:

  WITH links (parent, child) AS
  ( SELECT parent, child
        FROM Heirarchy_Table
        WHERE parent = '111111'
   UNION ALL 
    SELECT ht.parent, ht.child
        FROM Heirarchy_Table ht
        INNER JOIN links ON links.child = ht.parent)
SELECT * FROM links fl;

我需要对JPA使用此查询，因为JPA不处理"WITH"子句.[注意:"links" cte是递归使用的].非常感谢！

I need to use this query for JPA as JPA is not handling "WITH" clause. [Note: "links" cte is used recursively]. Many Thanks!!

推荐答案

如何将以下CTE查询(递归)更改为简单的子集sql:

How can we change below CTE query (recursive) to simple subset sql:

创建视图.

create view links
as
WITH links (parent, child) AS
( SELECT parent, child
    FROM Heirarchy_Table
    WHERE parent = '111111'
UNION ALL 
SELECT ht.parent, ht.child
    FROM Heirarchy_Table ht
    INNER JOIN links ON links.child = ht.parent)
SELECT * FROM links fl;

然后您可以查询视图

select * from links

或者您可以使用内联表值函数，有时称为参数化视图"，如下所示:

Or you can use an inline table-valued function, sometimes called a "parameratized view", like this:

create or alter function GetLinks(@parent int)
returns table
as 
return
WITH links (parent, child) AS
(SELECT parent, child
    FROM Heirarchy_Table
    WHERE parent = @parent
UNION ALL 
SELECT ht.parent, ht.child
    FROM Heirarchy_Table ht
    INNER JOIN links ON links.child = ht.parent)
SELECT * FROM links fl;

您可以通过以下方式查询:

which you can query like:

select * from GetLinks(1111)

这篇关于CTE SQL到JPA的简单子集查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！