要优化的sql语句递归查询不包含本部门及其子部门

最新推荐文章于 2023-01-16 20:44:33 发布

hanxiangtong

最新推荐文章于 2023-01-16 20:44:33 发布

阅读量2k

点赞数

CC 4.0 BY-SA版权

文章标签： sql 优化 string exception insert join

本文链接：https://blog.youkuaiyun.com/hanxiangtong/article/details/1968289

本文介绍了一种优化部门选择器的方法，通过改进代码逻辑避免显示当前部门及其子部门，并使用存储过程实现了更高效的部门递归查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

所做的操作是在选取部门的时，不显示自己及其子部门

刚开始时是这样做的

this.DropDownList1.Items.Add(new ListItem("该部门无上级部门", "0"));
getDepartmentSon(departmentid);
getDepartmentSons(departmentid);

#region 不包含本部门
        private void getDepartmentSon(string depid)
        {
            try
            {
                string sql = "select departmentname,departmentid from TOrganization_DepartmentBasicInfo where departmentid<>'" + depid + "' order by departmentname";
                DataTable dt = dataHelper.ExecuteDataSet(sql).Tables[0];
                if (dt.Rows.Count != 0)
                {
                    foreach (System.Data.DataRow dr in dt.Rows)
                    {
                        this.DropDownList1.Items.Add(new ListItem(dr[0].ToString(), dr[1].ToString()));
                    }
                }
            }
            catch (Exception error)
            {
                string msg = error.Message;
            }
        }
        #endregion 不包含本部门

        #region 递归不包含的子部门
        private void getDepartmentSons(string parentDepartmentID)
        {
            try
            {
                string sql = "select departmentid from TOrganization_DepartmentRelationship where superiordepartmentid=('" + parentDepartmentID + "')";
                DataTable dt = dataHelper.ExecuteDataSet(sql).Tables[0];
                foreach (System.Data.DataRow dr in dt.Rows)
                {
                    string departid = dr[0].ToString();
                    string sql1 = "select departmentname,departmentid from TOrganization_DepartmentBasicInfo where departmentid in ('" + dr[0].ToString() + "')";
                    DataTable dt1 = dataHelper.ExecuteDataSet(sql1).Tables[0];
                    foreach (DataRow dr1 in dt1.Rows)
                    {

//把这个部门下子部门去除

this.DropDownList1.Items.Remove(new ListItem(dr1[0].ToString(), dr1[1].ToString()));

                    }
                    getDepartmentSons(departid);
                }
            }
            catch (Exception error)
            {
                string msg = error.Message;
            }
        }
        #endregion 递归不包含的子部门

但是这样做效率不高，后来改成储存过程实现部门的递归

declare @tb table(id int)
insert @tb select DepartmentID from TOrganization_DepartmentRelationship
where SuperiorDepartmentID = 1
while @@rowcount > 0
insert @tb select a.DepartmentID from TOrganization_DepartmentRelationship as a
inner join @tb as b on a.SuperiorDepartmentID = b.id
and a.DepartmentID not in(select id from @tb)

select id from @tb

select a.DepartmentID ,b.DepartmentName from TOrganization_DepartmentRelationship as a,dbo.TOrganization_DepartmentBasicInfo as b
where a.DepartmentID not in(select id from @tb)
and a.DepartmentID<>1
and a.DepartmentID=b.DepartmentID