樹形結構之父子節點查詢
2022年10月27日 20:49:25
1. SQL父子節點查詢
-
這裡可以參照我之前關於CTE實現遞歸的用法：T-SQL——公用表表達式(CTE)
-
準備測試數據
--建立樹形結構表
CREATE TABLE [dbo].[Region]
(
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[PId] [bigint] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
)
--關閉主鍵自增
SET IDENTITY_INSERT Region ON
INSERT INTO [dbo].[Region] ([Id], [PId], [Name])
VALUES
( 1, 0, N'中國' ),
( 2, 1, N'江蘇省' ),
( 3, 2, N'蘇州市' ),
( 4, 3, N'吳中區' ),
( 5, 1, N'山東省' ),
( 6, 5, N'濟南市' ),
( 7, 5, N'青島市' ),
( 8, 5, N'煙台市' ),
( 9, 2, N'南京市' ),
( 11, 9, N'玄武區' )
--批量插入完成 打開主鍵自增
SET IDENTITY_INSERT [dbo].[Region] OFF
- 父查子
查詢某個區域的下級區域
比如說，這裡查詢表中所有江蘇省以下的行政區域
WITH temp AS (SELECT * FROM Company WHERE Id=2 --江蘇省的Id是2，所以遞歸初始值就是2
UNION ALL
SELECT c.* FROM temp, Company AS c WHERE temp.Id=c.PId)
SELECT * FROM temp;
結果：
Id PId Name
-------------------- -------------------- --------------------------------------------------
2 1 江蘇省
3 2 蘇州市
9 2 南京市
11 9 玄武區
4 3 吳中區
- 子查父
查詢某個區域的上級區域
比如說，這裡查詢吳中區的上級行政區域
WITH temp AS (SELECT * FROM Region WHERE Id=4 --吳中區Id
UNION ALL
SELECT c.* FROM temp, dbo.Region AS c WHERE temp.PId=c.Id)
SELECT * FROM temp;
結果
Id PId Name
-------------------- -------------------- --------------------------------------------------
4 3 吳中區
3 2 蘇州市
2 1 江蘇省
1 0 中國
2. DataTable對象中查找父子節點
- 準備測試數據
//模擬數據
private DataTable GetDtRegion()
{
//建表
DataTable dtRegion = new DataTable("Region");
//建列
DataColumn dcId = new DataColumn("Id", typeof(int));
DataColumn dcPId = new DataColumn("PId", typeof(int));
DataColumn dcName = new DataColumn("Name", typeof(string));
DataColumn[] aryDc = { dcId, dcPId, dcName };
dtRegion.Columns.AddRange(aryDc);
//設置主鍵
//dcId.AllowDBNull = false;
//dtRegion.PrimaryKey = new DataColumn[] { dcId };
dtRegion.Rows.Add(new object[] { "1", "0", "中國" });
dtRegion.Rows.Add(new object[] { "2", "1", "江蘇省" });
dtRegion.Rows.Add(new object[] { "3", "2", "蘇州市" });
dtRegion.Rows.Add(new object[] { "4", "3", "吳中區" });
dtRegion.Rows.Add(new object[] { "5", "3", "山東省" });
dtRegion.Rows.Add(new object[] { "6", "5", "濟南市" });
dtRegion.Rows.Add(new object[] { "7", "5", "青島市" });
dtRegion.Rows.Add(new object[] { "8", "3", "煙台市" });
dtRegion.Rows.Add(new object[] { "9", "2", "南京市" });
dtRegion.Rows.Add(new object[] { "11", "9", "玄武區" });
return dtRegion;
}
- 測試
public void Test()
{
DataTable dtRegion = GetDtRegion();
DataSet ds = new DataSet();
ds.Tables.Add(dtRegion);
//DataSet的Relations屬性主要是用於建立主子表關係，這裡我們將一張樹形結構的表建立自連接
ds.Relations.Add("TreeRelation", ds.Tables[0].Columns["Id"], ds.Tables[0].Columns["PId"], false);
//獲取指定的節點的所有下一級子節點（注意根據我們的關聯關係，我們只能查找到一個節點的所有二級節點）
DataRow[] drSons = dtRegion.Select("Name='中國'")[0].GetChildRows("TreeRelation");
DataRow[] drParents = dtRegion.Select("Name='越溪街道'")[0].GetParentRows("TreeRelation");
//使用遞歸獲取江蘇省節點下的所有子節點
List<DataRow> listDr = GetSons(dtRegion.Select("Name='江蘇省'")[0]);
List<DataRow> listDra = GetParents(dtRegion.Select("Name='越溪街道'")[0]);
listDr.ForEach(n => Console.WriteLine($"Id:{n["Id"]},PId:{n["PId"]},Name:{n["Name"]}"));
Console.WriteLine("---------------");
listDra.ForEach(n => Console.WriteLine($"Id:{n["Id"]},PId:{n["PId"]},Name:{n["Name"]}"));
}
//廣度遍歷：獲取指定的父節點的所有層級的子節點
public static List<DataRow> GetSons(DataRow dr)
{
List<DataRow> drSons = dr.GetChildRows("TreeRelation").ToList();
List<DataRow> result = new List<DataRow>(drSons);
foreach (DataRow row in drSons)
{
result.AddRange(GetSons(row));
}
return result;
}
//獲取指定的節點的所有上級父節點
public static List<DataRow> GetParents(DataRow dr)
{
List<DataRow> drParents = dr.GetParentRows("TreeRelation").ToList();
List<DataRow> result = new List<DataRow>(drParents);
foreach (DataRow row in drParents)
{
result.AddRange(GetParents(row));
}
return result;
}
3. Linq父子節點查詢
- 準備測試數據
//樹形表實體對象
public class Region
{
public int Id { get; set; }
public int PId { get; set; }
public string Name { get; set; }
}
//模擬數據源
public static List<Region> GetListRegion()
{
List<Region> listRegion = new List<Region>()
{
new Region (){Id=1, PId=0,Name="中國" },
new Region (){Id=2, PId=1,Name="江蘇省" },
new Region (){Id=3, PId=2,Name="蘇州市" },
new Region (){Id=4, PId=3,Name="吳中區" },
new Region (){Id=5, PId=1,Name="山東省" },
new Region (){Id=6, PId=5,Name="濟南市" },
new Region (){Id=7, PId=5,Name="青島市" },
new Region (){Id=8, PId=5,Name="煙台市" },
new Region (){Id=9, PId=2,Name="南京市" },
new Region (){Id=11,PId=9,Name="玄武區" },
new Region (){Id=12,PId=4,Name="越溪街道"},
new Region (){Id=13,PId=4,Name="橫涇街道"}
};
return listRegion;
}
- 遞歸查詢
public void Test()
{
//使用Linq實現
List<Region> regions = GetSons(GetListRegion(), 5).ToList();
regions.ForEach(n => Console.WriteLine($"Id:{n.Id},PId:{n.PId},Name:{n.Name}"));
}
// 獲取指定的節點其所有的子節點（包含指定節點本身）
public static IEnumerable<Region> GetSonsWithSelf(IEnumerable<Region> list, int pId)
{
var query = list.Where(p => p.Id == pId).ToList();
return query.Concat(GetSons(list, pId));
}
//獲取指定的節點其所有的子節點
public static IEnumerable<Region> GetSons(IEnumerable<Region> list, int pId)
{
var query = list.Where(p => p.PId == pId).ToList();
return query.ToList().Concat(query.ToList().SelectMany(t => GetSons(list, t.Id)));
}
//獲取父節點
public static IEnumerable<Region> GetFatherList(IEnumerable<Region> list, int Id)
{
var query = list.Where(p => p.Id == Id).ToList();
return query.ToList().Concat(query.ToList().SelectMany(t => GetFatherList(list, t.PId)));
}
