问题描述
我处理在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 | +--------+-----------+-----------------------------------------------+-------+