Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
3.1k views
in Technique[技术] by (71.8m points)

common table expression - Cleaner SQL CTE than the current one

Currently I use the following CTE to grab a category and any category below it:

WITH RECURSIVE tree AS (
SELECT * FROM (
    SELECT categoryId, 
          categoryName, 
          categoryParentId,
          categoryDescr,
          categoryType,
          categoryDC,
          categoryLedgerId,
          1 as categoryDepth
    FROM tbl_categories
    WHERE categoryId = '.$categoryId.'
    UNION
    SELECT categoryId, 
          categoryName, 
          categoryParentId,
          categoryDescr,
          categoryType,
          categoryDC,
          categoryLedgerId,
          1 as categoryDepth
    FROM tbl_categories_custom
    WHERE categoryId = '.$categoryId.'
) AS combined

UNION ALL

SELECT p.categoryId,
        p.categoryName,
        p.categoryParentId,
        p.categoryDescr,
        p.categoryType,
        p.categoryDC,
        p.categoryLedgerId, 
        t.categoryDepth + 1
        FROM (
           SELECT * FROM tbl_categories
           UNION
           SELECT * FROM tbl_categories_custom
        ) AS p
        JOIN tree t ON t.categoryId = p.categoryParentId
)
SELECT *
FROM tree

However, as @trincot showed me in Union two tables with categories in a query that retrieves categories and its parents it can be done much cleaner.

His version over there grabs a category and any category above it.

This one should do it the opposite way; grab any category and any category below it. Which it does. But it seems overcomplicated now that I read his other version.

How to simplify this CTE?


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Indeed, you can make this shorter by first making a common table expression for the union, and only then doing the recursive one.

The query is quite similar to the one posted in the other question. The major change is that the join condition in the inner join ... on clause is now reversed. The = 1 part is where you would do the comparison with your PHP variable:

with recursive 
base as (
    select * from tbl_categories
    union
    select * from tbl_categories_custom
),
cte as (
    select 1 as categoryDepth,
           base.* 
    from   base
    where  categoryId = 1
    union
    select cte.categoryDepth + 1, 
           base.*
    from   cte
    inner join base
            on cte.categoryId = base.categoryParentId
)
select   *
from     cte;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...