树形结构部门的 sqlserver 排序

树形结构部门的 sqlserver 排序

因为要实现部门排序功能，而且要考虑部门的层级，直接用 sql 排序是不行的，所以写个 sql function 来支持。 
首先部门表：company

CREATE TABLE company(
CompanyId           id         NOT NULL,
CompanyName         nvarchar(115)    NOT NULL
）

 

记录部门层级结构的表，如果部门没有上级部门则在这张表中不会有记录：

CREATE TABLE company_report(
CompanyId     id    NOT NULL,
ReportToId    id    NOT NULL,
DisplayOrd    ord   CONSTRAINT [DF1_company_report] DEFAULT (1) NOT NULL
)

 

在 company_report 中 ReportToId 是指上级部门的 CompanyId 。 
像这种树形结构，在代码中一般都是用递归来遍历了，但是在 sql 中实现递归还是很麻烦的，还是写成循环简单点。 
定义 function ：

go
if (exists (select * from sys.objects where name = 'get_company_report_name_fn'))
drop FUNCTION get_company_report_name_fn
go
CREATE FUNCTION get_company_report_name_fn (@i_vCompanyId id, @i_vCompanyName string2)
RETURNS string2
AS
BEGIN
   DECLARE @t_vResult string2;
   DECLARE @t_vReportToId id;

   SET @t_vResult = '';
   --父部门ID
   SET @t_vReportToId = 0;
   --拼接父部门Name
   SELECT @t_vResult = r.CompanyName + '_' + c.CompanyName,
   @t_vReportToId =  cr.ReportToId
   FROM company_report cr, company c, company r
   WHERE cr.CompanyId = c.CompanyId 
     AND cr.ReportToId = r.CompanyId 
      AND cr.CompanyId = @i_vCompanyId
   --while 父部门还存在父部门
   while ( 
      exists(select cr.ReportToId from company_report cr where cr.CompanyId = @t_vReportToId)
   )
   begin
        SELECT @t_vResult = r.CompanyName + '_'+  @t_vResult,
         @t_vReportToId =  cr.ReportToId
         FROM company_report cr, company c, company r
         WHERE cr.CompanyId = c.CompanyId 
           AND cr.ReportToId = r.CompanyId 
           AND cr.CompanyId = @t_vReportToId
   end
   --已经是最顶层的部门了 返回原值
   if @t_vResult = ''
   begin
      SET @t_vResult = @i_vCompanyName
   end

   return @t_vResult
END
GO

 

原理就是在子部门的 name 上加上父部门的 name 用 _ 符号连接，如果父部门还存在父部门则继续连接下去。 
在排序的时候这样调用:

select dbo.get_company_report_name_fn(companyId, companyName) from company order by dbo.get_company_report_name_fn(companyId, companyName)

结果：

ula-client01 LTD.
ula-client01 LTD._ula-client02
ula-client01 LTD._ula-client02_ula-client02-子
ula-client01 LTD._ula-client03
Sony
Sony_Hair
Sony_Hair_IBM

写完给 Leader 看看，他觉得我写复杂了，然后就随手改了下：

go
if (exists (select * from sys.objects where name = 'get_company_report_name_fn'))
drop FUNCTION get_company_report_name_fn
go
CREATE FUNCTION get_company_report_name_fn (@i_vCompanyId id, @i_vCompanyName string2)
RETURNS string2
AS
BEGIN
   DECLARE @t_vResult string2;
   DECLARE @t_vReportToId id;
   DECLARE @t_vReportToName string2;

   SET @t_vResult = @i_vCompanyName;
   SET @t_vReportToId = @i_vCompanyId;

   while (exists(select cr.ReportToId from company_report cr where cr.CompanyId = @t_vReportToId))
   begin
      SELECT @t_vReportToId = cr.ReportToId, @t_vReportToName = c.companyName
      FROM company_report cr, company c
      WHERE cr.ReportToId = c.CompanyId 
      AND cr.CompanyId = @t_vReportToId;
     set @t_vResult = @t_vReportToName + '_' + @t_vResult;
   end

   return @t_vResult;   
END
go

好吧，是简单了很多。主要是消除了重复的代码。 
END。