Home >Database >Mysql Tutorial >sqlite树型结构查询

sqlite树型结构查询

WBOY
WBOYOriginal
2016-06-07 14:58:211905browse

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  

 

Statement:
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