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)));
}