如何使用根 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     |
+--------+-----------+-----------------------------------------------+-------+

这篇关于如何使用根 ID 在 SQL Server 中的父子表中检索叶路径?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！