sqlite 樹型結構查詢

表結構：tblCity(ID, ParentID, Name)

因為sqlite 沒有row_number函數，也不能遞迴查詢，所幸它有RowID 這個欄位。只好採用這種 笨方法

1）

 

select ID,Name,1 as Level from tblCity where ParentID=0union all select a.ID,a.Name,c.RowID as Level from tblCity a inner join tblCity b on a.ParentID=b.IDinner join (      select ParentID from tblCity group by ParentID) c on a.ParentID=c.ParentID

2）

 

 

select ID,Name,1 as Level from tblCity where ParentID=0union all select a.ID,a.Name,c.RowID as Level from tblCity a inner join tblCity b on a.ParentID=b.IDinner join (      select ParentID from tblCity group by ParentID) c on a.ParentID=c.ParentID