27,579
社区成员
发帖
与我相关
我的任务
分享
--测试数据代码 科室表
if object_id('organization_infos') is not null
drop table organization_infos
Go
create table organization_infos( [organization_code] [varchar](50),
[organization_name] [varchar](50),
[parent_code] [varchar](50),
[level] [varchar](50),
[project_code] [varchar](50))
insert organization_infos
select '80A8E921-A6C1-4216-BE01-0579EB91F4BB',N'总经理','',1,'3A984F6D-6558-420D-8925-E07398234E6C' union all
select '2D45F592-AFC8-42A4-85FE-E4F8DDBBD202',N'副总经理','80A8E921-A6C1-4216-BE01-0579EB91F4BB',2,'3A984F6D-6558-420D-8925-E07398234E6C' union all
select '2DECEC06-B72D-4B85-8668-A86E44A922BD',N'财务部','2D45F592-AFC8-42A4-85FE-E4F8DDBBD202',3,'3A984F6D-6558-420D-8925-E07398234E6C' union all
select '80B58F15-E200-4267-B6F5-5064D5C1EFCD',N'人事部','2D45F592-AFC8-42A4-85FE-E4F8DDBBD202',3,'3A984F6D-6558-420D-8925-E07398234E6C'
--测试数据代码 公司表
if object_id('company_infos') is not null
drop table company_infos
create table company_infos( [company_code] [varchar](50),
[company_name] [varchar](50))
insert company_infos
select '3A984F6D-6558-420D-8925-E07398234E6C',N'公司1' union all
select '43F70F67-148C-4948-9239-ADC05787710B',N'公司2' union all
select '51A464C9-4CCB-4BBD-9FA8-44C1DDBB1099',N'公司3'
select newid() 'new_organization_code'
,a.organization_name
,a.parent_code
,a.level
,b.company_code
into #t
from organization_infos a,(select company_code from company_infos b where
not exists(select 1 from organization_infos where organization_infos.project_code=b.company_code)) b
where a.project_code='3A984F6D-6558-420D-8925-E07398234E6C'
select * from #t
--测试数据代码 科室表
if object_id('organization_infos') is not null
drop table organization_infos
Go
create table organization_infos( [organization_code] [varchar](50),
[organization_name] [varchar](50),
[parent_code] [varchar](50),
[level] [varchar](50),
[project_code] [varchar](50))
insert organization_infos
select '80A8E921-A6C1-4216-BE01-0579EB91F4BB',N'总经理','',1,'3A984F6D-6558-420D-8925-E07398234E6C' union all
select '2D45F592-AFC8-42A4-85FE-E4F8DDBBD202',N'副总经理','80A8E921-A6C1-4216-BE01-0579EB91F4BB',2,'3A984F6D-6558-420D-8925-E07398234E6C' union all
select '2DECEC06-B72D-4B85-8668-A86E44A922BD',N'财务部','2D45F592-AFC8-42A4-85FE-E4F8DDBBD202',3,'3A984F6D-6558-420D-8925-E07398234E6C' union all
select '80B58F15-E200-4267-B6F5-5064D5C1EFCD',N'人事部','2D45F592-AFC8-42A4-85FE-E4F8DDBBD202',3,'3A984F6D-6558-420D-8925-E07398234E6C'
--测试数据代码 公司表
if object_id('company_infos') is not null
drop table company_infos
create table company_infos( [company_code] [varchar](50),
[company_name] [varchar](50))
insert company_infos
select '3A984F6D-6558-420D-8925-E07398234E6C',N'公司1' union all
select '43F70F67-148C-4948-9239-ADC05787710B',N'公司2' union all
select '51A464C9-4CCB-4BBD-9FA8-44C1DDBB1099',N'公司3'
go
select newid() 'new_organization_code'
,a.organization_code
,a.organization_name
,a.parent_code
,a.level
,b.company_code
into #t
from organization_infos a
join company_infos b on 1=1
where a.project_code='3A984F6D-6558-420D-8925-E07398234E6C'
and not exists(select 1 from organization_infos where organization_infos.project_code=b.company_code)
select t1.new_organization_code, t1.organization_name, parent_code=t2.new_organization_code, t1.[level], t1.company_code
from #t t1
left outer join #t t2 on t1.Parent_Code=t2.organization_code and t1.company_code=t2.company_code
return
--测试数据代码 科室表
if object_id('organization_infos') is not null
drop table organization_infos
Go
return
create table organization_infos( [organization_code] [varchar](50),
[organization_name] [varchar](50),
[parent_code] [varchar](50),
[level] [varchar](50),
[project_code] [varchar](50))
insert organization_infos
select '80A8E921-A6C1-4216-BE01-0579EB91F4BB',N'总经理','',1,'3A984F6D-6558-420D-8925-E07398234E6C' union all
select '2D45F592-AFC8-42A4-85FE-E4F8DDBBD202',N'副总经理','80A8E921-A6C1-4216-BE01-0579EB91F4BB',2,'3A984F6D-6558-420D-8925-E07398234E6C' union all
select '2DECEC06-B72D-4B85-8668-A86E44A922BD',N'财务部','2D45F592-AFC8-42A4-85FE-E4F8DDBBD202',3,'3A984F6D-6558-420D-8925-E07398234E6C' union all
select '80B58F15-E200-4267-B6F5-5064D5C1EFCD',N'人事部','2D45F592-AFC8-42A4-85FE-E4F8DDBBD202',3,'3A984F6D-6558-420D-8925-E07398234E6C'
--测试数据代码 公司表
if object_id('company_infos') is not null
drop table company_infos
create table company_infos( [company_code] [varchar](50),
[company_name] [varchar](50))
insert company_infos
select '3A984F6D-6558-420D-8925-E07398234E6C',N'公司1' union all
select '43F70F67-148C-4948-9239-ADC05787710B',N'公司2' union all
select '51A464C9-4CCB-4BBD-9FA8-44C1DDBB1099',N'公司3'
go
select newid() 'new_organization_code'
,a.organization_code
,a.organization_name
,a.parent_code
,a.level
,b.company_code
into #t
from organization_infos a
join company_infos b on 1=1
where a.project_code='3A984F6D-6558-420D-8925-E07398234E6C'
and not exists(select 1 from organization_infos where organization_infos.project_code=b.company_code)
select t1.new_organization_code, t1.organization_name, parent_code=t2.new_organization_code, t1.[level], t1.company_code
from #t t1
left outer join #t t2 on t1.Parent_Code=t2.organization_code and t1.company_code=t2.company_code
select *,newid() as parent_code_new into #t from organization_infos A
join company_infos B ON A.project_code<>B.company_code
insert into organization_infos
select A.parent_code_new,
A.organization_name,
B.parent_code_new,
A.level,
A.company_code
from #t A
left join #t B on A.company_code=B.company_code and A.level=B.level+1
--测试数据代码 科室表
if object_id('organization_infos') is not null
drop table organization_infos
Go
create table organization_infos( [organization_code] [varchar](50),
[organization_name] [varchar](50),
[parent_code] [varchar](50),
[level] [varchar](50),
[project_code] [varchar](50))
insert organization_infos
select '80A8E921-A6C1-4216-BE01-0579EB91F4BB',N'总经理','',1,'3A984F6D-6558-420D-8925-E07398234E6C' union all
select '2D45F592-AFC8-42A4-85FE-E4F8DDBBD202',N'副总经理','80A8E921-A6C1-4216-BE01-0579EB91F4BB',2,'3A984F6D-6558-420D-8925-E07398234E6C' union all
select '2DECEC06-B72D-4B85-8668-A86E44A922BD',N'财务部','2D45F592-AFC8-42A4-85FE-E4F8DDBBD202',3,'3A984F6D-6558-420D-8925-E07398234E6C' union all
select '80B58F15-E200-4267-B6F5-5064D5C1EFCD',N'人事部','2D45F592-AFC8-42A4-85FE-E4F8DDBBD202',3,'3A984F6D-6558-420D-8925-E07398234E6C'
--测试数据代码 公司表
if object_id('company_infos') is not null
drop table company_infos
create table company_infos( [company_code] [varchar](50),
[company_name] [varchar](50))
insert company_infos
select '3A984F6D-6558-420D-8925-E07398234E6C',N'公司1' union all
select '43F70F67-148C-4948-9239-ADC05787710B',N'公司2' union all
select '51A464C9-4CCB-4BBD-9FA8-44C1DDBB1099',N'公司3'
select newid() 'new_organization_code'
,a.organization_name
,a.parent_code
,a.level
,b.company_code
into #t
from organization_infos a,(select company_code from company_infos b where
not exists(select 1 from organization_infos where organization_infos.project_code=b.company_code)) b
where a.project_code='3A984F6D-6558-420D-8925-E07398234E6C'
select a.new_organization_code,a.organization_name
,b.new_organization_code AS parent_code
,a.level
,a.company_code from #t a LEFT JOIN #t b ON a.company_code=b.company_code AND a.level=b.level+1
DROP TABLE #t
organization_code organization_name parent_code level project_code
------------------------------------ -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
FAAFC371-8FF9-48B0-819A-9ACE8AE25B29 总经理 NULL 1 43F70F67-148C-4948-9239-ADC05787710B
4B190C2E-652C-491C-AAD9-679DFD1D83FA 副总经理 FAAFC371-8FF9-48B0-819A-9ACE8AE25B29 2 43F70F67-148C-4948-9239-ADC05787710B
DFB2DFCA-0776-41E7-8F71-EA67D08AAF44 财务部 4B190C2E-652C-491C-AAD9-679DFD1D83FA 3 43F70F67-148C-4948-9239-ADC05787710B
0F2BB375-6A45-42A7-A6BE-FAAC8FE25EFC 人事部 4B190C2E-652C-491C-AAD9-679DFD1D83FA 3 43F70F67-148C-4948-9239-ADC05787710B
AC887F48-6924-4E01-8B99-B23F6DE582B6 总经理 NULL 1 51A464C9-4CCB-4BBD-9FA8-44C1DDBB1099
75762E9E-DEE1-4981-8718-161825A25AA5 副总经理 AC887F48-6924-4E01-8B99-B23F6DE582B6 2 51A464C9-4CCB-4BBD-9FA8-44C1DDBB1099
1952D0B5-1B56-48C1-A94D-3AA5EAA18FC5 财务部 75762E9E-DEE1-4981-8718-161825A25AA5 3 51A464C9-4CCB-4BBD-9FA8-44C1DDBB1099
68DC072A-30D2-4B17-9F08-A2195C5788A8 人事部 75762E9E-DEE1-4981-8718-161825A25AA5 3
;with t as (
select newid() as organization_code,organization_name,level,company_code as project_code from organization_infos a
cross apply (
select * from company_infos b where not exists(select top 1 1 from organization_infos where project_code=b.company_code)
) b
),tt as (
select organization_code,organization_name,convert(varchar(50),null) as parent_code,level,project_code from t where level=1
union all
select a.organization_code,a.organization_name,convert(varchar(50),b.organization_code) as parent_code,a.level,a.project_code from t a,tt b where a.level>1 and a.level-1=b.level and a.project_code=b.project_code
)
select * from tt order by project_code,level