SQL statement implementation tree query for classic instances-mysql tutorial

The SQL statement described below is very classic. This SQL statement implements the query of the child tree. This SQL statement can be directly executed in the query analyzer for your reference. -- Generate table createtableMENU (idint, mnamechar (50), parentint) -- insert data insertintoMENUselect1, news, Nullunionallselect2, real estate,

The SQL statement described below is very classic. This SQL statement implements the query of the child tree. This SQL statement can be directly executed in the query analyzer for your reference. -- Generate table createtableMENU (idint, mnamechar (50), parentint) -- insert data insertintoMENU select1, 'news', Nullunionall select2, 'House ',

The following describesSQL statementVeryClassic,SQL statement implementationDescendant treeQuery,SQL statementYou can directlyQueryAnalyzer for your reference.

-- Generate a table

Create table MENU (id int, mname char (50), parent int)

-- Insert data

Insert into MENU

Select 1, 'news', Null union all

Select 2, 'House', Null union all

Select 3, 'tech News ', 1 union all

Select 4, 'social News ', 1 union all

Select 5, 'It News', 3 union all

Select 6, 'Aerospace News ', 3

--ImplementationQuery news descendant tree

Declare @ s varchar (1000)

Select @ s = ',' + cast (id as varchar (20) + ''from MENU where id = 1

While @ rowCount> 0

-- Charindex: returns the starting position of the specified expression in the string.

Select @ s = @ s + ',' + cast (id as varchar) from MENU

Where charindex (',' + cast (id as varchar) + ',', @ s + ',') = 0

And charindex (',' + cast (parent as varchar) + ',', @ s + ',')> 0

Select * from MENU where charindex (',' + cast (id as varchar) + ',', @ s + ',')> 0

-- Delete a table

Drop table MENU