需求是查询出相应区域ID、名称、父区域、级别、是否叶结点,现在区域表只有区域ID和名称,下面是部分截图:
需要在查询时添加父区域、级别、是否叶结点,三个字段,下面是查询语句:
select t.RegionID,t.RegionName
,rLen/2 as level
,(case when rLen in(5,6) then 1 else 0 end) as isLeaf
,(case when rLen=2 then ''
when rLen in(3,4) then convert(varchar(2),t.RegionID,120)+'0000'
when rLen in(5,6) then convert(varchar(4),t.RegionID,120)+'00'
end) as pid
from(select *,len(cast(reverse(RegionID) as int)) as rLen
from Region
where RegionID like '15%'
) as t
查询结果: