本文介紹了如何使用根 ID 在 SQL Server 中的父子表中檢索葉路徑?的處理方法，對(duì)大家解決問題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！
問題描述
我處理在SQL Server中存儲(chǔ)Tree的類型，它有一個(gè)特定的方法.在這個(gè)方法中，我們有兩個(gè)這樣的表:
位置表
LocationID |評(píng)論-----------+--------1 德黑蘭2 阿扎迪街3 號(hào) 5
LocationParent 表
LocationParentID |家長(zhǎng) ID |兒童ID |深度-----------------+----------+---------+------1 1 1 02 1 2 13 1 3 24 2 2 15 2 3 26 3 3 2我希望有這樣的結(jié)果:
LocationID |地址-----------+--------------------------------3 德黑蘭 >阿扎迪圣 >5號(hào)在ParentID 和ChildID 中將存儲(chǔ)LocatioID.我想知道如何通過一個(gè)查詢檢索根到子路徑.如果我們有:City >街 >胡同 >數(shù)字 6.每一個(gè)都有一個(gè)單獨(dú)的locationID，例如city在位置表中有一行，依此類推.
現(xiàn)在我會(huì)有整個(gè)地址的列表?
有什么簡(jiǎn)單的解決辦法嗎??
解決方案
我不完全了解您的需求，我懷疑您提供的結(jié)構(gòu)是否是最好的方法.查看以下 遞歸 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,'美國(guó)'),(2,1,3,'紐約'),(3,2,4,'路1'),(4,2,4,'路2'),(5,2,4,'3號(hào)公路'),(6,4,5,'路2中的房子1'),(7,4,5,'Rouad 2 的 House 2'),(8,NULL,1,'德國(guó)'),(9,8,3,'柏林'),(10,9,4,'廣場(chǎng)1'),(11,9,4,'廣場(chǎng) 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 為空聯(lián)合所有選擇 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;結(jié)果
+------------+---------+-----------+--------------------+-----+-----------------------------------------------+|位置 ID |家長(zhǎng) ID |位置類型 ID |價(jià)值 |等級(jí) |位置路徑 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|1 |空 |1 |美國(guó) |1 |美國(guó) |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|8 |空 |1 |德國(guó) |1 |德國(guó) |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|9 |8 |3 |柏林 |2 |德國(guó) >柏林 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|10 |9 |4 |廣場(chǎng) 1 |3 |德國(guó) >柏林 >廣場(chǎng) 1 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|11 |9 |4 |廣場(chǎng) 2 |3 |德國(guó) >柏林 >廣場(chǎng) 2 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|13 |10 |5 |房子在 Platz 1 |4 |德國(guó) >柏林 >廣場(chǎng) 1 >房子在 Platz 1 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|2 |1 |3 |紐約 |2 |美國(guó)>紐約 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|3 |2 |4 |路1 |3 |美國(guó)>紐約 >路1 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|4 |2 |4 |路2 |3 |美國(guó)>紐約 >路2 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|5 |2 |4 |路3 |3 |美國(guó)>紐約 >路3 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+|6 |4 |5 |2號(hào)路1號(hào)樓|4 |美國(guó) >紐約 >路 2 >2號(hào)路1號(hào)樓|+------------+------------+------------+-------------------+-----+-----------------------------------------------+|7 |4 |5 |位于 Rouad 2 的 House 2 |4 |美國(guó)>紐約 >路 2 >位于 Rouad 2 的 House 2 |+------------+------------+------------+-------------------+-----+-----------------------------------------------+您存儲(chǔ)的LocationID(例如作為一個(gè)人的address)是最詳細(xì)的部分(葉節(jié)點(diǎn))
您可以輕松地將此邏輯轉(zhuǎn)換為自下而上(從葉子開始)
您可以通過查找所有條目來找到葉子，其中 LocationID 未作為 ParentID 找到.
更新我為你改變了...
更新 2 添加深度
檢查這個(gè)
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)聯(lián)合所有選擇 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 為空；結(jié)果
+--------+-----------+-----------------------------------------------+-------+|葉 ID |位置類型 ID |位置路徑 |深度 |+--------+-----------+-----------------------------------------------+-------+|13 |5 |德國(guó) >柏林 >廣場(chǎng) 1 >房子在 Platz 1 |4 |+--------+-----------+-----------------------------------------------+-------+|11 |4 |德國(guó) >柏林 >廣場(chǎng) 2 |3 |+--------+-----------+-----------------------------------------------+-------+|7 |5 |美國(guó)>紐約 >路 2 >位于 Rouad 2 的 House 2 |4 |+--------+-----------+-----------------------------------------------+-------+|6 |5 |美國(guó)>紐約 >路 2 >2號(hào)路1號(hào)樓|4 |+--------+-----------+-----------------------------------------------+-------+|5 |4 |美國(guó)>紐約 >路3 |3 |+--------+-----------+-----------------------------------------------+-------+|3 |4 |美國(guó)>紐約 >路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 |
+--------+-----------+-----------------------------------------------+-------+
這篇關(guān)于如何使用根 ID 在 SQL Server 中的父子表中檢索葉路徑?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！
【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請(qǐng)聯(lián)系我們刪除處理，感謝您的支持！