批量导入科室信息的问题

liujunyanjiayou 2018-01-05 03:24:48
公司1的科室信息:
公司信息:
想要达到的效果是通过公司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 * from #t

得出的数据如下:
相应的parent_code字段没有改变。怎样改写可以使parent_code字段也能相应地改变呢?
...全文
334 12 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
liujunyanjiayou 2018-01-08
  • 打赏
  • 举报
回复
引用 7 楼 yisuylm 的回复:
不复杂,楼主基本上都写出了
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
完美解决了我的问题,谢谢啦。
liujunyanjiayou 2018-01-08
  • 打赏
  • 举报
回复
引用 4 楼 sinat_28984567 的回复:
如果数据就是三级并且只有第三级有两个科室,可以试试这个
 --测试数据代码 科室表
 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
如果仅是这些数据的话,是正确的,如果第二级还有其他科室,此第二级下面还有第三级科室,结果就不对了。不过还是谢谢啦。
liujunyanjiayou 2018-01-08
  • 打赏
  • 举报
回复
引用 1 楼 superwfei 的回复:
;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
如果第二级还有其他科室,此第二级下面还有第三级科室,结果就不对了。不过还是谢谢。
早起晚睡 2018-01-06
  • 打赏
  • 举报
回复
我都是先倒到临时表然后再用语句
日月路明 2018-01-05
  • 打赏
  • 举报
回复

 --测试数据代码 科室表
 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
日月路明 2018-01-05
  • 打赏
  • 举报
回复
不复杂,楼主基本上都写出了
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
日月路明 2018-01-05
  • 打赏
  • 举报
回复
问题看起来不复杂,晚上试一下
RINK_1 2018-01-05
  • 打赏
  • 举报
回复


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
二月十六 2018-01-05
  • 打赏
  • 举报
回复
如果数据就是三级并且只有第三级有两个科室,可以试试这个
 --测试数据代码 科室表
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


文盲老顾 2018-01-05
  • 打赏
  • 举报
回复
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   
文盲老顾 2018-01-05
  • 打赏
  • 举报
回复
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
文盲老顾 2018-01-05
  • 打赏
  • 举报
回复
;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

27,582

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧