无临时表的递归sql语句

e0bqpujr  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(322)

我有一个工作递归sql语句。由于我的系统中有一些限制,我需要重新格式化它,使其以“select count”开头。
该语句背后的思想是从树中的某个点导航,以找到该点下所有分支的所有端点。
如果你有个主意那就太好了。
这是我的声明:

WITH temp_Parent_ID (Parent_productgroup_id, Productgroup_id) AS    
(
    SELECT 
        Parent_productgroup_id, Productgroup_id 
    FROM 
        VW_PRODUCTGROUP_HIERARCHY 
    WHERE 
        Productgroup_id = 26976

    UNION ALL 

    SELECT 
        VW.Parent_productgroup_id, VW.Productgroup_id
    FROM 
        VW_PRODUCTGROUP_HIERARCHY VW    
    INNER JOIN 
        temp_Parent_ID temp ON VW.Parent_productgroup_id = temp.Productgroup_id
)
SELECT 
    COUNT(ae.element_ID) 
FROM 
    temp_Parent_ID temp 
INNER JOIN 
    VW_PRODUCTS_HIERARCHY VW ON temp.Productgroup_id = VW.PRODUCTGROUP_ID
INNER JOIN
    VW_PRODUCTS_ASSIGNED_E ae ON VW.PRODUCTVARIANT_ID = ae.PRODUCT_ID
INNER JOIN 
    VW_CATEGORY_ELEMENTS ce ON ae.ELEMENT_ID = ce.ELEMENT_ID
WHERE
    ce.CATEGORY_MASTER_NAME LIKE 'Technische Zeichnung (DWG)'
    AND ce.CATEGORY_LANGUAGE LIKE 'da'
kpbpu008

kpbpu0081#

你需要 WITH 子句,因为您需要一个递归查询。一 WITH 子句不能移动到sql server查询中的其他位置。这是特定于sql server的限制,因此不可能执行此操作:

select *
from ( with x as (select 1 as col) select * from x ) t;

WITH 子句是一个特殊的视图。因此,对于sql server,我看到的唯一选择是将其设置为普通视图:

CREATE VIEW view_products AS
WITH temp_Parent_ID (Parent_productgroup_id, Productgroup_id) AS    
(
    SELECT 
        Parent_productgroup_id, Productgroup_id 
    FROM 
        VW_PRODUCTGROUP_HIERARCHY 
    WHERE 
        Productgroup_id = 26976

    UNION ALL 

    SELECT 
        VW.Parent_productgroup_id, VW.Productgroup_id
    FROM 
        VW_PRODUCTGROUP_HIERARCHY VW    
    INNER JOIN 
        temp_Parent_ID temp ON VW.Parent_productgroup_id = temp.Productgroup_id
)
select * from temp_Parent_ID;

然后在查询中使用此视图:

SELECT 
    COUNT(ae.element_ID) 
FROM 
    view_products
INNER JOIN 
    VW_PRODUCTS_HIERARCHY VW ON view_products.Productgroup_id = VW.PRODUCTGROUP_ID
INNER JOIN
    VW_PRODUCTS_ASSIGNED_E ae ON VW.PRODUCTVARIANT_ID = ae.PRODUCT_ID
INNER JOIN 
    VW_CATEGORY_ELEMENTS ce ON ae.ELEMENT_ID = ce.ELEMENT_ID
WHERE
    ce.CATEGORY_MASTER_NAME LIKE 'Technische Zeichnung (DWG)'
    AND ce.CATEGORY_LANGUAGE LIKE 'da'

相关问题