批量导入科室信息的问题

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字段也能相应地改变呢？
...全文
335 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
毕业设计源码-jspSSM373的医院挂号上班打卡系统-项目实战.zip
本项目是基于JSP和SSM框架开发的医院挂号及考勤管理系统，采用B/S架构，使用MySQL数据库15。系统主要分为管理员、医生、用户三大角色模块：管理员可管理用户信息、科室排班、预约时段及考勤记录，支持批量导入导出数据；医生可查看出诊信息、管理在线预约并记录每日打卡；用户则能完成挂号预约、查看科室动态及个人健康档案15。前台首页集成公告通知、科室导航、出诊查询等核心功能，支持一键跳转后台管理界面5。
HIS全部API1
HIS全部API1
软件开发方案书完整版.pdf
软件开发方案书完整版.pdf软件开发方案书完整版.pdf软件开发方案书完整版.pdf软件开发方案书完整版.pdf软件开发方案书完整版.pdf软件开发方案书完整版.pdf软件开发方案书完整版.pdf软件开发方案书完整版.pdf软件开发方案书完整版.pdf
软件开发方案书.pdf
软件开发方案书.pdf
软件开发专项方案书.doc
软件开发专项方案书.doc
应用实例

27,582

社区成员

68,544

社区内容

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

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章