树形结构之父子节点查询

时间:2022-10-28 08:08:17

2022年10月27日 20:49:25

1. SQL父子节点查询

  1. 这里可以参照我之前关于CTE实现递归的用法:T-SQL——公用表表达式(CTE)

  2. 准备测试数据

--建立树形结构表
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
  1. 父查子
    查询某个区域的下级区域
    比如说,这里查询表中所有江苏省以下的行政区域
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                    吴中区

  1. 子查父
    查询某个区域的上级区域
    比如说,这里查询吴中区的上级行政区域
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对象中查找父子节点

  1. 准备测试数据
//模拟数据
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;
}
  1. 测试

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父子节点查询

  1. 准备测试数据
//树形表实体对象
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;
}
  1. 递归查询

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



3. 参加及源代码下载