树形结构部门的 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。

上一篇:hdu 2767 Proving Equivalences(tarjan缩点)


下一篇:[置顶] 【Git入门之一】Git是神马?