Shulou(Shulou.com)06/03 Report--

Since SQL Server 2005, we can support recursive queries directly through CTE, and CTE is the common table expression.

Baidu Encyclopedia

The common table expression (CTE), a temporary named result set defined in the query, will be used in the from clause. Each CTE is defined only once (but can be referenced any number of times within its scope) and will survive during the lifetime of the query. You can use CTE to perform recursive operations. The syntax to create is:

With ()

As (

)

Select * from

Generate data

-- menu directory structure table create table tb_menu (id int not null,-- primary key idtitle varchar (50),-- title parent int-- parent id);-- parent menu insert into tb_menu (id, title, parent) values (1, 'parent menu 1); insert into tb_menu (id, title, parent) values (2,' parent menu 2); insert into tb_menu (id, title, parent) values (3, 'parent menu 3) Insert into tb_menu (id, title, parent) values; insert into tb_menu (id, title, parent) values;-- first-level menus insert into tb_menu (id, title, parent) values (6, 'first-level menus 6); insert into tb_menu (id, title, parent) values (7,' first-level menus 7 minutes 1) Insert into tb_menu (id, title, parent) values (8, 'first-level menus 8); insert into tb_menu (id, title, parent) values (9,' level-1 menus 9); insert into tb_menu (id, title, parent) values (10, 'level-1 menus 10); insert into tb_menu (id, title, parent) values (11,' level-1 menus 11) Insert into tb_menu (id, title, parent) values (12, 'first-level menus 12); insert into tb_menu (id, title, parent) values (13,' level-1 menus 13); insert into tb_menu (id, title, parent) values (14, 'level-1 menus 14); insert into tb_menu (id, title, parent) values (15,' level-1 menus 15) Insert into tb_menu (id, title, parent) values (16, 'level-1 menus 16); insert into tb_menu (id, title, parent) values (17,' level-1 menus 17); insert into tb_menu (id, title, parent) values (18, 'level-1 menus 18); insert into tb_menu (id, title, parent) values (19,' level-1 menus 19) Insert into tb_menu (id, title, parent) values (20, 'first-level menus 20);-- second-level menus insert into tb_menu (id, title, parent) values (21,' second-level menus 21); insert into tb_menu (id, title, parent) values (22, 'second-level menus 22); insert into tb_menu (id, title, parent) values (23,' second-level menus 23) Insert into tb_menu (id, title, parent) values (24, 'second-level menus 24); insert into tb_menu (id, title, parent) values (25,' second-level menus 25); insert into tb_menu (id, title, parent) values (26, 'second-level menus 26); insert into tb_menu (id, title, parent) values (27,' second-level menus 27) Insert into tb_menu (id, title, parent) values (28,'id, title, parent) values (29, 'second-level menus 29); insert into tb_menu (id, title, parent) values (30,' second-level menus 30 minutes 13); insert into tb_menu (id, title, parent) values (31, 'second-level menus 31) Insert into tb_menu (id, title, parent) values (32, 'second-level menus 32); insert into tb_menu (id, title, parent) values (33,' second-level menus 33); insert into tb_menu (id, title, parent) values (34, 'second-level menus 34); insert into tb_menu (id, title, parent) values (35,' second-level menus 35) Insert into tb_menu (id, title, parent) values (36, 'second-level menus 36 minutes, 19); insert into tb_menu (id, title, parent) values (37,' second-level menus 37);-- three-level menus insert into tb_menu (id, title, parent) values (38, 'three-level menus 38 minutes 21); insert into tb_menu (id, title, parent) values (39,' three-level menus 39 minutes, 22) Insert into tb_menu (id, title, parent) values (40, 'three-level menus 40); insert into tb_menu (id, title, parent) values (41,' three-level menus 41); insert into tb_menu (id, title, parent) values (42, 'three-level menus 42); insert into tb_menu (id, title, parent) values (43,' three-level menus 43) Insert into tb_menu (id, title, parent) values (44, 'three-level menus 44); insert into tb_menu (id, title, parent) values (45,' three-level menus 45); insert into tb_menu (id, title, parent) values (46, 'three-level menus 46); insert into tb_menu (id, title, parent) values (47,' three-level menus 47) Insert into tb_menu (id, title, parent) values (48, 'three-level menus 48); insert into tb_menu (id, title, parent) values (49,' three-level menus 49); insert into tb_menu (id, title, parent) values (50, 'three-level menus 50 minutes, 31); commit

two。 Find all parent nodes

-- query all root nodes of a node (44) in a tree structure with cte_parent (id,title,parent) as (--start condition select id,title,parent from tb_menu where id = 44-- list child node query condition union all-- Recursive condition select a.idmage a.titlematia.parent from tb_menu an inner join cte_parent b-- perform recursion It's time to understand on a.id=b.parent) select * from cte_parent

3. Find subordinate nodes with level

-- query all child nodes (with cte_child (id,title,parent,level) as) under a node in the tree structure (--start condition select id,title,parent,0 as level from tb_menu where id = 6-color-list parent node query condition union all-- Recursive condition select a.id.title.parent) B.level+1 from tb_menu an inner join cte_child b on (a.parent=b.id)) select * from cte_child

Reference article

CTE Recursive query http://www.cnblogs.com/xfrog/archive/2010/10/10/1847462.html in SQL Server 2008

Oracle tree operation (start with … Connect by... Prior) http://www.cnblogs.com/linjiqin/archive/2013/06/24/3152674.html

A simple re-summary of the design of "infinite pole" classified data table http://www.cnblogs.com/jeffwongishandsome/archive/2010/10/26/1861633.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.