Home > Database > Mysql Tutorial > body text

sqlite树型结构查询

WBOY
Release: 2016-06-07 14:58:21
Original
1901 people have browsed it

sqlite树型结构查询 表结构:tblCity(ID, ParentID, Name) 因为sqlite 没有row_number函数,也不能递归查询,所幸它有RowID 这个字段。只好采用这种 笨方法 1) [sql] select ID,Name,1 as Level from tblCity where ParentID=0 union all select a.ID,a.Nam

sqlite树型结构查询

 

表结构:tblCity(ID, ParentID, Name)

 

因为sqlite 没有row_number函数,也不能递归查询,所幸它有RowID 这个字段。只好采用这种 笨方法

 

1)

 

[sql] 

select ID,Name,1 as Level from tblCity where ParentID=0  

union all   

select a.ID,a.Name,c.RowID as Level from tblCity a   

inner join tblCity b on a.ParentID=b.ID  

inner join   

(  

      select ParentID from tblCity group by ParentID  

) c on a.ParentID=c.ParentID  

 

2)

[sql] 

select ID,Name,1 as Level from tblCity where ParentID=0  

union all   

select a.ID,a.Name,c.RowID as Level from tblCity a   

inner join tblCity b on a.ParentID=b.ID  

inner join   

(  

      select ParentID from tblCity group by ParentID  

) c on a.ParentID=c.ParentID  

 

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template