i应对具有特定方法的SQL Server中的Tree类型。在这种方法中，我们有两个这样的表：
位置表
LocationID | Remark
-----------+--------
1 Tehran
2 Azadi St
3 Number5
locationparent表
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
我希望有这样的结果：
LocationID | Address
-----------+--------------------------------
3 Tehran > Azadi St > Number5
在ParentID中，ChildID将存储LocatioID。我想知道如何通过一个查询将根回到儿童路径。如果我们有：City > Street > Alley > Number 6，我应该说这些表保持着exmaple的地址。这些中的每一个都有一个分离的 locationID，例如，城市在位置表中有一个行等等。
现在我会列出整个地址的清单吗？
有任何简单的解决方案??
我不完全理解您的需求，我怀疑您提供的结构是最佳方法。看看以下recursive CTE的示例。您可以放置一个过滤器以获取所需的行：
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;
结果
+------------+----------+-----------+--------------------+-----+-----------------------------------------------+
| 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 |
+------------+----------+-----------+--------------------+-----+-----------------------------------------------+
您存储的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 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;
结果
+--------+-----------+-----------------------------------------------+-------+
| 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 |
+--------+-----------+-----------------------------------------------+-------+