不知道是不是这样 SELECT A.* FROM TCity A JOIN (SELECT MIN(CityID)CityID1,MAX(CityID)CityID2 FROM TManagerArea WHERE LoginID='888888') B ON A.CityID>=B.CityID1 AND A.CityID<=B.CityID2
with cte as (select * from tcity where exists (select 1 from tmanagerarea where tcity.cityid =tmanagerarea.cityid) union all select a.* from tcity as a join cte as b on a.parentid=b.cityid and a.parentid!=0) select * from cte order by cityid --结果 CityID AreaName ParentID ----------- ------------------------------ ----------- 1001 广东省 1000 1002 深圳市 1001 1003 东莞市 1001 1004 南山区 1002 1007 贵港市 1005 1008 平南县 1007 1009 大新镇 1008 1010 桂平市 1007(8 行受影响)
修改如下, ;WITH CTE AS( SELECT CityID,AreaName,ParentID,CAST(CityID AS VARCHAR(8000))[PATH]FROM TCity WHERE ParentID=0 UNION ALL SELECT A.*,B.[PATH]+'-'+CAST(A.CityID AS VARCHAR(8000)) FROM TCity A JOIN CTE B ON A.ParentID=B.CityID ) SELECT A.CityID,A.AreaName FROM CTE A JOIN(SELECT B.*,C.[PATH]FROM TManagerArea B JOIN CTE C ON B.CityID=C.CityID)D ON A.[PATH]LIKE D.[PATH]+'%' WHERE D.LoginID=888888 ORDER BY A.CityID
你的数据是对了,可是奇怪,怎么没有让输入LOGINID=888888
你的数据是对了,可是奇怪,怎么没有让输入LOGINID=888888我的可以吗~~
你表里面数据LOGININ都是888888,这个条件加不加都是一样。如果表里面还有一条数据。LOGININ 不是88888, 我们不选择这一条的下级。 with cte as (select * from tcity where exists (select 1 from tmanagerarea where tcity.cityid =tmanagerarea.cityid and tmangaerarea=888888) union all select a.* from tcity as a join cte as b on a.parentid=b.cityid and a.parentid!=0) select * from cte order by cityid 这样加一句就OK 了。
SELECT
A.*
FROM
TCity A
JOIN (SELECT MIN(CityID)CityID1,MAX(CityID)CityID2 FROM TManagerArea WHERE LoginID='888888') B ON A.CityID>=B.CityID1 AND A.CityID<=B.CityID2
如果是 (1001,'广东省') 到 (1007,'贵港市')的路径?为什么还包括(1008,'平南县')、(1009,'大新镇')?为什么同样的下级(1010,'桂平市')不算?
给定 LoginID = 888888,如果在 TManagerArea 不是2条记录,怎么利用它们?
with cte as
(select * from tcity
where exists (select 1 from tmanagerarea
where tcity.cityid =tmanagerarea.cityid)
union all
select a.* from tcity as a join cte as b
on a.parentid=b.cityid and a.parentid!=0)
select * from cte
order by cityid
--结果
CityID AreaName ParentID
----------- ------------------------------ -----------
1001 广东省 1000
1002 深圳市 1001
1003 东莞市 1001
1004 南山区 1002
1007 贵港市 1005
1008 平南县 1007
1009 大新镇 1008
1010 桂平市 1007(8 行受影响)
;WITH CTE AS(
SELECT CityID,AreaName,ParentID,CAST(CityID AS VARCHAR(8000))[PATH]FROM TCity WHERE ParentID=0
UNION ALL
SELECT A.*,B.[PATH]+'-'+CAST(A.CityID AS VARCHAR(8000)) FROM TCity A JOIN CTE B ON A.ParentID=B.CityID
)
SELECT
A.CityID,A.AreaName
FROM
CTE A
JOIN(SELECT B.*,C.[PATH]FROM TManagerArea B JOIN CTE C ON B.CityID=C.CityID)D ON A.[PATH]LIKE D.[PATH]+'%'
WHERE
D.LoginID=888888
ORDER BY
A.CityID
你的数据是对了,可是奇怪,怎么没有让输入LOGINID=888888
你的数据是对了,可是奇怪,怎么没有让输入LOGINID=888888我的可以吗~~
我们不选择这一条的下级。
with cte as
(select * from tcity
where exists (select 1 from tmanagerarea
where tcity.cityid =tmanagerarea.cityid and tmangaerarea=888888)
union all
select a.* from tcity as a join cte as b
on a.parentid=b.cityid and a.parentid!=0)
select * from cte
order by cityid
这样加一句就OK 了。