如何使用根 ID 在 SQL Server 中的父子表中检索叶路径?
我处理在SQL Server中存储Tree的类型，它有一个特定的方法.在这个方法中，我们有两个这样的表:
位置表
LocationID |评论-----------+--------1 德黑兰2 阿扎迪街3 号 5
LocationParent 表
LocationParentID |家长 ID |儿童ID |深度-----------------+----------+---------+------1 1 1 02 1 2 13 1 3 24 2 2 15 2 3 26 3 3 2我希望有这样的结果:
LocationID |地址-----------+--------------------------------3 德黑兰 >阿扎迪圣 >5号在ParentID 和ChildID 中将存储LocatioID.我想知道如何通过一个查询检索根到子路径.如果我们有:City >街 >胡同 >数字 6.每一个都有一个单独的locationID，例如city在位置表中有一行，依此类推.
现在我会有整个地址的列表?
有什么简单的解决办法吗??
我不完全了解您的需求，我怀疑您提供的结构是否是最好的方法.查看以下 递归 CTE 示例.您可以放置过滤器以仅获取所需的行:
DECLARE @LocType TABLE(LocTypeID INT,LocType VARCHAR(100));INSERT INTO @LocType VALUES(1,'Country'),(2,'县'),(3,'城市'),(4,'街道'),(5,'房子');声明@mockup TABLE(LocationID INT,ParentID INT,LocTypeId INT,Value VARCHAR(250));插入@mockup 值(1,NULL,1,'美国'),(2,1,3,'纽约'),(3,2,4,'路1'),(4,2,4,'路2'),(5,2,4,'3号公路'),(6,4,5,'路2中的房子1'),(7,4,5,'Rouad 2 的 House 2'),(8,NULL,1,'德国'),(9,8,3,'柏林'),(10,9,4,'广场1'),(11,9,4,'广场 2'),(13,10,5,'Platz 1 的房子');使用recCTE AS(SELECT m.LocationID,m.ParentID,m.LocTypeID,m.Value,1 AS Lvl,CAST(m.Value AS NVARCHAR(MAX)) AS LocPath来自@mockup AS m其中 m.ParentID 为空联合所有选择 m.LocationID,m.ParentID,m.LocTypeID,m.Value,r.Lvl + 1,r.LocPath + ' >' + CAST(m.Value AS NVARCHAR(MAX))来自@mockup AS mINNER JOIN recCTE AS r ON m.ParentID=r.LocationID)选择 * 从 recCTE;结果
+------------+---------+-----------+--------------------+-----+-----------------------------------------------+|位置 ID |家长 ID |位置类型 ID |价值 |等级 |位置路径 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|1 |空 |1 |美国 |1 |美国 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|8 |空 |1 |德国 |1 |德国 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|9 |8 |3 |柏林 |2 |德国 >柏林 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|10 |9 |4 |广场 1 |3 |德国 >柏林 >广场 1 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|11 |9 |4 |广场 2 |3 |德国 >柏林 >广场 2 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|13 |10 |5 |房子在 Platz 1 |4 |德国 >柏林 >广场 1 >房子在 Platz 1 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|2 |1 |3 |纽约 |2 |美国>纽约 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|3 |2 |4 |路1 |3 |美国>纽约 >路1 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|4 |2 |4 |路2 |3 |美国>纽约 >路2 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|5 |2 |4 |路3 |3 |美国>纽约 >路3 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|6 |4 |5 |2号路1号楼|4 |美国 >纽约 >路 2 >2号路1号楼|+------------+------------+------------+-------------------+-----+-----------------------------------------------+|7 |4 |5 |位于 Rouad 2 的 House 2 |4 |美国>纽约 >路 2 >位于 Rouad 2 的 House 2 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+您存储的LocationID(例如作为一个人的address)是最详细的部分(叶节点)
您可以轻松地将此逻辑转换为自下而上(从叶子开始)
您可以通过查找所有条目来找到叶子，其中 LocationID 未作为 ParentID 找到.
更新我为你改变了...
更新 2 添加深度
检查这个
WITH recCTE AS(SELECT m.LocationID AS LeafID,m.LocTypeId,m.LocationID,m.ParentID,m.Value,CAST(m.Value AS NVARCHAR(MAX)) AS LocPath,1 AS 深度来自@mockup AS m哪里不存在(从@mockup AS x WHERE x.ParentID=m.LocationID 中选择 1)联合所有选择 r.LeafID,r.LocTypeId,m.LocationID,m.ParentID,m.Value,CAST(m.Value AS NVARCHAR(MAX)) + ' >' + r.LocPath,r.深度+1来自@mockup AS mINNER JOIN recCTE AS r ON m.LocationID=r.ParentID)SELECT LeafID,LocTypeId,LocPath,Depth从 recCTE哪里 ParentID 为空；结果
+--------+-----------+-----------------------------------------------+-------+|叶 ID |位置类型 ID |位置路径 |深度 |+--------+-----------+-----------------------------------------------+-------+|13 |5 |德国 >柏林 >广场 1 >房子在 Platz 1 |4 |+--------+-----------+-----------------------------------------------+-------+|11 |4 |德国 >柏林 >广场 2 |3 |+--------+-----------+-----------------------------------------------+-------+|7 |5 |美国>纽约 >路 2 >位于 Rouad 2 的 House 2 |4 |+--------+-----------+-----------------------------------------------+-------+|6 |5 |美国>纽约 >路 2 >2号路1号楼|4 |+--------+-----------+-----------------------------------------------+-------+|5 |4 |美国>纽约 >路3 |3 |+--------+-----------+-----------------------------------------------+-------+|3 |4 |美国>纽约 >路1 |3 |+--------+-----------+-----------------------------------------------+-------+I cope with type of storing Tree in SQL Server which has a specific method. In this method we have two table like this:
Location Table
LocationID | Remark
-----------+--------
1 Tehran
2 Azadi St
3 Number5
LocationParent Table
LocationParentID | ParentID | ChildID | Depth
-----------------+----------+---------+------
1 1 1 0
2 1 2 1
3 1 3 2
4 2 2 1
5 2 3 2
6 3 3 2
I desire have result like this:
LocationID | Address
-----------+--------------------------------
3 Tehran > Azadi St > Number5
In ParentID and ChildID will store LocatioID. I wonder how can I retrieve root to child path with one query. I should say these tables maintain adresses for exmaple if we have : City > Street > Alley > Number 6.
Each of these has a separated locationID for instance city has one row in Location Table and so on.
Now I would have list of whole adresses?
Is there any simple solution??
I do not fully understand your needs and I doubt, that the structure you provide is the best approach. Have a look at the following example of a recursive CTE. You can place a filter to get only the needed row:
DECLARE @LocType TABLE(LocTypeID INT,LocType VARCHAR(100));
INSERT INTO @LocType VALUES(1,'Country')
,(2,'County')
,(3,'City')
,(4,'Street')
,(5,'House');
DECLARE @mockup TABLE(LocationID INT,ParentID INT,LocTypeId INT,Value VARCHAR(250));
INSERT INTO @mockup VALUES
(1,NULL,1,'USA')
,(2,1,3,'New York')
,(3,2,4,'Road 1')
,(4,2,4,'Road 2')
,(5,2,4,'Road 3')
,(6,4,5,'House 1 in Road 2')
,(7,4,5,'House 2 in Rouad 2')
,(8,NULL,1,'Germany')
,(9,8,3,'Berlin')
,(10,9,4,'Platz 1')
,(11,9,4,'Platz 2')
,(13,10,5,'House in Platz 1');
WITH recCTE AS
(
SELECT m.LocationID,m.ParentID,m.LocTypeID,m.Value,1 AS Lvl,CAST(m.Value AS NVARCHAR(MAX)) AS LocPath
FROM @mockup AS m
WHERE m.ParentID IS NULL
UNION ALL
SELECT m.LocationID,m.ParentID,m.LocTypeID,m.Value
,r.Lvl + 1
,r.LocPath + ' > ' + CAST(m.Value AS NVARCHAR(MAX))
FROM @mockup AS m
INNER JOIN recCTE AS r ON m.ParentID=r.LocationID
)
SELECT * FROM recCTE;
The result
+------------+----------+-----------+--------------------+-----+-----------------------------------------------+
| LocationID | ParentID | LocTypeID | Value | Lvl | LocPath |
+------------+----------+-----------+--------------------+-----+-----------------------------------------------+
| 1 | NULL | 1 | USA | 1 | USA |
+------------+----------+-----------+--------------------+-----+-----------------------------------------------+
| 8 | NULL | 1 | Germany | 1 | Germany |
+------------+----------+-----------+--------------------+-----+-----------------------------------------------+
| 9 | 8 | 3 | Berlin | 2 | Germany > Berlin |
+------------+----------+-----------+--------------------+-----+-----------------------------------------------+
| 10 | 9 | 4 | Platz 1 | 3 | Germany > Berlin > Platz 1 |
+------------+----------+-----------+--------------------+-----+-----------------------------------------------+
| 11 | 9 | 4 | Platz 2 | 3 | Germany > Berlin > Platz 2 |
+------------+----------+-----------+--------------------+-----+-----------------------------------------------+
| 13 | 10 | 5 | House in Platz 1 | 4 | Germany > Berlin > Platz 1 > House in Platz 1 |
+------------+----------+-----------+--------------------+-----+-----------------------------------------------+
| 2 | 1 | 3 | New York | 2 | USA > New York |
+------------+----------+-----------+--------------------+-----+-----------------------------------------------+
| 3 | 2 | 4 | Road 1 | 3 | USA > New York > Road 1 |
+------------+----------+-----------+--------------------+-----+-----------------------------------------------+
| 4 | 2 | 4 | Road 2 | 3 | USA > New York > Road 2 |
+------------+----------+-----------+--------------------+-----+-----------------------------------------------+
| 5 | 2 | 4 | Road 3 | 3 | USA > New York > Road 3 |
+------------+----------+-----------+--------------------+-----+-----------------------------------------------+
| 6 | 4 | 5 | House 1 in Road 2 | 4 | USA > New York > Road 2 > House 1 in Road 2 |
+------------+----------+-----------+--------------------+-----+-----------------------------------------------+
| 7 | 4 | 5 | House 2 in Rouad 2 | 4 | USA > New York > Road 2 > House 2 in Rouad 2 |
+------------+----------+-----------+--------------------+-----+-----------------------------------------------+
The LocationID you store (e.g. as the address of a person) is the most detailled part (the leaf-node)
You can easily turn this logic to bottom-up (start off with the leafs)
You find a leaf by looking for all entries, which LocationID is not found as a ParentID.
UPDATE I turned it around for you...
UPDATE 2 Added Depth
Check this
WITH recCTE AS
(
SELECT m.LocationID AS LeafID,m.LocTypeId
,m.LocationID,m.ParentID,m.Value
,CAST(m.Value AS NVARCHAR(MAX)) AS LocPath
,1 AS Depth
FROM @mockup AS m
WHERE NOT EXISTS(SELECT 1 FROM @mockup AS x WHERE x.ParentID=m.LocationID)
UNION ALL
SELECT r.LeafID,r.LocTypeId
,m.LocationID,m.ParentID,m.Value
,CAST(m.Value AS NVARCHAR(MAX)) + ' > ' + r.LocPath
,r.Depth +1
FROM @mockup AS m
INNER JOIN recCTE AS r ON m.LocationID=r.ParentID
)
SELECT LeafID,LocTypeId,LocPath,Depth
FROM recCTE
WHERE ParentID IS NULL;
The result
+--------+-----------+-----------------------------------------------+-------+
| LeafID | LocTypeId | LocPath | Depth |
+--------+-----------+-----------------------------------------------+-------+
| 13 | 5 | Germany > Berlin > Platz 1 > House in Platz 1 | 4 |
+--------+-----------+-----------------------------------------------+-------+
| 11 | 4 | Germany > Berlin > Platz 2 | 3 |
+--------+-----------+-----------------------------------------------+-------+
| 7 | 5 | USA > New York > Road 2 > House 2 in Rouad 2 | 4 |
+--------+-----------+-----------------------------------------------+-------+
| 6 | 5 | USA > New York > Road 2 > House 1 in Road 2 | 4 |
+--------+-----------+-----------------------------------------------+-------+
| 5 | 4 | USA > New York > Road 3 | 3 |
+--------+-----------+-----------------------------------------------+-------+
| 3 | 4 | USA > New York > Road 1 | 3 |
+--------+-----------+-----------------------------------------------+-------+
相关文章