如何使用父级和子级层次结构选择标题,在SQL中显示“true”和“false”? [英] How to select title using parent and child hierarchy with visible 'true' and 'false' in SQL?
问题描述
我需要搜索标题'%p%'。如果我检查标题,父牌和状态作为父级和子级。如果搜索到的标题父级可见为真,则显示子标题。
CREATE TABLE CTE(
标题VARCHAR(20),
ParentTitle VARCHAR(20),
可见VARCHAR(20),
)
插入CTE
VALUES
('p1','Home','TRUE'),
('p1.1', 'p1','TRUE'),
('p1.2','p1','FALSE'),
('p1.3','p1 .2','TRUE'),
('p1.3.1','p1.3','TRUE'),
('p2','首页','TRUE'),
('p2.1','p2','TRUE'),
('p2.2','p2.1 ','FALSE'),
('P3','Home','TRUE'),
('p3.1','p3','TRUE '),
('P3.1.1','p3.1','FALSE')
我需要输出像
title
p1
p1。 1
p2
p2。 1
P3
p3.1
我的尝试:
I need to search title '%p%'. if i check title ,parent tile and status as parent and child level. if the searched title parent visible is true then show the child Title.
CREATE TABLE CTE(
Title VARCHAR(20),
ParentTitle VARCHAR(20),
visible VARCHAR(20),
)
INSERT INTO CTE
VALUES
('p1', 'Home', 'TRUE'),
('p1.1', 'p1', 'TRUE'),
('p1.2', 'p1', 'FALSE'),
('p1.3', 'p1.2', 'TRUE'),
('p1.3.1', 'p1.3', 'TRUE'),
('p2', 'Home', 'TRUE'),
('p2.1', 'p2', 'TRUE'),
('p2.2', 'p2.1', 'FALSE'),
('P3', 'Home', 'TRUE'),
('p3.1', 'p3', 'TRUE'),
('P3.1.1', 'p3.1', 'FALSE')
I need output like
title
p1
p1.1
p2
p2.1
P3
p3.1
What I have tried:
Select b.Title,b.ParentTitle from CTE B
left join CTE C ON C.ParentTitle=b.Title AND b.visible='TRue'
where b.ParentTitle in(Select a.ParentTitle from CTE A join CTE B on a.Title=b.ParentTitle and b.visible='false')
i tried this query. But i can't get correct answer..
推荐答案
您好,
您需要应用递归cte。尝试下面的脚本输出。
; tbl为(
选择标题,来自cte的ParentTitle其中ParentTitle ='Home'
union all
选择a.title,a.ParentTitle来自cte a inner join tbl b
a。 ParentTitle = b.title
其中a.visible ='true'
)
从tbl选择标题按标题排序
这里tbl是cte。
谢谢
Hi,
You need to apply recursive cte . Try below script for your output.
;with tbl as (
select title ,ParentTitle from cte where ParentTitle='Home'
union all
select a.title,a.ParentTitle from cte a inner join tbl b on
a.ParentTitle=b.title
where a.visible='true'
)
select title from tbl order by title
Here tbl is cte.
Thanks
这篇关于如何使用父级和子级层次结构选择标题,在SQL中显示“true”和“false”?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!