27,579
社区成员
发帖
与我相关
我的任务
分享
protected void doAjax(object sender, EventArgs e)
{
string searchStr = "";
string Org_Pid = this.Org_Pid.Text.ToString().Trim();
string sqlstrall;
sqlstrall = "WITH CTE AS (\r\n"
+ "select t.Org_No,t.Org_Id from HR_Org t (NOLOCK)\r\n"
+ "where Org_id=" + Org_Pid + "\r\n"
+ "UNION ALL \r\n"
+ "SELECT t1.Org_No,t1.Org_Id FROM dbo.HR_Org t1 (NOLOCK)\r\n"
+ "INNER JOIN CTE t2 ON t1.Org_Pid=t2.Org_Id\r\n"
+ ")\r\n"
+ "SELECT Org_No FROM cte";
DbHelper db1 = DbFactory.CreateDb();
db1.CmdType = CmdType.SQL;
DataTable dt1 = db1.ExecDataTable(sqlstrall);
string deptId = "";
int j = dt1.Rows.Count;
if (j > 0)
{
for (int i = 0; i < j; i++)
{
deptId += Utility.Quote(dt1.Rows[i]["Org_No"].ToString().Trim()) + ",";
}
deptId = deptId.Substring(0, deptId.Length - 1);
searchStr = "e.PartID in(" + deptId + ")";
setGridView(searchStr);
}
else
{
setGridView("e.Incumbency=1");
}
}
对结果集不过滤的话就是这种效果
parentid id Name Sublevel
----------- ----------- -------------------------------------------------- -----------
1 2 中国 0
2 3 中国/湖南 1
2 4 中国/广东 1
4 5 中国/广东/深圳 2
4 6 中国/广东/广州 2
4 7 中国/广东/珠海 2
(6 行受影响)
create table tb1
(
parentid int,
id int,
Name nvarchar(20),
Remark nvarchar(20)
)
insert into tb1 values (1,1,'世界','AAAAA')
insert into tb1 values (1,2,'中国','AAAAA')
insert into tb1 values (2,3,'湖南','AAAAA')
insert into tb1 values (2,4,'广东','AAAAA')
insert into tb1 values (4,5,'深圳','AAAAA')
insert into tb1 values (4,6,'广州','AAAAA')
insert into tb1 values (4,7,'珠海','AAAAA')
--查询某个节点及其子节点
with f as
(
select parentid,id,cast(Name as varchar(max)) as Name,0 as Sublevel from tb1 where id=2--这里选择级别ID
union all
select a.parentid,a.id,CAST(b.Name+'/'+a.Name as varchar(max)) as Name,Sublevel+1 from tb1 as a inner join f as b on a.parentid=b.id
)
select * from f where Sublevel =2